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…

 

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!