Restore a SQL Server Database Faster
BUFFERCOUNT
- Specifies the total number of I/O buffers to be used for the restore operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process.
MAXTRANSFERSIZE
- Specifies the largest unit of transfer in bytes to be used between the backup media and SQL Server. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
DBCC TRACEON (3213, -1)
DBCC TRACEON (3605, -1)
- required to output backup & restore information to SQL Server error log.
Example 1: Using default parameter values for MAXTRANSFERSIZE and BUFFERCOUNT
RESTORE DATABASE [App_Logs] FROM DISK = N'E:\Volume1\App_Logs_FULL_2019-01-10-1020.BAK' WITH FILE = 1,
MOVE N'App_Logs_log' TO N'E:\Volume1\DB\Log\applogs.ldf', MOVE N'App_Logs' TO N'E:\Volume1\DB\Data\applogs.mdf',
NOUNLOAD, STATS = 5
Processed 2570416 pages for database 'App_Logs', file 'App_Logs' on file 1.
Processed 2 pages for database 'App_Logs', file 'App_Logs_log' on file 1.
RESTORE DATABASE successfully processed 2570418 pages in 183.606 seconds (109.372 MB/sec).
Restore Time: 3mins
Example 2: Using custom parameter values for MAXTRANSFERSIZE and BUFFERCOUNT
RESTORE DATABASE [App_Logs] FROM DISK = N'E:\Volume1\App_Logs_FULL_2019-01-10-1020.BAK' WITH FILE = 1,
MOVE N'App_Logs_log' TO N'E:\Volume1\DB\Log\applogs.ldf', MOVE N'App_Logs' TO N'E:\Volume1\DB\Data\applogs.mdf',
NOUNLOAD, STATS = 5,
MAXTRANSFERSIZE= 4194302,
BUFFERCOUNT = 50
Processed 2570416 pages for database 'App_Logs', file 'App_Logs' on file 1.
Processed 2 pages for database 'App_Logs', file 'App_Logs_log' on file 1.
RESTORE DATABASE successfully processed 2570418 pages in 87.481 seconds (229.551 MB/sec).
RestoreTime: 1.5mins
A 50% Restore Performance Improvement !!!!!!!!!!!
|
|
|
|
|
|