TSQL: Restore a multiple file backup

Just a follow up post using the backup files created in TSQL: Backup a database to multiple files. Here’s the script I used…

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
 
-- Single file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\single_file_backup.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Two file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\two_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\two_files_file2.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Five file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\five_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file2.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file3.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file4.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file5.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Ten file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\ten_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file2.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file3.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file4.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file5.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file6.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file7.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file8.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file9.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file10.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

The results are less than exciting…

Files 1 2 5 10
CPU time (ms) 39577 37595 35162 32790
Elapsed time (ms) 1478788 1443965 1450444 1454009

I’ve sexed up my results a bit on these graphs by leaving the restore times in ms…

tsql_multiple_backup_files_sql_server

 

Have a more sensible scale and the truth is less sexy (minutes instead of ms)…

Now I’m aware this isn’t the best of tests. I had a quick glance through the documentation and I couldn’t see any indication that the RESTORE command was multi-threaded. I kept an eye on sys.dm_exec_requests and it didn’t indicate this either. I guess a better test might be to see if having multiple data files has any significant effect. Fill your boots if you fancy having a go!

 

 

 

 


Leave a Reply