Like most DBA’s will know (or at least some unfortunate one’s) when disaster strikes the last thing to do is panic, but its also a time when you need to be confident you’re able to carry out database restores quickly and proficiently.
For most DBA’s this is bread and butter stuff and would quickly knock out the required RESTORE DATABASE scripts to get the databases back operational.
However, there could well be a time when you need to restore the databases onto a newly build SQL instance. In this scenario you’d need to restore the Master database in order to restore configurations and logins from the previous SQL instance. Simple I hear you say, think again, restoring the master database needs to be carried out in a certain way with various steps and can’t simply be restored in the same way as other databases.
So in this blog post I’m going to demonstrate the steps needed to restore the Master database.
Firstly and most crucially we need to locate the last full backup of the master database for the SQL instance you require, If you don’t backup your system databases then I’d suggest you start doing so or you’ll be looking for a new career when disaster strikes.
Next we need to fire up a command prompt (running in administrator mode) to carry out the restore.
Once the Command prompt is open we’ll start by stopping the SQL instance database engine service by using the NET STOP MSSQLSERVER command.
**Note: if your doing this for a named instance then you’d need to have the named instance service name not the default instance shown below**
Now the service has stopped we then need to restart the service in single user mode, this is achieved by using the startup code /m.
NET START [SQL Instance Service] /m (for other startup codes see link)
With the service now started in single user mode we need to connect to the SQL instance using SQLCMD.
SQLCmd -S [SQLinstanceName] -E (The -E flag means for a is a trusted connection i.e. Windows Auth)
Now we have a connection on the instance we’re in a position to start issuing any T-SQL command, in this case we’re going to issue a RESTORE DATABASE command like so.
After the restore has completed, we have one last step which is to restart the SQL Instance again.
So we’ll start with the NET STOP MSSQLSERVER command first. Then we’ll start the service up again, but this time we don’t need to start the service in single user mode so simple issue a NET START MSSQLSERVER command like below.
Once the service has started your SQL Instance will be back operational with all configurations and logins from the old instance now restored.
So to summarize, restoring the master database isn’t as simple as restoring all other databases. Therefore I’d advise people to have a little practice every once in a while, as you’ll definitely not want to be frantically googling for this information when your boss is stood over your shoulder wanting the databases back online urgently.
Hope this is helpful to some folks out there.