Recovering the master Database

(1)

O

N

E

129

F

O

U

R

Recovering the master Database

CHAPTER OBJECTIVES

• Rebuilding and Recovering the master Database... 129

• Potential Problems in Rebuilding and Restoring

the master Database... 133

• Recovering Other Databases after master Has Been Rebuilt... 134

• Notes on Logins and Users after a master Rebuild... 137

• Program to Generate Logins from the User Database... 139

• Remapping Orphaned Users... 143

• Recovering msdb... 144

The master database has specific system information common to all data-bases (server logins, for example). If you lose your master database, chances are you are not going to start your SQL Server.

This chapter goes over the process of rebuilding your master data-base, then recovering it from a backup. Additionally, we go over some other critical areas that often get overlooked in a recovery, namely logins and user accounts getting out of synchronization. We’ll go over two tools on how to fix common problems.

We’ll also cover some other common problems that you may encounter when rebuilding the masterdatabase and how to work around them.

Rebuilding and Recovering the master Database

The masterdatabase is special. When the masterdatabase becomes damaged in any way, chances are your SQL Server is not going to work. In this section, we’ll rebuild the masterdatabase and then restore it. Remember that once you

(2)

rebuild master, you lose all information and data. So, make sure your data-bases are backed up. If your database files still exist and are recoverable, you can re-attach the databases by using the SQL EM or by using the stored proce-dure sp_attach_db. Let’s run through a typical recovery situation.

Scenario

Our system took a power hit and we lost a disk drive. When we try to start SQL Server backup, we discover that it won’t start, so we look at the error log. The default location for the errorlog is in the (. . .\LOG) directory: 2001-04-28 14:53:18.45 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)

Aug 6 2000 00:57:48

2001-04-28 14:53:18.50 server Server Process ID is 1364.

2001-04-28 14:53:18.50 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 2001-04-28 14:53:18.53 server initconfig: Error 2(The system cannot find the

file specified.) opening 'C:\Program

Files\Microsoft SQL Server\MSSQL\data\master. mdf' for configuration information.

We’ve lost the master database data file. Use the rebuildm tool locat-ed in the . . .\tools\binn directory (C:. . .\tools\binn> rebuildm.exe) to rebuild the master database.

(3)

Running the rebuildm tool displays the screens shown in Figures 4–1 through 4–5. You will be asked for information such as the location of the data files (the original data files that came on the CD), which are located in the . . .\x86\data directory on the CD. I strongly suggest you copy the files to a local hard disk before you attempt to run the rebuild. It takes some of the headaches out of the restore, although it’s not mandatory.

Figure 4–1 shows the splash screen for the Rebuild Master tool. Enter the server name and the location of the data files master.mdfand master.ldf.

Once you provide the location, and if your sort order is different from the default, change the sort orders for the databases. This information will be in the sp_helpdb and sp_helpsort that you saved before. You can also see the sort information from previous backups by looking at the header information. The backup stores this information on the backup device. In this example, we will take the defaults. Next, SQL Server will confirm that we want to rebuild the master and overwrite the system information stored in the system database as shown in Figure 4–2. Select Yes.

SQL Server will next start copying the data files from the source to the target locations. Figure 4–3 shows what you will see.

Following the files being copied over, SQL Server will configure itself so that the new masteris recognized as seen in Figure 4–4.

When these steps are completed, the exit dialog box appears as shown in Figure 4–5.

Rebuilding and Recovering the master Database

131

Figure 4–2 Rebuild Master confirmation screen.

(4)

At this point, we have a clean SQL Server, but one that does not know other databases exist.

Now it’s time to restore the backup of master from our last good backup. This will contain the locations of other databases on the server, SQL Server logins, etc.

Restoring the master Database

To restore the masterdatabase, you will need to execute the following steps:

1. Add the backup device:

sp_addumpdevice 'disk', 'master_backup', 'c:\tmp\sql_backup\master_backup.dmp'

go

2. Stop SQL Server.

3. Bring SQL Server up into single-user mode via the command line. To

do this, get to a DOS prompt, change directory into the

. . .\MSSQL\BINNdirectory, and execute the following command: C:\Program Files\Microsoft SQL Server\MSSQL\Binn> sqlservr.exe -c -m

This will start SQL Server and the output will be directed to the con-sole. Here’s a sample of what will be displayed on your console screen (it is also in the errorlog):

Figure 4–5 Rebuild Master complete. Figure 4–4 Rebuild Master configuration.

(5)

2001-04-29 15:44:08.43 spid3 SQL Server started in single user mode. Updates allowed to system catalogs. 2001-04-29 15:44:08.46 spid3 Starting up database 'master'.

4. Log into SQL Server as SQL Server user sa, or as a trusted administrator.

5. Restore the master database just as you would any other database. Note that master’s recovery mode is simple, so you will not restore any transaction logs.

restore database master from master_backup go

Here’s the output from that command:

The master database has been successfully restored. Shutting down SQL Server. SQL Server is terminating this process.

6. Restart SQL Server. Once master has been restored, SQL Server will automatically shut down. Bring it up just as you normally would.

7. Restore other user databases as needed. If your other database files are

okay, then you will not need to restore them. If other databases were damaged, or if you are rebuilding the server, you will need to restore them.

8. Resynchronize SQL Server logins and users as needed. If everything is

a clean restore, then there are no orphaned users and logins. If there are orphans, then see the sections on creating logins/users and resyn-chronizing them.

Potential Problems in Rebuilding and Restoring

the master Database

The following are three potential rebuilding and restoring problems and fixes:

PROBLEM 1: • The rebuildm.exe tool gets caught in an infinite loop. It should only take a few minutes to rebuild.

FIX: • Rebuildm.exe may have problems with the read-only attributes on the CD for the master data files. Copy them to a local hard drive and then run the following command:

C:\tmp\master> attrib -r *.*

This will take the read-only attributes off and the rebuildm.exe

should run.

PROBLEM 2: • You try and restore the master from a backup and you get the following error:

(6)

restore database master from master_backup go

Server: Msg 3108, Level 16, State 1, Line 1

RESTORE DATABASE must be used in single user mode when trying to restore the master database.

Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

FIX: • Put the SQL Server into single-user mode via the command line. Execute the following from the BINNdirectory:

sqlservr.exe -c -m

PROBLEM3: • Trying to put the master database into single-user mode via

sp_dboptionor ALTER DATABASEresults in the following error: sp_dboption master, 'single user', true

go /*

Option 'SINGLE_USER' cannot be set in database 'MASTER'. sp_dboption command failed.

*/

FIX: • Place the SQL Server into single-user mode via the command line. You need to stop SQL Server and restart it by executing the following from the BINNdirectory:

sqlservr.exe -c -m

Recovering Other Databases after master Has Been Rebuilt

This section deals with the scenario of losing a master database and not hav-ing a back up of it.

Once masterhas been rebuilt, you have a freshly installed server. SQL Server at this point does not know anything about your previous databases that were on this machine. If you had a backup of master you would restore it now. If you did not, you have two choices: Either restore the databases from tape or disk or use SQL EM or the sp_attach_dbstored procedure to re-associate the database files at the OS level back to the database.

Tip

If your other data files appear to be okay (meaning the disk drives were not damaged), I strongly suggest that you first try to use the sp_attach_dbprocedure because it is a lot faster to attach pre-existing files than to restore a whole database. The reason is that a restore will have to lay all the bits back on the disk, while the sp_attach_dbstored procedure just puts a few entries back in the system tables so SQL Server will recognize the database. The worst thing that can happen is that sp_attach_dbdoesn’t work.

(7)

Recovering Other Databases after master Has Been Rebuilt

135

Then, you drop the database and restore from tape. The worst thing is that you lose five minutes; the best is you save several hours of restore. If you proactively create the

sp_attach_dbscripts, it will make recovery even faster. Also, attaching the existing databases allows you to back up the tail of the log.

Using the RESTORE Command Method

1. Add the backup devices. 2. Restore the database(s).

3. Synchronize the users if necessary (use sp_fixlogins later in the chapter).

use master go

sp_addumpdevice 'disk', 'full_backup', 'c:\tmp\sql_backup\full_backup.dat' go

sp_addumpdevice 'disk', 'log_backup', 'c:\tmp\sql_backup\log_backup.dat' go

backup database mcbath to full_backup with init go

backup log mcbath to log_backup with init go

–– boom... lose your master database...

–– use the rebuildm.exe tool to rebuild your master database –– then load your database back from tape

use master go

sp_addumpdevice 'disk', 'full_backup', 'c:\tmp\sql_backup\full_backup.dat' go

sp_addumpdevice 'disk', 'log_backup', 'c:\tmp\sql_backup\log_backup.dat' go

restore database mcbath from full_backup go

restore log mcbath from log_backup go

Using the sp_attach_db Method

You need to know the sources for the database files. These can be located from a variety of sources:

•

Old database logs

•

sp_helpdb

•

Using a dircommand to look for files in the data file directory Here is an example of attaching data files using sp_attach_db: sp_helpdb mcbath

(8)

/*

name db_size owner

compatibility_level

mcbath 61.00 MB sa

name fileid filename filegroup size

mcbath_Data0 1 c:\tmp\sql_data\data0.mdf PRIMARY 1024 KB

mcbath_Log 2 c:\tmp\sql_log\log0.ldf NULL 5120 KB

mcbath_data1 3 c:\tmp\sql_data\data1.ndf data 10240 KB

mcbath_index0 4 c:\tmp\sql_data\index0.ndf index 10240 KB

mcbath_data2 5 c:\tmp\sql_data\data2.ndf data 5120 KB

test_filegroup_file0 6 c:\tmp\sql_data\test_filegroup0.ndf test_filegroup 10240 KB test_filegroup_file1 7 c:\tmp\sql_data\test_filegroup1.ndf test_filegroup 10240 KB test_filegroup_file2 8 c:\tmp\sql_data\test_filegroup2.ndf test_filegroup 10240 KB */

–– we could have just added the files via sp_attach_db which is *MUCH* faster than a restore –– because it’s just adding the entries into system tables as opposed to loading data into –– the database. note, we are assuming the data files are ok. run dbcc checkdb just to be sure –– once the database is up and running.

use master go

select name from sysdatabases go

sp_attach_db @dbname = 'mcbath',

@filename1 = 'c:\tmp\sql_data\data0.mdf', @filename2 = 'c:\tmp\sql_data\data1.ndf', @filename3 = 'c:\tmp\sql_data\data2.ndf', @filename4 = 'c:\tmp\sql_data\index0.ndf', @filename5 = 'c:\tmp\sql_data\test_filegroup0.ndf', @filename6 = 'c:\tmp\sql_data\test_filegroup1.ndf', @filename7 = 'c:\tmp\sql_data\test_filegroup2.ndf', @filename8 = 'c:\tmp\sql_log\log0.ldf' go

select name from sysdatabases go /*--Before name master tempdb model msdb pubs Northwind (6 row(s) affected)

(9)

--After name master tempdb model msdb pubs Northwind mcbath (7 row(s) affected) */

Notes on Logins and Users after a master Rebuild

If you have backed up your master database and user databases at the same time, then restoring will be easy. But if for some reason you didn’t back up your

master, or can’t recover it from a backup, things could get tricky. Specifically, user logins for the server may be out of synchronization with the users in the database. The following discussion will walk you through this scenario.

For purposes of discussion, let’s assume the following:

•

You do not have the latest copy of the masterdatabase.

•

You have added a whole lot of server logins and users to the

database.

After you do a rebuild of the masterdatabase and then restore it, you may encounter problems with users trying to log in. This is because the users no longer have logins in the server, but they do still have user assignments in the user database.

What’s important is that the SIDs are the same in both tables. For exam-ple, I could have the syslogins and sysusers tables match up by name, but not SID number. If this happened, users would not be able to log in. Here’s an example of that:

print 'master..syslogins:'

select name, sid from master..syslogins order by name print 'mcbath..sysusers:'

select name, sid from mcbath..sysusers where

sid <> 0x01 and sid <> 0x00 and sid is not NULL order by name

(10)

master..syslogins: name sid BUILTIN\Administrators 0x01020000000000052000000020020000 sa 0x01 test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x1CF91F72D5C03C49A982EBCDA3756F9B test_login_2 0xFB518D2DCD41F54088F1A613B0F0E9E1 test_login_3 0x779A044E08943544A53E1D4E604A27AD test_login_4 0x4DB074BF2B52EF40AA7982D11528C268 test_login_5 0xED863C488BF15E4EA5CB677DCA0F7798 (8 row(s) affected) mcbath..sysusers: name sid test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8 (6 row(s) affected)

For users to log in, they need to have the syslogins, sysusers, and SID numbers in the tables match up. Here’s an example of what a successful restore looks like:

master..syslogins: name sid BUILTIN\Administrators 0x01020000000000052000000020020000 sa 0x01 test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8 mcbath..sysusers: name sid test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8

(11)

You can fix these errors by several methods:

•

Drop all users from the user database. Drop the logins from the server. Then, recreate them either by a script that you had ready for such an occasion or enter them in manually. This method is the least risky, but takes the most time.

•

Insert the missing logins into the server, and then update the system tables via an update statement that synchronizes the SID numbers. This is higher risk.

•

Run a script that creates the logins in the server based on the users in the database, and then resynchronizes the SID numbers via system stored procedures and/or scripts. This is probably the fastest and safest method.

Regardless, users will get new passwords. You just need to decide if they are going to be NULL or fabricated. For security reasons, I suggest ran-dom passwords.

Program to Generate Logins from the User Database

This is the stored procedure to create logins in the server from a restored database that had orphaned users. There are two modes you can run it in, test and run. Test mode will show you the output before it makes any changes. This way, you can check that the passwords and logins are correct. Run mode will actually execute the changes and update the system tables.

Here are the combinations in which you can run it:

exec fix_logins @random='RANDOM', @run='TEST' exec fix_logins @random='RANDOM', @run='RUN' exec fix_logins @random='', @run='TEST' exec fix_logins @random='', @run='RUN'

If you don’t specify to use random passwords, then the password will be “password.” Note, the random password uses the RAND() function. For better password generation, use a better RAND()seed if you need it.

create proc fix_logins @random varchar(20)='', @run varchar(20)='TEST' as

begin

set nocount on

declare @no_users int declare @user_name sysname declare @dbname sysname

(12)

declare @def_lang sysname declare @counter int declare @name sysname declare @sid varbinary(85) declare @pw varchar(20)

if (upper(@run) <> 'RUN') begin

print ''

print '**************************'

print '* Test Run *'

print '**************************' print ''

end

select @no_users = count(*) from sysusers

where

sid <> 0x01 and sid <> 0x00 and sid is not NULL

select @dbname = db_name(dbid) from master..sysprocesses where spid=@@spid

select @def_lang =a.name

from master..syslanguages a, master..sysconfigures b where

b.comment = 'default language' and b.value = a.langid

select @counter=1

declare sysusers_cursor cursor for select name, sid

from sysusers where

sid <> 0x01 and sid <> 0x00 and sid is not NULL order by name

open sysusers_cursor

fetch next from sysusers_cursor into @name, @sid

while (@@FETCH_STATUS = 0) begin select @counter=@counter + 1 if (upper(@random) = 'RANDOM') begin select @pw=convert(varchar(50),RAND(@counter)) select @pw=substring(@pw,3,5) end else begin select @pw='password'

(13)

end

if (upper(@run) = 'RUN') begin

exec sp_addlogin @name, @pw, @dbname, @def_lang, @sid end

select 'Adding Login: ', @name, @pw, @dbname, @def_lang, @sid

fetch next from sysusers_cursor into @name, @sid end

close sysusers_cursor deallocate sysusers_cursor

print 'master..syslogins:'

select name, sid from master..syslogins order by name select @dbname=@dbname+'..sysusers'

print '' print @dbname select name, sid from sysusers where

sid <> 0x01 and sid <> 0x00 and sid is not NULL order by name set nocount off end

Here is the output when the logins and users are not synchronized between the user’s database and the masterdatabase:

exec fix_logins @random='RANDOM', @run='TEST' go

***************************

* Test Run *

***************************

Adding Login: test_login 71361 mcbath us_english

0x80559A87BC2B7B49BA43EF92B6EDF87E

Adding Login: test_login_1 71362 mcbath us_english

0x85B6F41D4C681847B475097DAC1BA085

0x89EBE069EAC0614EA33F8C4EA283C889

0xC657B3EC122ED64883F517CA717728F7

0x3E1654A4E8AA634DA5E3DAFBAFD84FA4

(14)

Adding Login: test_login_5 71370 mcbath us_english 0xE91D1C6414BE4C4E8CD9EB6502609BB8 master..syslogins: name sid BUILTIN\Administrators 0x01020000000000052000000020020000 sa 0x01 mcbath..sysusers: name sid test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8

Following is the output when the logins and users have been synchro-nized between the user’s database and the master database using the stored procedure:

exec fix_logins @random='RANDOM', @run='RUN' go

New login created.

Adding Login: test_login 71361 mcbath us_english

0x80559A87BC2B7B49BA43EF92B6EDF87E New login created.

0x85B6F41D4C681847B475097DAC1BA085 New login created.

0x89EBE069EAC0614EA33F8C4EA283C889 New login created.

0xC657B3EC122ED64883F517CA717728F7 New login created.

0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 New login created.

0xE91D1C6414BE4C4E8CD9EB6502609BB8 master..syslogins:

name sid

(15)

sa 0x01 test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8 mcbath..sysusers: name sid test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8

Tip

To avoid these types of ugly problems, back up your masterdatabase (which is very

small) every time you back up your user database. Also, document your system very well. Have scripts that work and have been tested well in advance.

Remapping Orphaned Users

Quite often after you restore a database, there are user login, security, and permission problems because SQL Server requires a database login and you also have to be a user of the database. When you restore a database, you are quite often just restoring users to the database and not restoring server logins. Thus, you end up in a situation that I call orphaned users: a database with users but a server with different login SIDs for those users.

SQL Server provides a stored procedure (sp_change_users_login) to synchronize users and logins, but it can be tedious to use if there are more than a few users. The following is a script that automates much of this and helps on large databases with many users. It matches up users to SQL Server logins by name and then links the SIDs together so there are no orphaned users. What’s important to remember is that the user and login have to pre-exist. If they don’t, read the section “Notes on Logins and Users after A master Rebuild,” which goes over creating logins when only users exist.

CREATE PROCEDURE dbo.sp_fixusers AS

BEGIN

DECLARE @username varchar(25) DECLARE fixusers CURSOR FOR

SELECT UserName = name FROM sysusers

(16)

WHERE issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers INTO @username

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC sp_change_users_login 'update_one', @username, @username

FETCH NEXT FROM fixusers INTO @username

END

CLOSE fixusers DEALLOCATE fixusers END

Note

It is important to go over the final results to ensure that the security for each user is correct. The tool will attempt to match users and logins properly, but there is a chance that a user could be mismatched and get the wrong level of security. Always check your work.

Recovering msdb

The msdb database contains system-wide information such as log shipping, replication information, backup history, and marked transaction information. Thus, it is critical that you back it up as part of your recovery strategy. Probably the biggest distinction with backing up msdb up to this point is that msdb is in simple mode (by default), which doesn’t allow backups of trans-action logs. This makes recovery faster because there is less to do.

Process

1. Back up the msdb database.

2. Corruption happens. In this case, a data file could not be opened, as

seen in the errorlog.

3. Since this is a simple recovery model, all we have to apply is the full

backup and no transaction logs.

msdb is a system database, thus you cannot drop it. Here’s the error you would receive if you tried:

Note

The default recovery model for msdbis simple. It can be changed to full. For this

(17)

Cannot drop the database. 'msdb' because it is a system database.

Script

use master go

backup database msdb to full_backup with init go

/*

Processed 1496 pages for database 'msdb', file 'MSDBData' on file 1. Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.

BACKUP DATABASE successfully processed 1497 pages in 13,361 seconds (0.917 MB/sec).

*/

/*

sql server was stopped and started

from the errorlog, we see that MSDB will not come up. A file is missing:

2001-04-23 20:02:37.37 spid8 Starting up database 'msdb'. 2001-04-23 20:02:37.37 spid9 Starting up database 'pubs'. 2001-04-23 20:02:37.37 spid10 Starting up database 'Northwind'. 2001-04-23 20:02:37.37 spid11 Starting up database 'mcbath'.

2001-04-23 20:02:37.37 spid8 udopen: Operating system error 2(The sys-tem cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL;data;msdbdata.mdf.

2001-04-23 20:02:37.59 spid8 FCB: Open failed: Could not open device C:\Program Files\Microsoft SQL Server/MSSQL/data/msdbdata.mdf for virtual device number (VDN) 1.

2001-04-23 02:02:37.75 spid8 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL;data;msdbdata.mdf' may be incorrect.

*/

restore database msdb from full_backup go

*/

Processed 1496 pages for database 'msdb', file 'MSDBData' on file 1. Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.

RESTORE DATABASE successfully processed 1497 pages in 13.653 seconds (0.897 MB/sec).

*/

(18)

select name, dbid from master..sysdatabases go /* name dbid master 1 tempdb 2 model 3 msdb 4 pubs 5 Northwind 6 mcbath 7 (7 row(s) affected) */

Summary

By having a carefully documented and tested recovery plan, restoring a serv-er aftserv-er a comprehensive failure doesn’t have to be a gut-wrenching expserv-er- exper-ience.

I strongly suggest that at the very least, you back up the master and

msdb databases in addition to your user databases with every backup. Both databases are very small and will make your recovery experience easier.

If possible, you could also back up the databases (master and msdb) to a local disk drive and tape. This would make recovery even faster. Since both are small, it would be easy and fast. As a precaution, you would need to put the backup on a different disk than the database—if you lost the data-base disk, you would also lose your disk backups!

Using this chapter in conjunction with the previous chapter, you should have most critical recovery scenarios covered.