USE Tempdb GO SELECT GETDATE() GO SELECT @@SERVERNAME GO BACKUP DATABASE Master TO DISK = 'D:Program FilesMicrosoft SQL ServerMSSQLBackupsMaster.bak' WITH INIT GO BACKUP DATABASE MSDB TO DISK = 'D:Program FilesMicrosoft SQL ServerMSSQLBackupsMsdb.bak' WITH INIT GO BACKUP DATABASE Model TO DISK = 'D:Program FilesMicrosoft SQL ServerMSSQLBackupsModel.bak' WITH INIT GO SELECT GETDATE() GOThe backups should be saved locally on a redundant disk drive separate from the online databases and backed up to tape on a regular basis. Additionally, if space is available on a file server or secondary server participating in log shipping, it may be beneficial to copy the system database backups from the primary server to the secondary server.
As valuable as the backups are, without a reliable and regularly tested restore process the backups may be useless....
Unfortunately the process to restore a system database, especially master is not as simple as a user database due to the overall platform configurations. Check out restoring the master database from a current backup in SQL Server 2000 Books Online for the details. Good luck!
Dig Deeper on SQL Server Backup and Recovery
Related Q&A from Greg Robidoux
See how to restore a database after receiving a server error message by first running this command SQL Server as advised by our expert. Continue Reading
Expert Greg Robidoux explains how to restore MSDB on another server while also allowing users access. Continue Reading
Find out how to run a T-SQL query to loop through all databases. Continue Reading