info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Restore a SQL Server Database Faster
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2019-01-10








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 !!!!!!!!!!!