mjs082969
asked on
SQL Server Restore of Master Database On ANother Server with Different DB Name
Hello,
I am trying to restore a copy of our Master database. Normally this runs on our production server, but I need to restore this to my local machine, which runs a different/newer version of SQL, in case that matters. I have the BAK file locally, and I do not wish to overwrite my existing Master database, so I have been attempting to restore it as MasterX.
C:\Users\me>SQLCMD -E -SMyPC -Q "RESTORE DATABASE masterx FROM Disk='c:\
master.bak' WITH RECOVERY, MOVE 'master' TO 'C:\SQLDATA\master_data.md f', MOVE '
mastlog' TO 'C:\SQLDATA\master_log.mdf '"
Processed 376 pages for database 'masterx', file 'master' on file 1.
Processed 3 pages for database 'masterx', file 'mastlog' on file 1.
Msg 3013, Level 16, State 1, Server PC9391, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 5570, Level 23, State 3, Server PC9391, Line 1
FILESTREAM Failed to find the garbage collection table.
Converting database 'masterx' from version 661 to the current version 706.
Database 'masterx' running the upgrade step from version 661 to version 668.
Database 'masterx' running the upgrade step from version 668 to version 669.
Database 'masterx' running the upgrade step from version 669 to version 670.
Database 'masterx' running the upgrade step from version 670 to version 671.
Database 'masterx' running the upgrade step from version 671 to version 672.
Database 'masterx' running the upgrade step from version 672 to version 673.
Database 'masterx' running the upgrade step from version 673 to version 674.
Database 'masterx' running the upgrade step from version 674 to version 675.
Database 'masterx' running the upgrade step from version 675 to version 676.
Database 'masterx' running the upgrade step from version 676 to version 677.
Database 'masterx' running the upgrade step from version 677 to version 679.
When this is complete, the database IS displayed in the SSMS tree of databases, but it says '(Recovery Pending)'.
I don't need to do much with this database besides look at a table, and possibly some triggers.
I DID attempt to do this in single user mode, but because I was not restoring to Master, this would not work.
Any ideas what I am doing wrong?
Thanks In Advance,
- Michael
I am trying to restore a copy of our Master database. Normally this runs on our production server, but I need to restore this to my local machine, which runs a different/newer version of SQL, in case that matters. I have the BAK file locally, and I do not wish to overwrite my existing Master database, so I have been attempting to restore it as MasterX.
C:\Users\me>SQLCMD -E -SMyPC -Q "RESTORE DATABASE masterx FROM Disk='c:\
master.bak' WITH RECOVERY, MOVE 'master' TO 'C:\SQLDATA\master_data.md
mastlog' TO 'C:\SQLDATA\master_log.mdf
Processed 376 pages for database 'masterx', file 'master' on file 1.
Processed 3 pages for database 'masterx', file 'mastlog' on file 1.
Msg 3013, Level 16, State 1, Server PC9391, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 5570, Level 23, State 3, Server PC9391, Line 1
FILESTREAM Failed to find the garbage collection table.
Converting database 'masterx' from version 661 to the current version 706.
Database 'masterx' running the upgrade step from version 661 to version 668.
Database 'masterx' running the upgrade step from version 668 to version 669.
Database 'masterx' running the upgrade step from version 669 to version 670.
Database 'masterx' running the upgrade step from version 670 to version 671.
Database 'masterx' running the upgrade step from version 671 to version 672.
Database 'masterx' running the upgrade step from version 672 to version 673.
Database 'masterx' running the upgrade step from version 673 to version 674.
Database 'masterx' running the upgrade step from version 674 to version 675.
Database 'masterx' running the upgrade step from version 675 to version 676.
Database 'masterx' running the upgrade step from version 676 to version 677.
Database 'masterx' running the upgrade step from version 677 to version 679.
When this is complete, the database IS displayed in the SSMS tree of databases, but it says '(Recovery Pending)'.
I don't need to do much with this database besides look at a table, and possibly some triggers.
I DID attempt to do this in single user mode, but because I was not restoring to Master, this would not work.
Any ideas what I am doing wrong?
Thanks In Advance,
- Michael
>FILESTREAM Failed to find the garbage collection table.
the restore visibly is failing/missing on the filestream part.
please locate the path of the filestream file of the db:
RESTORE FILELISTONLY FROM Disk='c:\master.bak'
and add the MOVE option for that one also in your restore statement
the restore visibly is failing/missing on the filestream part.
please locate the path of the filestream file of the db:
RESTORE FILELISTONLY FROM Disk='c:\master.bak'
and add the MOVE option for that one also in your restore statement
ASKER
Should the filestream file be in addition to the data and log files? I don't see one referenced.
master C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER VER\MSSQL\ DATA\maste r.mdf D PRIMARY 4194304
mastlog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER VER\MSSQL\ DATA\mastl og.ldf L NULL 1048576
master C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
mastlog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That system no longer exists, so unfortunately that is not possible.
I had searched a bit on the internet, but couldn't find anything like this.
I fear you will have to get into touch with MS support for this ...
I fear you will have to get into touch with MS support for this ...
ASKER
Thanks, I am going to contact them and will update this with the results.
ASKER
I did contact Microsoft. We were unable to restore the database on the system that I was attempting to restore on. However, we were able to restore to a system that had a installation of SQL Server that was more comparable to the one that the database was backed up from. They were the same version (I had been attempting to restore on SQL Server 2012), and had the same configuration options (in particular, the FILESTREAM options).
Thanks for your assistance, Guy!
Thanks for your assistance, Guy!
ASKER
I did contact Microsoft. We were unable to restore the database on the system that I was attempting to restore on. However, we were able to restore to a system that had a installation of SQL Server that was more comparable to the one that the database was backed up from. They were the same version (I had been attempting to restore on SQL Server 2012), and had the same configuration options (in particular, the FILESTREAM options).
Thanks for your assistance, Guy!
Thanks for your assistance, Guy!
ASKER