Did you ever had to restore your system databases? It’s not that difficult when the databases are still available (or at least some version of it). However, what do you need to do when all system databases are gone?
For example when your system database LUN is missing J. Now, Books Online is not really explaining this in detail so I’ve made this blog post to make it clearer.
Before you read on, please make sure that you have a full backup of all your system databases.
Restore MASTER database
SQL Server requires the master database before a SQL Server instance can run at all. Some version of a master database must exist. Without that “version” the service will not start and it will be impossible to restore the backup.
So, the first step is to rebuild the system databases.
To rebuild the system databases, you can follow these steps on http://msdn.microsoft.com/en-us/library/dd207003.aspx
Insert the SQL Server installation media into the disk drive or mount the iso-file.
From a command prompt window (run as administrator), enter the following command.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<InstanceName> /SQLSYSADMINACCOUNTS=<accounts>
<InstanceName> Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.
<accounts> Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role.
When the rebuild is finished, you will find new (empty) data and log files in the data folder of the instance.
Once you have the “dummy” master.mdf and ldf file in place, you need to start up the instance in single user mode. This is done by adding the –m startup parameter. Open the SQL Server Configuration Manager to add it. (see screenshot below)
In the SQL Server Configuration Manager, start the SQL Server service. Once it is started we finally can restore the master database. The example starts sqlcmd and executes a RESTORE DATABASE statement that restores a full database backup of master from a disk device.
The REPLACE option will replace the current master database with the one in the backup file. Also notice that after the restore, the instance is shutting down. Remove the –m option again from the startup parameters before you restart SQL Server. You will see that all your user databases are back online and that all your server logins exist.
Restore MASTER database on a failover cluster
The procedure is slightly different than the restore of a stand-alone instance. The rebuild of the system databases must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure. I’m trying to restore the master database on a Windows 2012 R2 Failover Cluster and I’m running SQL Server 2012 SP2 Enterprise Edition.
I started with a rebuild of the system databases and started up the SQL Server instance with –m parameter (single user mode), as described above.
To restore the master database, I open a SQL CMD but I always get the error that only one administrator can connect to the instance. The problem is that as soon as I startup the service, the sp_server_diagnostics process kicks in and takes the first connection…
After searching for a while, I figured out that you have to startup a cluster instance in single user mode in a different way. More information can be found on http://msdn.microsoft.com/en-us/library/ms188236.aspx . Check the Note for Clustered Installations.
Restore MASTER database alternative
Another alternative method to restore the MASTER database could be the following:
- Startup the instance with the new empty master database after the rebuild of the system databases
- Restore the original master database as a user database (use a different name)
- Shut down the service again
- Remove the master.mdf and ldf files.
- Rename the restored master database back to master.mdf and mastlog.ldf
- Restart the service
The method above is also working but the above mentioned procedures are the best practices for SQL Server.
Restore MODEL database
The model database can be restored as a normal database. However, if the database should be corrupt, you have to start up the SQL Server Instance with trace flag –T3608. It prevents SQL Server from automatically starting and recovering any database except the master database.
To activate the trace flag, open the SQL Server Configuration manager and add it to the startup parameters.
(Re)start your SQL Server Instance. Do not forget to remove this trace flag after the model database has been restored.
Open the SQL Server Management Studio and restore the original mode database from the backup
Restore MSDB database
Before you can restore the MSDB, you’ll have to stop the SQL Server Agent. You can do this with the SQL Server Configuration manager or just right click on the agent in the SSMS and click stop
The MSDB database can be restored like a normal user database.
Once the restore is finished, you can restart the SQL Server Agent and you should see all your jobs again in the SSMS.
Restore Tempdb database
You cannot backup or restore the tempdb. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently.
If your tempdb is corrupt, just restart your SQL Server Instance. In case, your disk is corrupt, you will have to move your tempdb. More information on how to move system databases can be found here http://msdn.microsoft.com/en-us/library/ms345408.aspx
Restore RESOURCE database
It is a hidden database. The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, SQL Server cannot back up the Resource database. It can be restored by a file-level restore in Windows or by running the setup program for SQL Server.