msdb and master Backup and Restore 1

[donutman]

Here's what I want to accomplish. I'm upgrading my file server OS to 2003. I want to start with a clean slate to correct a license problem that has dogged my network upgrade from NT to 2K. So here's my current strategy.

1) Backup "my" databases and restore them to an existing "backup" file server to prove that the backup is solid.

2) Clean install of 2003 Server on the SQL box.

3) Install SQL Server, SP etc.

4) Restore Databases.

5) Here's the part I'm not sure about: Restore msdb database.

Will #5 create all of the scheduled jobs and maintenance plans? Should the master database be restored too?
-Karl

 

[SQLBill]

Here is probably the 'best' way to backup and restore databases when you know in advance you are going to have to do that.

Detach all the user databases.
Detach all the system databases (this requires master to be put into single user mode via OSQL).
Copy the .mdf and .ldf files (also .ndf if you partition the data files) to another location or tape.
Do the new install.
From the new install, detach all the system databases.
Move the original database/log files to the new location and overwrite the 'new' system files with the original ones.
Attach all the databases.

-SQLBill

 

[donutman]

Thanks!
1) If you can just copy the .ldf and .mdf files over, then why is it necessary to detach the databases? If you shut down SQL Server, aren't the databases detached as much as they ever can be?

2) With OSQL can you put it in Single User mode without shutting down SQL Server? The help files says to restart it with sqlserv.exe -c -m

3) When you say overwrite the "new" system files, you are referring to the .ldf and .mdf files right?

4) Do you know the answer to the msdb and master db question?
-Karl

 

[SQLBill]

1. Yes, but sometimes SQL Server is 'picky' about it. I always detach whenever I can. But if it's not an option (detaching), then copying the files after a shutdown should work.

2. I believe that command makes the change without shutting SQL Server down.

3. correct. When you rebuild the SQL Server, there will be 'new' system databases (master, msdb, model, tempdb) and their .mdf/.ldf files. You won't need those, you'll need the 'old' ones. So overwrite/replace the 'new' ones with the 'old' ones.

4. Yes, it should keep the logins and jobs IF you use your 'old' master, msdb files instead of the new files.

By old files, I mean the ones from the current install. The new files I refer to are the ones created by the new install.

-SQLBill