SQL Server Backup Recovery efficiency

How to improve the speed of backup?

In fact, this problem and how to make the system run faster is the same, to want the system to run faster, nothing else is: optimize the system, or even better and more powerful servers, especially more CPU, larger memory, faster hard disk.

Improve the speed of backup is the same, the general backup database, is unlikely to be a system outage, why? Because the system resources used to back up the database do not take up too much, so if you want to increase the speed of the backup, then only allow the backup to use more memory line, through the following 2 options, to allocate more resources to the backup, then the backup speed is greatly increased.

Data transfer Options :

1. The total space used by the buffer is determined by the following formula: Buffercount*maxtransfersize.

2, BUFFERCOUNT = {BUFFERCOUNT | @buffercount_variable}
Specifies the total number of I/O buffers used for the backup operation. You can specify any positive integer, but a large number of buffers can cause an "out of memory" error due to insufficient virtual address space in the Sqlservr.exe process.

3, maxTransferSize = {maxtransfersize | @maxtransfersize_variable}
Specifies the maximum Transmission Unit (bytes) to use between SQL Server and backup media. The possible value is a multiple of 65536 kilobytes (KB), up to 4194304 bytes (4 MB).

Can you do it any faster?

In fact, when the backup, the reason is slow, and IO performance-related, because IO is the entire computer system is the slowest, so the backup is also slow, but if we can reduce the data written to the disk, it can improve performance, of course, while reducing the write data, You need to compress by consuming more CPU resources to reduce the size of the data you are backing up.

Compression options:

Specifies whether backup compression is performed for this backup.
Starting with SQL Server R2, SQL Server R2 Standard and all later versions support backup compression. At installation time, the default behavior is no backup compression. However, this default setting can be changed by setting the backup compression default server configuration option.

By default, when you compress a backup, a checksum (that is, the checksum option) is performed to detect the presence of media corruption.

In the parameters of SQL Server, the default server is not enabled for backup compression.

?

12345

selectname,         description,         value_in_use --是否启用此参数 from sys.configurations  where name=‘backup compression default‘

If you want to enable backup compression, you can run the following command:

?

123	execsp_configure ‘backup compression default‘,1  reconfigure  go

Can you go any faster?

This time is the use of IO parallel features, that is, if there are multiple physical hard disks on the server, then a backup file can be striped, for example, there are 3 hard disks, then the original backup files, divided into 3 parts, respectively, stored on 3 hard disks.

Here's the experiment code.

1, build a library, build a table, insert a large amount of data:

?

1234567891011121314151617181920212223242526272829303132

use mastergoif DB_ID(‘db_test‘) is not null   drop database db_testgoCREATE DATABASE db_testON(    NAME = db_test_DATA,    FILENAME = ‘E:\db_test.mdf‘)LOG ON(    NAME = db_test_LOG,    FILENAME = ‘E:\db_test.ldf‘)GOuse db_testgoselect * Into testfrom sys.objectsgoinsert into testselect *from testgo 20

2, the normal full backup, time-consuming 213 seconds:

?

12345678

backup databasedb_testto disk = ‘e:\db_test.bak‘withformat/*已为数据库 ‘db_test‘，文件 ‘db_test_DATA‘ (位于文件 1 上)处理了 356448 页。已为数据库 ‘db_test‘，文件 ‘db_test_LOG‘ (位于文件 1 上)处理了 3 页。BACKUP DATABASE 成功处理了 356451 页，花费 213.018 秒(13.072 MB/秒)。*/

3. Increase the full backup of the IO buffer, which takes 142 seconds:

?

12345678910

backup database db_test to disk = ' E:\db_test1.bak ' with format,       Code class= "SQL Plain" >buffercount = ten,       maxtransfersize = 4194304 /* 356448 pages have been processed for database ' db_test ', file ' Db_test_data ' (located on file 1). 1 pages have been processed for database ' db_test ', file ' Db_test_log ' (located on file 1). backup DATABASE successfully processed 356449 pages and took 142.101 seconds (19.597 MB/s). */

4, increase the IO buffer, compress the full backup, time is 56 seconds:

?

1234567891011

backup database db_testto disk = ‘e:\db_test2.bak‘with format,     buffercount = 10,     maxtransfersize = 4194304,     compression/*已为数据库 ‘db_test‘，文件 ‘db_test_DATA‘ (位于文件 1 上)处理了 356448 页。已为数据库 ‘db_test‘，文件 ‘db_test_LOG‘ (位于文件 1 上)处理了 1 页。BACKUP DATABASE 成功处理了 356449 页，花费 56.089 秒(49.648 MB/秒)。*/

5, increase the IO buffer, compression, striped full backup, time-consuming 55 seconds, because it is a laptop, on a hard disk, so the effect is not obvious:

?

12345678910111213

backup database db_test  to disk = ‘c:\db_test_stripping1.bak‘,     disk = ‘d:\db_test_stripping2.bak‘,     disk = ‘e:\db_test_stripping3.bak‘with format,     buffercount = 10,     maxtransfersize = 4194304,     compression   /*已为数据库 ‘db_test‘，文件 ‘db_test_DATA‘ (位于文件 1 上)处理了 356448 页。已为数据库 ‘db_test‘，文件 ‘db_test_LOG‘ (位于文件 1 上)处理了 1 页。BACKUP DATABASE 成功处理了 356449 页，花费 55.060 秒(50.576 MB/秒)。*/

By doing this experiment on my regular laptop, we can see the obvious difference, especially with the compression option, which is 4 times times faster than the original full backup. So if it is on the server, I think it should be able to improve more.

SQL Server Backup Recovery efficiency