Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

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.

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

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 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)
*/
  • + Share This
  • 🔖 Save To Your Account