SQL Server 2000 Restore and Recover MSDB Database

Restore the MSDB Database with a Database Backup

Description:

The msdb database stores all of our backup information and our job information. We're going to assume in this exercise that we lose the data and transaction log for the msdb database. Fortunately, we have a backup that we'll be able to restore. The msdb database restore is a typical database restore, except that you must make sure that SQL Server Agent is stopped during the restore.

(back to top)

Requirements:

  • As with all of the exercises on this site, do not perform this exercise on a production server.  Perform this exercise in a closed development environment
  • You will need to have a backup device already created - for the exercise on creating a backup device, click here
(back to top)

Setup:

None.

(back to top)

GUI Steps:

1) In Enterprise Manager, back up the msdb database by right clicking on the msdb database and choosing "All Tasks", "Backup Database".

Enterprise Manager - right clicking on Northwind Database, choosing All Tasks, and selecting Backup Database

 

2) The backup database screen appears. Choose "msdb" as the database, provide a id, and give the database a complete backup. Choose a destination device, and click the "OK" button. You will be notified that the backup operation completed successfully.

Screen Shot of SQL Server Backup Screen - provide a database name, backup name, database backup type, backup destination, and click the OK button

 

3) Find out where the msdb data and transaction log files are. In Enterprise Manager, right click on the msdb database and choose "Properties".

4) The properties menu appears. Click on the Data Files and Transaction Log Files tabs to find the file locations.

Screen shot of Data Files tab for msdb database properties

 

Screen shot of Transaction Log tab for msdb database properties

 

5) Shut down SQL Server by going into Enterprise Manager, right clicking on the Server and choosing "Stop".

Enterprise Manager screen shot, right clicking on SQL Server and choosing STOP

 

6) Delete the files that we found in step 4 from the operating system.

 

7) Restart SQL Server by right clicking on the server in Enterprise Manager (just like in step 5) and choosing "Stop".

 

8) Although it is difficult to replicate in this example, make sure that the SQL Server Agent is stopped before we begin the restore process. You can't restore a database unless you have exclusive access, and in the case of the msdb database, SQL Server Agent would ruin the exclusive access.

 

9) In Enterprise Manager, right click on the msdb database, choose "All Tasks", and "Restore Database". You will probably get multiple error messages letting you know that there is a problem with the database and it can't be opened. Just continue to click "OK" for each message.

Screen shot of right clicking on suspect msdb database, choosing All Tasks, Restore Database

 

10) After the error messages, the Restore database screen appears. Click the "From Device" radio button, then click on the "Select Devices" button. The other defaults should be fine (We're restoring a complete database backup set).

11) You are taken to a "Choose Restore Devices" screen.  Make sure that the disk radio button is selected and click the "Add" button.

Screen shot of Choose Restore Devices screen - selecting the Add button

 

12) We now choose a restore destination. We're going to click on the "Backup Device" radio button and select the same device that we backed our database to earlier in the exercise. Click the "OK" button.

Screen shot of the Choose Restore Destination screen, choosing the Backup device radio button, selecting our backup device and clicking the OK button

 

13) Just click the "OK" button when you are returned to the "Choose Restore Devices" screen. Notice that our Device name is now established.

Screen shot of Choose Restore Devices screen with correct backup device path - clicking OK button

 

14) You are returned to the "Restore Database" screen. Just click the "OK" button and the database will be restored.

Screen shot of Restore Database screen with proper device path, clicking OK button

 

15) We are notified that our restore process is complete and we're done.

Screen shot with notification that msdb database has been restored successfully

 

(back to top)

T-SQL Steps:

 

1) Backup the database in Query Analyzer.

Query Analyzer Screen shot, backing up msdb database using BACKUP DATABASE statement

 

2) While connected to the msdb database in Query Analyzer, find the path for the msdb data and transaction log files by using sp_helpfile  (The entire path is truncated in the image).

Running sp_helpfile in Query Analyzer to find out the paths for the msdb data and transaction log files

 

3) Shutdown the server from Query Analyzer.

Query Analyzer screen shot - shutting down the database using SHUTDOWN WITH NOWAIT

 

4) Delete the files from step 2 in the Operating system.

 

5) Start up SQL Server in Enterprise Manager by right clicking on the server and choosing "Start".

 

6) Although it is difficult to replicate in this example, make sure that the SQL Server Agent is stopped before we begin the restore process. You can't restore a database unless you have exclusive access, and in the case of the msdb database, SQL Server Agent would ruin the exclusive access.

 

7) We need to get a file number for our RESTORE DATABASE statement. We run the RESTORE HEADERONLY statement while connected to the master database and discover that there is only one file in our backup device, and this is the backup set that we will need to restore.

Query Analyzer screen shot - using the RESTORE HEADERONLY statement to find out which files need to be restored

 

8) Restore the msdb database while still connected to the master database in Query Analyzer, and our restore process is complete.

Using RESTORE database in query analyzer to get a complete msdb restore

(back to top)