Sql-server – Restore master database from SQL Server 2008 R2 on SQL Server 2012

restoresql-server-2012

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?

Best Answer

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.

Related Question