Backup and recovering system databases

Introduction

 
The master, msdb, and model databases are the corner stone of a SQL Server instance. A SQL Server instance does not work in an appropriate way if one of these databases is corrupt. Therefore, it's very important to have a backup of these databases in order to be ready to recovery a SQL instance in the event of a failure. A strategy backup to follow is a full backup of these databases because they don't change frequently. However, when major changes occur in the SQL instance such as the creation of a new database or setting changes, we need to do a full backup of these databases. In this article, I will cover how to backup and recover system databases.
 
Getting started with the solution
 
When a SQL Server instance fails, the process to restore the instance is to firstly recover the master database as follows.
 
First step is to back up the system databases such as master, msdb, and model (see Listing 1).
  1. -- backup the master database  
  2. backup database master  
  3. to disk = 'c:\temp\master.bak'  
  4. with init;  
  5.   
  6. -- backup the msdb database  
  7. backup database msdb  
  8. to disk = 'c:\temp\msdb.bak'  
  9. with init;  
  10.   
  11. -- backup the model database  
  12. backup database model  
  13. to disk = 'c:\temp\model.bak'  
  14. with init; 
Listing 1
 
Now let's supposed that the system databases are corrupt, but the binary files of the SQL Server 2005 instance are correct. Then, to simulate this scenario, let's stop the instance of the SQL Server 2005.
 
Now in order to recover the system databases, then go to the directory of the SQL Express Instance's configuration (c:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn). Open a Command Window and change to this directory (see Figure 1).
 
figure1.gif
 
Figure 1
 
Next step is to start the SQL Server Instance in single-user (admin) mode as shown in Figure 2.
 
figure2.gif
 
Figure 2
 
Now let's execute the command in Listing 2 in another Command Windows.
  1. sqlcmd -E 
Listing 2
 
And then the restore SQL command (see Listing 3).
  1. RESTORE DATABASE master  
  2. FROM DISK = 'c:\temp\master.back';  
  3. GO 
Listing 3
 
Finally, let's open the SQL Server Management Studio in order to recover the msdb and model databases (see Listing 4).
  1. restore database msdb  
  2. from disk = 'c:\temp\msdb.bak';  
  3. go  
  4. restore database model  
  5. from disk = 'c:\temp\model.bak';  
  6. go 
Listing 4
 

Conclusion

 
In this article, I covered how to backup and recover system databases such as master, msdb, and model databases.