Restoring the master database in SQL Server
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
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!!!