Backup DTS of SQL server March 31, 2011Posted by jbanju in DB MS Sql 2000.
Just backup msdb database. They are all there, in sysdts… tables.
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.
1) In Enterprise Manager, back up the msdb database by right clicking on the msdb database and choosing “All Tasks”, “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.
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.
5) Shut down SQL Server by going into Enterprise Manager, right clicking on the 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.
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.
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.
13) Just click the “OK” button when you are returned to the “Choose Restore Devices” screen. Notice that our Device name is now established.
14) You are returned to the “Restore Database” screen. Just click the “OK” button and the database will be restored.
15) We are notified that our restore process is complete and we’re done.