SQLBkup 3 - Disaster Recovery issues
- Date: 28 Jan 2006 (reviewed)
- Versions Affected: SQL Backup 3.0-4.0
SQL Backup requires two connections in order to restore a database via the console interface. This has some noteworthy implications when a server disaster occurs and the SQL Server's MASTER database needs to be recovered from backup.
This is because in order to restore MASTER, the server needs to be started in single-user mode by specifying the -m option in the server's startup parameters. This will cause SQL Server to only allow one connection and therefore SQL Backup cannot be used to restore the master database. Once the master database is restored, however, the server can be restarted in multi-user mode and additional databases can be restored through SQL Backup.
To work around this limitation, there are three options:
You may use the command-line utility, SQLBackupc.exe, to restore the master database. To do this, open a command prompt and run SQLBackupc -SQL "RESTORE DATABASE [master] FROM DISK='c:\backups\master.sqb' [WITH PASSWORD='password']" -I <instance> -U <username> -P <password> -E (if using Winows Authentication)
Also, you may use the SQB2MTF.exe utility included with SQL Backup to convert the SQL Backup file to a native Microsoft Tape Format (MTF) file, then use SQL Server Enterprise Manager to restore the master database from this file.
This is the usage for the sqb2mtf command:
sqb2mtf inputfile outputfile [password]
e.g. sqb2mtf "d:\backups\pubs.sqb" "e:\data\pubs.bak"
Finally, you can simply use a normal file backup package to back up the system databases as normal files. NTBackup, the built-in utility that comes with Windows 2003, can backup open SQL Server database files. When the server is restored, the system databases will already be in place so it is not necessary to restart the server in single-user mode with the -m switch.