Master Database Restore

Gordon

Hi

What is the correct procedure for restoring the master database via the SQL Backup GUI? I have started SQL in single user mode via the command line but cannot connect to it via the GUI as it says there is someone already connected.

I know I could convert the file to native sql format and restore in the normal way via Enterprise Manager but I'm trying to make the recovery procedures as easy as possible.

cheers

Gordon

Brian Donahue

Hi Gordon,

Yes this is a common issue and a real pain. I'd posted an article in the Knowledge Base recently about this problem.

The problem is this:

SQL Backup needs to connect to the database to get the information it needs to do a backup. When you run the backup, it needs a second connection to the SQL Server, which can't be granted in single-user mode.

Your only alternative is to use the SQB2MTF.exe utility to convert the SQL Backup file to native SQL Server format, then restore that file using Enterprise Manager.

The strategy I have adopted for our own network, though, is to back up the master database as part of the file backup strategy. Since master is in simple recovery mode, it works to simply back up the .mdf and .ldf files and restore these to the server before starting the SQL Server Service. Any file backup software that supports open file backup should let you do this. You can save yourself quite a lot of trouble backing up your master database as a group of files. You should still include master in your SQL Server backup strategy as well, though 'just in case'!

petey

You would need to use the command line version of SQL Backup to restore the master database. The GUI Restore Wizard needs at least 2 connections to work.