- Rebuilding and Recovering the <tt>master</tt> Database
- Potential Problems in Rebuilding and Restoring the <tt>master</tt> Database
- Recovering Other Databases after <tt>master </tt> Has Been Rebuilt
- Notes on Logins and Users after a <tt>master</tt> Rebuild
- Program to Generate Logins from the User Database
- Remapping Orphaned Users
- 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 transaction logs. This makes recovery faster because there is less to do.
Back up the msdb database.
Corruption happens. In this case, a data file could not be opened, as seen in the errorlog.
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:
The default recovery model for msdb is simple. It can be changed to full. For this example, I use the default.
Server: Msg 3708, Level 16, State 6, Line 1 Cannot drop the database. 'msdb' because it is a system database.
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 system 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).
*/ 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) */