SQL Server backup database acceleration (compression-compression)

ManySQL ServerDBA inBackupThe general writing format is as follows:

Backup database adventureworks
To disk = 'z: \ sqlserverbackups \ advworksdata. Bak'
With format, checksum;

For largeDatabase, The backup time is relatively long,

After studying the backup database parameters recently, we found that you can set the parameters to speed up backup:

1. Set multiple backup sets during backup, such
To disk = n'd:/1. bak ', disk = n'd:/2. bak ', disk = n'd:/3. bak ', disk = n'd:/4. bak'

2. Use the buffercount and maxtransfersize options.

Buffercount :Specifies the total number of I/O buffers used for backup operations. You can specify any positive integer. However, a large number of buffers may cause a "memory insufficiency" error due to insufficient virtual address space in the sqlservr.exe process.

The total space used by the buffer zone is determined by the following formula:Buffercount*Maxtransfersize.

Maxtransfersize :Specifies the maximum transmission unit (in bytes) to be used between the SQL server and the backup media ). The possible value is a multiple of 65536 bytes (64 KB), up to 4194304 bytes (4 MB ).

3. Enable Compression)

This option is only available on SQL Server.2008Enterprise, SQL Server 2008 R2 Standard, and all later versions are supported.

Note: The compression option requires additional CPU resources, so we recommend that you do not use it when the system is busy.

4. As a DBA, you may need to ensure data security. Generally, the checksum parameter is added to ensure data security.

Test comparison:

Database size: 53282.19 MB (including log files)

The data file size is about 32 GB.

Use the standard backup method:

Backup database [test]
To disk = n'd:/1. Bak 'with checksum;

Use the BACKUP command of optimization parameters

Backup database [test]
To disk = n'd: \ test_full_20110311_0500000001.bak ',
Disk = n'd: \ test_full_20110311_050001_2.bak ',
Disk = n'd: \ test_full_20110311_050001_3.bak ',
Disk = n'd: \ test_full_20110311_0500000004.bak'
With checksum, compression, buffercount = 50, maxtransfersize = 4194304

Backup time: 110.350 seconds

The speed has increased by 50%.

If the checksum parameter is not used, the backup speed can be faster, but as dBA, data security is the first priority. Therefore, we recommend that you add the checksum parameter.

The preceding parameters are only applicable to SQL Server.2005SQL Server 2008

 

The Restore database statement can also be used

Buffercount and maxtransfersize Parameters