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
Synchronize databases on separate servers without using SANs. Learn options for server failover in SQL Server including log shipping, database ... Continue Reading
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings. Continue Reading
Find out how to restore data from a corrupt database. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.