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).

-- backup the master database
backup database master
to disk = 'c:\temp\master.bak'
with init;

-- backup the msdb database
backup database msdb
to disk = 'c:\temp\msdb.bak'
with init;

-- backup the model database
backup database model
to disk = 'c:\temp\model.bak'
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).

 

 

Figure 1

 

Next step is to start the SQL Server Instance in single-user (admin) mode as shown in Figure 2.

 

 

Figure 2

 

Now let's execute the command in Listing 2 in another Command Windows.

sqlcmd -E

Listing 2

 

And then the restore SQL command (see Listing 3).

RESTORE DATABASE master
FROM DISK = 'c:\temp\master.back';
GO

Listing 3

 

Finally, let's open the SQL Server Management Studio in order to recover the msdb and model databases (see Listing 4).

restore database msdb
from disk = 'c:\temp\msdb.bak';
go
restore database model
from disk = 'c:\temp\model.bak';
go

Listing 4

 

Conclusion

 

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