Backup and Recovery of SQL Server System Databases

Posted at Monday, May 19, 2008
This posting covers how to backup and restore SQL Servers' system databases - master, msdb, model, etc.

In this example, I backup SQL Server's databases and then replicate a loss/corruption - and subsequent restoration - of the master database's underlying datafiles.

In SQL Server 2000, the rebuildm.exe executable was available to help you rebuild the master database so that you could start the SQL Server and then, in turn, restore the master (and related system databases) database from an available backup.

In SQL Server 2005, the rebuildm.exe executable is not available, rather the approved procedure is to call the setup.exe of the installation binaries, passing the appropriate parameters/switches, etc. in order to rebuild the master database. Afterwards, you could startup the database engine and then restore the system databases from backups. Unfortunately, this process is riddled with bugs, particularly post application of SP2.

Given the aforementioned, my preferred approach is to retain offline copies of the system databases' underlying datafiles. In the event of a loss/corruption of the master database, I simply restore these old datafile copies to the default location. This allows me to startup the database engine and restore the system databases from backups, avoiding the above flawed mechanisms. Such datafile backups should be taken at key times, such as post-upgrade, etc.

All the scripts used in this example are available in the following .zip file:

bckp_restore_sys_dbs_sql2005.zip

My example uses SQL Server 2005 - my instance is named PRIVATE-E3A52F5\SS1

Step 1 - backup_master_files.bat

I first take an offline file backup of the master database's datafiles:

net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf" "C:\backups\master.mdf"
copy /Y "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf" "C:\backups\mastlog.ldf"
net start "SQL Server (SS1)"
net start "SQL Server Agent (SS1)"

Step 2 - backup_databases.bat

Next, I backup all the SQL Server's databases via a batch file:

sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "C:\Documents and Settings\James\Desktop\backup_all_databases.sql" -o "C:\Documents and Settings\James\Desktop\backup_all_databases.out"

This batch file calls the following SQL script:

backup_all_databases.sql

--Use the foreach stored procedure  
--To backup each database to disk - except - tempdb
--Create necessary sub-directories and validate each backup
EXEC master.sys.sp_MSforeachdb @command1='
BEGIN
DECLARE @bkpfile NVARCHAR(1000), @bkploc NVARCHAR(500), @bkpname NVARCHAR(500);
DECLARE @backupsetid INT;
SET @bkploc = ''C:\backups\?\'';
PRINT @bkploc;
SET @bkpname = @bkploc + ''?_backup_''+ cast(datepart(yyyy, getdate()) as nvarchar) + cast(datepart(mm,getdate()) as nvarchar) + cast(datepart(dd, getdate()) as nvarchar) + cast(datepart(hh, getdate()) as nvarchar) + cast(datepart(mi, getdate()) as nvarchar);
PRINT @bkpname;
SET @bkpfile = @bkpname + ''.BAK'';
IF NOT (''?'' = ''tempdb'')
BEGIN
BEGIN
EXECUTE master.dbo.xp_create_subdir @bkploc;
END;

BEGIN
BACKUP DATABASE ? TO DISK = @bkpfile WITH NOFORMAT, NOINIT, NAME = @bkpname, SKIP, REWIND, NOUNLOAD;
END;

BEGIN
SELECT @backupsetid = position FROM msdb.dbo.backupset WHERE database_name=''?'' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name=''?'' );
RESTORE VERIFYONLY FROM DISK = @bkpfile WITH FILE = @backupsetid, NOUNLOAD, NOREWIND
END;

END;
END';
GO

Yielding the following output:

C:\backups\master\
C:\backups\master\master_backup_20085181746
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 354 pages in 0.517 seconds (5.599 MB/sec).
The backup set on file 1 is valid.
C:\backups\tempdb\
C:\backups\tempdb\tempdb_backup_20085181746
C:\backups\model\
C:\backups\model\model_backup_20085181746
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.285 seconds (4.426 MB/sec).
The backup set on file 1 is valid.
C:\backups\msdb\
C:\backups\msdb\msdb_backup_20085181746
Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 606 pages in 0.763 seconds (6.500 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1\
C:\backups\ReportServer$SS1\ReportServer$SS1_backup_20085181746
Processed 288 pages for database 'ReportServer$SS1', file 'ReportServer$SS1' on file 1.
Processed 1 pages for database 'ReportServer$SS1', file 'ReportServer$SS1_log' on file 1.
BACKUP DATABASE successfully processed 289 pages in 0.496 seconds (4.773 MB/sec).
The backup set on file 1 is valid.
C:\backups\ReportServer$SS1TempDB\
C:\backups\ReportServer$SS1TempDB\ReportServer$SS1TempDB_backup_20085181746
Processed 176 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB' on file 1.
Processed 1 pages for database 'ReportServer$SS1TempDB', file 'ReportServer$SS1TempDB_log' on file 1.
BACKUP DATABASE successfully processed 177 pages in 0.357 seconds (4.061 MB/sec).
The backup set on file 1 is valid.

Step 3 - destroy_master.bat

When ready to perform the test, I call this batch file to simulate a destruction/corruption of the master database.

net stop "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
del "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"

At this point, attempts to start the database engine, would yield something similar to the following with the ERRORLOG:

2008-05-18 13:09:26.73 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2008-05-18 13:09:26.73 Server (c) 2005 Microsoft Corporation.
2008-05-18 13:09:26.73 Server All rights reserved.
2008-05-18 13:09:26.73 Server Server process ID is 3360.
2008-05-18 13:09:26.73 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-05-18 13:09:26.74 Server This instance of SQL Server last reported using a process ID of 456 at 5/18/2008 1:08:35 PM (local) 5/18/2008 7:08:35 PM (UTC). This is an informational message only; no user action is required.
2008-05-18 13:09:26.74 Server Registry startup parameters:
2008-05-18 13:09:26.74 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2008-05-18 13:09:26.74 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2008-05-18 13:09:26.74 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-05-18 13:09:26.74 Server Error: 17113, Severity: 16, State: 1.
2008-05-18 13:09:26.74 Server Error 2(The system cannot find the file specified.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

Step 4 - recover_master.bat

Next, a batch file is called to perform the recovery:

net stop  "SQL Server Agent (SS1)"
net stop "SQL Server (SS1)"
net stop "SQL Server Analysis Services (SS1)"
net stop "SQL Server FullText Search (SS1)"
net stop "SQL Server Integration Services"
net stop "SQL Server Reporting Services (SS1)"
copy /Y "C:\backups\master.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf"
copy /Y "C:\backups\mastlog.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf"
net start "SQL Server (SS1)" /m
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_master.sql" -o "restore_master.out"
net start "SQL Server (SS1)"
sqlcmd -S PRIVATE-E3A52F5\SS1 -E -i "restore_databases.sql" -o "restore_databases.out"
net start "SQL Server Agent (SS1)"
net start "SQL Server Analysis Services (SS1)"
net start "SQL Server FullText Search (SS1)"
net start "SQL Server Integration Services"
net start "SQL Server Reporting Services (SS1)"

The batch file first restores the offline backups of the master database's datafiles and then performs a recovery of that database via:

restore_master.sql

RESTORE DATABASE [master] from DISK = 'C:\backups\master\master_backup_20085181746.BAK' WITH REPLACE ;
GO

Yielding the following output:

Processed 352 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

Next, the batch file restores the other system databases via:

restore_databases.sql

RESTORE DATABASE [msdb] from DISK = 'C:\backups\msdb\msdb_backup_20085181746.BAK' WITH REPLACE  ;
GO
RESTORE DATABASE [model] from DISK = 'C:\backups\model\model_backup_20085181746.BAK' WITH REPLACE ;
GO

Yielding the following output:

Processed 600 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.
RESTORE DATABASE successfully processed 606 pages in 0.531 seconds (9.341 MB/sec).
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.127 seconds (9.933 MB/sec).

At this point, your SQL Server Instance should be ready for use.


Labels: ,