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!
This was first published in February 2005