5

I have a process that restores all my backups on a centralized server for testing. I have recently upgraded this server to SQL server 2012, and now I am getting an error when restoring master from SQL Server 2008 R2. Restores from SQL Server 2012 and SQL Server 2005 work fine. I get the same results when trying to restore to SQL 2014 as well.

This is my restore statement:

RESTORE DATABASE masttest 
FROM DISK = 'c:\temp\master.bak' 
WITH MOVE 'master' TO 'c:\temp\masttest.mdf', 
MOVE 'mastlog' TO 'c:\temp\masttest.ldf';

And this is the results. The database ends up in Recovery pending.

Processed 400 pages for database 'masttest', file 'master' on file 1.
Processed 3 pages for database 'masttest', file 'mastlog' on file 1.
Converting database 'masttest' from version 661 to the current version 706.
Database 'masttest' running the upgrade step from version 661 to version 668.
Database 'masttest' running the upgrade step from version 668 to version 669.
Database 'masttest' running the upgrade step from version 669 to version 670.
Database 'masttest' running the upgrade step from version 670 to version 671.
Database 'masttest' running the upgrade step from version 671 to version 672.
Database 'masttest' running the upgrade step from version 672 to version 673.
Database 'masttest' running the upgrade step from version 673 to version 674.
Database 'masttest' running the upgrade step from version 674 to version 675.
Database 'masttest' running the upgrade step from version 675 to version 676.
Database 'masttest' running the upgrade step from version 676 to version 677.
Database 'masttest' running the upgrade step from version 677 to version 679.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 5570, Level 23, State 3, Line 1
FILESTREAM Failed to find the garbage collection table.

Filestream is not enabled on the source server or the server I am restoring to. Is there anything I can do to restore this successfully or does this look like a bug?

  • 1
    You cannot restore system databases to a higher version of SQL Server so testing that backup is a mute point. – user507 May 14 '14 at 17:26
  • I am just trying to test whether the backup is restorable, thus making it a valid backup. version should not matter. I dont have any issues restoring the master from a 2005 instance on to the 2012 instance. – Bob Klimes May 14 '14 at 17:32
  • Filestream did not exist in SQL Server 2005, likely why it succeeds. I just tried it and get the same error. – user507 May 14 '14 at 19:15
  • I've encountered the same problem attaching .mdf from SQL 2008 R2 onto SQL 2014. In my case it's because my dev box died, I still have the disc, and I want to extract data from the master database. Anyone know if it should be possible to do this? – Rory Sep 8 '14 at 21:29
  • @ShawnMelton: I understand that the OP is restoring a system database. But he is restoring it as a db not named master. Wouldn't that make a difference? Can you post some reference/link that supports your comments for the OP's specific scenario? – Dave Mason Feb 23 '15 at 18:24
3

You should never restore a system database from from one version of SQL Server to another. The master database contains tables and other objects that support the inner workings of the server itself. I wouldn't even restore system databases to another server of the same version. I would instead determine what the information was I was trying to capture (user accounts, scheduled jobs, SSIS packages etc.) and have those extracted and imported to the new server.

| improve this answer | |
  • I am just doing the restore to test the backup not to have a functional master database. – Bob Klimes May 14 '14 at 15:58
  • 1
    Sorry to say this, but your test is invalid. You are breaking SQL by doing this. There is not way for you to verify your backup is correct, because you are mixing different versions of SQL. You should use "with verify" when you perform the backup, and if you absolutely need to prove the backup is valid, restore it to the same version/edition of SQL Server. – datagod May 14 '14 at 19:28
  • 1
    @datagod: I understand that the OP is restoring a system database. But he is restoring it as a db not named master. Wouldn't that make a difference? Can you post some reference or documentation link that supports your answer for the OP's specific scenario? – Dave Mason Feb 23 '15 at 18:22
  • 1
    I did not see the new name. I thought the OP was restoring over top of the existing master database which would break SQL. I still would not restore a system database from one version to another. It is not an activity that would normally take place. I am going on instinct here, so no I cannot directly link you to any articles explaining this. When you restore a database from an earlier version, upgrade scripts are applied. This particular path may not have been fully tested by Microsoft, as it is not a common path to follow. – datagod Feb 24 '15 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.