Restoring the master database in SQL Server November 10, 2013Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: restore master db
I had a seminar in SQL Server 2012 recently and one of the people in my class asked me what is the best way to restore a master database
In this post I will like to shed some light on the issue.
As we all know we must include in our backup strategy, the system databases backup. We must backup the master and msdb databases regularly.
If for some reason the master database becomes corrupt or missing , the SQL Server instance cannot be started.
Master and tempdb cannot be repaired. Repair requires single-user mode, since master and tempdb cannot be put into single-user mode.
If your SQL Server master database becomes corrupt, some people suggest to rebuild the master database, then start SQL Server, then restore the backup of the master database. I do not suggest rebuilding the master database.That is really time-consuming and you need to do a lot of trial-and-error, especially if have a cluster disk subsystem.Some people recommend the full re-installation of the SQL Server. Well that does not sound ideal as well.
Instead, you can restore a backup of the master database on another instance of SQL Server as long as it’s the same version of SQL Server, then use the restored files to replace the corrupt files on the broken system.
- An easier solution would be to restore the backup of the master database backup to another instance of SQL Server
- In the section of the Restore Database dialog box, use a different database name such as attempted_recovery_master to avoid conflict with the master database on the SQL Server you are restoring.
- The master .mdf/.ldf files will be be named attempted_recovery_master.mdf and attempted_recovery_master_1.ldf.
- We then need to detach the attempted_recovery_master database
- Then we need to copy the attempted_recovery_master.mdf and the attempted_recovery_master_1.ldf and then paste them to the instance of SQL Server with the corrupted master database.
- We then must delete the corrupted master.mdf and mastlog.ldf file
- Then we need to rename attempted_recovery_master.mdf to master.mdf and rename attempted_recovery_master_1.ldf to mastlog.ldf.
- We can now safely restart the SQL Server service
One thing to note is that the SQL Server versions of the two instances must match. You cannot restore a master database backup from SQL Server 2005 to SQL Server 2008,detach it and then try to restore it to a SQL Server 2005 instance.I will not work.
It is always better to use a restore to fix system database/table corruption.
Hope it helps!!!