Speeding up Database Restore

Use the following instead of default restore settings will speed up database restore.

restore database MyDB from disk = 'D:\Folder01\MyDB_FullBackup.bak'
with file = 1, replace, maxtransfersize = 4194302, buffercount = 96, stats = 5

restore database MyDB from disk = 'D:\Folder01\MyDB_FullBackup.bak'
with file = 1, replace, maxtransfersize = 4194302, buffercount = 96, stats = 5
   MOVE 'MyDB' TO 'E:\SQLServer\Data\MyDB.mdf',   
   MOVE 'MyDB_log' TO 'E:\SQLServer\Log\MyDB_log.ldf'

Use the following to determine buffercount to set to…

dbcc traceon (3213, -1)

dbcc traceon (3605, -1)

declare @dtfromdate datetime = dateadd (hour, -1, getdate()),
       @dtToDate datetime = getdate()

exec xp_readerrorlog 0,1,null, null, @dtfromdate, @dtToDate

MaxTransferSize: 4032 KB
BufferCount: 96
Sets Of Buffers: 2
Total buffer space: 756 MB
Memory limit: 1023 MB

Max Transfer Size * Buffer Count * Sets of Buffers = Total Buffer Space
4032 * 96 * 2 = (774,144 KB/ 1024 KB) = 756 MB

1023 MB – 756 MB = 267 MB left, so we can continue to increase buffercount.

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

BUFFERCOUNT
S

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.

The total space used by the buffers is determined by: buffercount****maxtransfersize.

STATS

Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed (approximately).

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, the Database Engine reports at approximately that interval; for instance, instead of displaying precisely 40%, the option might display 43%. For large backup sets, this is not a problem because the percentage complete moves very slowly between completed I/O calls.

REPLACE

Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs. This prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

• The database named in the RESTORE statement already exists on the current server, and
• The database name is different from the database name recorded in the backup set.

REPLACE also allows RESTORE to overwrite an existing file that cannot be verified as belonging to the database being restored. Normally, RESTORE refuses to overwrite pre-existing files. WITH REPLACE can also be used in the same way for the RESTORE LOG option.

REPLACE also overrides the requirement that you back up the tail of the log before restoring the database.

Source:

https://www.mssqltips.com/sqlservertip/4935/optimize-sql-server-database-restore-performance/