Saw an interesting error message today when a colleague was doing a test SQL restore onto a spare server. Neither of us are SQL people…! Here’s the error:
We went back to the restore wizard and set its options again but this time clicked the Script button at the top to copy the T-SQL to the clipboard.
The T-SQL was as follows. I’ve shortened paths, changed names and added line-breaks:
RESTORE DATABASE [database] FROM DISK = N'D:\Restore\MSSQL\Backup\database\backupfilename.bak' WITH FILE = 1, MOVE N'dbname_SYSTEM' TO N'D:\MSSQL\DATA\dbfilename.mdf', MOVE N'dbname_DATA' TO N'D:\MSSQL\DATA\dbfilename.mdf', MOVE N'dbname_INDEX' TO N'D:\MSSQL\DATA\dbfilename.mdf', MOVE N'dbname_INDEX_2' TO N'D:\MSSQL\DATA\dbfilename.NDF', MOVE N'dbname_ARCHIVE' TO N'D:\MSSQL\DATA\dbfilename.NDF', MOVE N'dbname_ARCHIVE_2' TO N'D:\MSSQL\DATA\dbfilename.NDF', MOVE N'dbname_LOG' TO N'D:\MSSQL\DATA\dbfilename.ldf', MOVE N'dbname_LOG_2' TO N'D:\MSSQL\DATA\dbfilename.ldf', NOUNLOAD, STATS = 10
From that, you can see multiple parts of the database going to the same destination filename which must explain the error we were getting.
Pasting the T-SQL into a new query and adding suffices to the stem of those filenames to make them unique let the restore work and the database tables, when checked, looked fine.
Recognising the NDF file extension as being from Secondary Data Files, I next had a look at what was going on with the disposition of the files on the live database:
As you can see, the files are normally spread over 7 folders on 4 drives but we were restoring the lot for test purposes into one folder and so the filenames were clashing.
Rather than tweak the T-SQL like we did, I see we could just change the destinations in the second page of the Restore wizard next time in the “Restore As” column: