SQL Server backup Acceleration

For large databases, backup takes a lot of time and resources. What if I can make the backup run faster?

 

You can use the following parameters:

1. Back up the database to multiple files (multiple disks)

2. Enable the compression function (2008, after R2)

3. Use the maxtransfersize and buffercount Parameters

4. Adjust the backup side rate and back up data using files and file groups

 

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 ).

Backing up a database to multiple files and multiple disks has the following advantages:

 

1. Back up to multiple disks to make full use of disk Io and reduce backup time

 

2. In addition, if a single disk has insufficient space, backing up multiple files can reduce the size of the backup file to solve the disk space problem. In addition, it is convenient to move to other places through the network.

 

3. SQL server can use multiple backup threads to back up multiple files to provide efficiency.

 

We can back up to multiple files using management studio or T-SQL:

 

 

T-SQL:

Backup database [adventureworks] to disk = n' D: \ MSSQL \ mssql10.sql2k8 \ MSSQL \ backup \ adventureworks. Bak ',

Disk =
N'e: \ MSSQL \ mssql10.sql2k8 \ MSSQL \ backup \ advantureworks1.bak ',

Disk =
N'f: \ MSSQL \ mssql10.sql2k8 \ MSSQL \ backup \ adventureworks2.bak 'withnoformat, noinit,

Name = n' adventureworks-full database backup ', Skip, norewind, nounload, stats =
10

Go

 

Restoration is also simple:

Restore database [adventureworks] From

Disk =
N'd: \ MSSQL \ mssql10.sql2k8 \ MSSQL \ backup \ adventureworks. Bak ',

Disk =
N'e: \ MSSQL \ mssql10.sql2k8 \ MSSQL \ backup \ advantureworks1.bak ',

Disk =
N'f: \ MSSQL \ mssql10.sql2k8 \ MSSQL \ backup \ adventureworks2.bak 'withreplace

 

Note that the checksum option will increase the backup time while ensuring the backup quality. It would be terrible if the backup cannot be used during the last restoration.