SQLBkup 3 - Disaster Recovery issues

Brian Donahue

• 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.

Brian Donahue

You may also find this VBScript useful. It will recover an entire SQL Server using SQL Backup, including all instances, provided all database backup files are available. Note well, it probably won't copy with split file or filegroup backup strategies!

'recoversql.vbs
'given a sql server and its' backups, restore the server and all dbs.
' Assumes you are restoring the same server (same disk config, etc)
'This script requires SQL Backup command-line utility from Red Gate Software


' ------------------ GLOBALS -------------------------
Dim obShell
Set obShell=WScript.CreateObject("WScript.Shell")

Dim obFSO
Set obFSO=WScript.CreateObject("Scripting.FileSystemObject")

Dim sqlBackupC
Dim strPassword

'Recordset containing current file list
Dim obFilesRS
Set obFilesRS=WScript.CreateObject("ADODB.Recordset")
obFilesRS.Fields.Append "name", 200, 255
obFilesRS.Fields.Append "date", 7
obFilesRS.Open


sqlBackupC="c:\program files\Red Gate\SQL Backup\SQLBACKUPC.exe"

'Get list of instances of SQL Server -- HKLM\Software\Microsoft\Microsoft SQL Server\InstalledInstances
'It's a space delimited list, MSSQLSERVER is the default
Dim strInstance, aInstances, iResult
aInstances=obShell.RegRead("HKLM\Software\Microsoft\Microsoft SQL Server\InstalledInstances")


' --- Do for each instance --
For Each strInstance IN aInstances
WScript.Echo("Recovering " &strInstance)
'If SQL Server running, stop it
	StopSqlServer strInstance
'Start SQL Server (single-user)
	StartSqlServer strInstance, True
'If it did not start, rebuildm
	IF strInstance="MSSQLSERVER" Then 
	iResult=obShell.Run("NET START MSSQLSERVER", 1, True)
	ELSE: iResult=obShell.Run("NET START MSSQL$"&strInstance, 1, True)
	End If
	If iResult=2 Then RebuildMaster strInstance
'restore system databases
	RestoreSystemDatabases strInstance
'read database list from master, restore each database (exc. tempdb)
	RestoreAllDatabases strInstance
'Start SQL Server (multi-user)
	StartSqlServer strInstance, False

NEXT ' --- End do for each instance ---

'-- Functions --

'-----------------------------------RestoreAllDatabases----------------------------------
'Restore all databases but master

Function RestoreAllDatabases(InstanceName)
Dim stdin, strBackupType, obDatabasesRS, obMasterConnection, strRestoreCommand, DatabaseName, iResultCode
Dim ServerName
Dim strFile
iCounter=0 'Count the number of records read until the full backup.
strPassword="P@ssw0rd"	
Set stdin=WScript.StdIn

If InstanceName="MSSQLSERVER" Then 
	ServerName="."
	ELSE ServerName=".\" &InstanceName
End If
WScript.Echo
'Get the list of databases
Set obMasterConnection=WScript.CreateObject("ADODB.Connection")
obMasterConnection.Open="Provider=SQLOLEDB;Data Source="&ServerName&";Initial Catalog=master;Integrated Security=SSPI"
Set obDatabasesRS=WScript.CreateObject("ADODB.Recordset")
Set obDatabasesRS.ActiveConnection=obMasterConnection
obDatabasesRS.Open "Select [name] FROM sysdatabases WHERE [name] NOT IN ('master','tempdb', 'msdb')"
obDatabasesRS.MoveFirst
Do Until obDatabasesRS.EOF
	DatabaseName=obDatabasesRS.Fields(0)
	'Populate obFilesRS
	GetBackupFileList ServerName, DatabaseName
	'The recordset pointer should already be set to the last Full
	Do Until obFilesRS.BOF
	strFile=obFilesRS.Fields(0)
		strBackupType=GetBackupType(obFilesRS.Fields(0), InstanceName, DatabaseName, strPassword)
		strRestoreCommand="""" & sqlBackupC & """ -SQL ""RESTORE"
		SELECT CASE strBackupType
			CASE "Full" strRestoreCommand=strRestoreCommand & " DATABASE"
			CASE "Log" strRestoreCommand=strRestoreCommand & " LOG"
			CASE "Differential" strRestoreCommand=strRestoreCommand & " DATABASE"
		End SELECT

		strRestoreCommand=strRestoreCommand & " ["&DatabaseName&"] FROM DISK='"&obFilesRS.Fields(0)&"'"
		If strBackupType="Full" Then 
			strRestoreCommand=strRestoreCommand &" WITH REPLACE, NORECOVERY"
			ELSE: strRestoreCommand=strRestoreCommand &" WITH NORECOVERY"
		End If
		If LEN(strPassword)>0 Then strRestoreCommand=strRestoreCommand & ", PASSWORD='"&strPassword&"'"
		strRestoreCommand=strRestoreCommand &""" -E -I """ &InstanceName&""""
		iResultCode=obShell.Run(strRestoreCommand, 1 , True)
		WScript.Echo "Restored "&obFilesRS.Fields(0)&":"&iResultCode
		obFilesRS.MovePrevious
	Loop
	'iResultCode=obShell.Run("""" &sqlBackupC& """ RESTORE DATABASE " &DatabaseName&" WITH RECOVERY", 1, True)
	obMasterConnection.Execute "RESTORE DATABASE ["&DatabaseName&"] WITH RECOVERY"
SET obFilesRS=Nothing
Set obFilesRS=WScript.CreateObject("ADODB.Recordset")
obFilesRS.Fields.Append "name", 200, 255
obFilesRS.Fields.Append "date", 7
obFilesRS.Open
obDatabasesRS.MoveNext
Loop 'databases
obDatabasesRS.Close
obMasterConnection.Close
Set obDatabasesRS=Nothing
Set obMasterConnection=Nothing
End Function
'End Restore database

' ------------------------------------RebuildMaster------------------------------------------
' Run rebuildm.exe
Function RebuildMaster(InstanceName)
StopSqlServer InstanceName
	'run rebuildm from install location
	Dim stdin, cdLocation, reMPath
	Set stdin=WScript.StdIn
	WScript.Echo "Rebuilding system databases"
	WScript.Echo "---------------------------"
	WScript.Echo "You will need the SQL Server Installation CD"
	WScript.Echo "The Rebuild will ask for the location of the master MDF and LDF files."
	reMPath=obShell.ExpandEnvironmentStrings("%PROGRAMFILES%") &"Microsoft SQL Server\80\Tools\Binn\REBUILDM.exe"
	If NOT obFSO.FileExists(reMPath) Then
		WScript.Echo "Could not find " &reMPath &". Please enter full path to rebuildm.exe"
		WScript.Echo "Example: c:\microsoft sql server\80\Tools\BINN\REBUILDM.exe"
		reMPath=stdin.ReadLine
	End If
	obShell.Run reMPath, 0, True
	'attempt server restart. On failure, exit script.
	StartSqlServer InstanceName, True
END Function
'End Rebuildm utility

'-------------------------------- RestoreSystemDatabases --------------------------------
' Ask for location of last good master, model, and msdb SQL Backups
' Restore these and restart the server in multi-user mode

Function RestoreSystemDatabases(InstanceName)
Dim stdin, stdout
Dim masterFile, modelFile, msdbFile
Set stdin=WScript.StdIn
Set stdout=WScript.StdOut
	'Prompt user for location of master full backup
		stdout.WriteLine "Please enter the full path to the latest master database backup for "&InstanceName& ":"
		masterFile = stdin.ReadLine
	'Prompt user for location of model full backup
		stdout.WriteLine "Please enter the full path to the latest model database backup for "&InstanceName& ":"
		modelFile = stdin.ReadLine
	'Prompt user for location of msdb full backup
		stdout.WriteLine "Please enter the full path to the latest msdb database backup for "&InstanceName& ":"
		msdbFile = stdin.ReadLine

	'Attempt restore of master (exit script on fail)
		RestoreSystemDatabase "master", InstanceName, masterFile, True
		'Start the SQL Server; restoring MASTER will stop the server.
		StartSqlServer InstanceName, False
	'Attempt restore of msdb (exit script on fail)
		RestoreSystemDatabase "msdb", InstanceName, msdbFile, True
	'Attempt restore of model (allow fail?)
		RestoreSystemDatabase "model", InstanceName, modelFile, Flase
END FUNCTION
'------------------------------------ End restoresystemdatabases-----------------------------

'-------------------------------------RestoreSystemDatabase-------------------------------
'Support function for RestoreSystemDatabases

Function RestoreSystemDatabase(DatabaseName, InstanceName, BackupFileName, ExitOnFail)
Dim retCode, stdin
Set stdin=WScript.StdIn

'Check that we can access SQLBackupc.exe
	sqlBackupC=obShell.ExpandEnvironmentStrings("%PROGRAMFILES%") & "\Red Gate\SQL Backup\SQLBackupC.exe"
	If Not obFSO.FileExists(sqlBackupC) Then
		stdout.WriteLine "Enter the full path to SQLBackupC.exe"
		sqlBackupC=stdin.ReadLine
	End If
	retCode=obShell.Run("""" &sqlBackupC & """ -SQL ""RESTORE DATABASE [" & DatabaseName & "] FROM DISK='"&BackupFileName&"' WITH REPLACE"" -E -I """ &InstanceName& """", 1, True)
	If retCode=710 Then 'prompt for password
		'Try saved password
		retCode=obShell.Run("""" & sqlBackupC & """ -SQL ""RESTORE DATABASE [" & DatabaseName & "] FROM DISK='"&BackupFileName&"' WITH REPLACE, PASSWORD='"&strPassword&"'"" -E -I """ &InstanceName& """", 1, True)
		If retCode=710 Then
			WScript.Echo "Please enter encryption password for " &DatabaseName
			strPassword=stdin.ReadLine
			retCode=obShell.Run("""" & sqlBackupC & """ -SQL ""RESTORE DATABASE [" & DatabaseName & "] FROM DISK='"&BackupFileName&"' WITH REPLACE, PASSWORD='"&strPassword&"'"" -E -I """ &InstanceName& """", 1, True)
		End If
	End If
	'If retCode > 1 AND ExitOnFail=True Then
'		WScript.Echo "A critical restore failed (Command returned "&retCode&")."
'		WScript.Quit(1)
'	End If
End Function

'----------------------------- StartSqlServer --------------------------------
' Modify the registry to start sql server in single-user if needed.
' Net Start the SQL Server Service

Function StartSqlServer(InstanceName, SingleUser)
Dim SQLArgValue, iCount, retVal, runningSingle
iCount=0
runningSingle=False
	If SingleUser=True Then
		ON ERROR RESUME NEXT
			While Not SQLArgValue Is Nothing
			Set SQLArgValue=Nothing
			SQLArgValue=obShell.RegRead("HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount)
			If Not SQLArgValue Is Nothing Then 
				If SQLArgValue="-m" Then
					runningSingle=True
					Break
				End If
			End If
			iCount=iCount+1
			WEND
		ON ERROR GOTO 0
	If runningSingle=False Then 
		'WScript.Echo "Writing registry -m"	
		obShell.RegWrite "HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount-1, "-m", "REG_SZ"
	End If
	End If
	If SingleUser=False Then
		ON ERROR RESUME NEXT
			While Not SQLArgValue Is Nothing
			Set SQLArgValue=Nothing
			SQLArgValue=obShell.RegRead("HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount)
			If Not SQLArgValue Is Nothing Then 
				If SQLArgValue="-m" Then 
					obShell.RegDelete("HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount)
					Break
				End If
			End If
			iCount=iCount+1
			WEND
		ON ERROR GOTO 0	
	End If
	IF InstanceName="MSSQLSERVER" Then 
	retVal=obShell.Run("NET START MSSQLSERVER", 1, True)
	ELSE: retVal=obShell.Run("NET START MSSQL$"&InstanceName, 1, True)
	End If
	If retVal > 2 Then 
		WSCript.Echo("Could not start SQL Server """&InstanceName&""". Command Returned "&retVal)
		WScript.Quit(1)
	End If
End Function
'End modify startup parameters

' ---------------------- GetBackupFileList -------------------------
' Work out the backup folder location
' Then get an enum of files
' Then examine each file and return a recordset of backup files
' starting at the last backup and proceeding to the last full backup

Function GetBackupFileList(ServerName, DatabaseName)
Dim HistoryQuery, FileName, FolderName, stdin, iResult, strBackupType
Set stdin=WScript.StdIn

'HistoryQuery returns
'CheckBox | physical_device_name | type | Backup Date | Backup Name | Backed Up Size | Files | media_set_id
'Where type: D=FULL, I=Full Differential, L=Log, F=File(group), G=file, diff P=partial, Q=Partial Diff
HistoryQuery="SELECT DISTINCT '0' CHECKBOX, b.physical_device_name, a.type, a.backup_start_date [Backup Date], " &vbCrLf &_
"  a.name [Backup Name]," &vbCrLf &_
"  (SELECT (SUM(backed_up_page_count) * 8192 / 1048576) FROM msdb..backupfile WHERE backup_set_id = a.backup_set_id) [Backed Up Size]," &vbCrLf &_
"  (SELECT COUNT(*) FROM msdb..backupmediafamily WHERE media_set_id = a.media_set_id) [Files]," &vbCrLf &_
"  a.media_set_id" &vbCrLf &_
"FROM msdb..backupset a" &vbCrLf &_
"INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id" &vbCrLf &_
"WHERE a.type IN ('D','L','I')" &vbCrLf &_
"  AND a.database_name = '" & DatabaseName &"'" &vbCrLf &_
"  AND DATEDIFF(dd, a.backup_start_date, GETDATE()) <= 7" &vbCrLf &_
"  AND b.device_type = 7" &vbCrLf &_
"  AND a.media_set_id NOT IN " &vbCrLf &_
"	(" &vbCrLf &_
"	SELECT y.media_set_id" &vbCrLf &_
"	FROM msdb..backupmediafamily x" &vbCrLf &_
"	INNER JOIN msdb..backupmediafamily y ON x.physical_device_name = y.physical_device_name" &vbCrLf &_
"	WHERE x.media_set_id > y.media_set_id" &vbCrLf &_
"	)" &vbCrLf &_
"ORDER BY backup_start_date desc"
Dim obHistoryConnection 'Connection to MSDB
Dim obHistoryRS, obFolder 'Recordset with a database's backup history

Set obHistoryConnection=WScript.CreateObject("ADODB.Connection")
Set obHistoryRS=WScript.CreateObject("ADODB.RecordSet")

obHistoryConnection.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=msdb;Integrated Security=SSPI"
obHistoryRS.Open HistoryQuery, obHistoryConnection

ON ERROR RESUME NEXT
	Set FileName=Nothing
	Set FolderName=Nothing
	Set obFolder=Nothing
	obHistoryRS.MoveFirst
	FileName=obHistoryRS.Fields(1)
		'Lock on the file's parent directory
		FolderName=LEFT(FileName, InStrRev(FileName, "\", -1, 1))
		Set obFolder=obFSO.GetFolder(FolderName)

ON ERROR GOTO 0
obHistoryRS.Close
obHistoryConnection.Close
Set obHistoryRS=Nothing
Set obHistoryConnection=Nothing

	If obFolder Is Nothing Then
		WScript.Echo "Cannot determine backup folder for " &DatabaseName&". Please enter path:"
		FolderName=stdin.ReadLine
		ON ERROR RESUME NEXT
			Set obFolder=GetFolder(FolderName)
		ON ERROR GOTO 0
	End If

Dim obFile
'Create a recordset of files that we can leverage ADO to sort
	If obFolder Is Nothing Then 
		WScript.Echo "Invalid folder. No backup files available."
		Exit Function
	End If

For Each obFile in obFolder.Files
	obFilesRS.AddNew
	obFilesRS("name")=obFile.Path
	obFilesRS("date")=obFile.DateCreated
Next
	obFilesRS.Sort="date DESC"
	obFilesRS.MoveFirst
'Set the pointer to the first full backup.
WScript.Echo "Generating backup file list for "&DatabaseName
iResult=obShell.Run("""" &sqlBackupC&""" -SQL ""RESTORE FILELISTONLY FROM DISK='"&obFilesRS.Fields(0)&"'""",1,True)
	If iResult=710 Then
		'Try again with curretn password
		iResult=obShell.Run("""" &sqlBackupC&""" -SQL ""RESTORE FILELISTONLY FROM DISK='"&obFilesRS.Fields(0)&"' WITH PASSWORD='"&strPassword&"'""",1,True)
		If iResult=710 Then
			WScript.Echo "Enter backup password for database " &DatabaseName& ":"
			strPassword=stdin.ReadLine	
		End If
	End If

	Do Until obFilesRS.EOF
		strBackupType=GetBackupType(obFilesRS.Fields(0), ServerName, DatabaseName, strPassword)
		WScript.Echo obFilesRS.Fields(0) &" : "&strBackupType
		If strBackupType="Full" Then Exit Do
	'If the backup is not for this database, remove it from the files list.
	If strBackupType="Invalid" Then obFilesRS.Delete
	obFilesRS.MoveNext
Loop
Set obFolder=Nothing
'GetBackupFileList=obFilesRS

'end read backup history
END FUNCTION

'----------------------------- End GetBackupFileList -----------------------------

'----------------------------- StopSQLServer ---------------------------
'Run NET STOP for an instance of SQL Server.

Function StopSQLServer(InstanceName)
IF InstanceName="MSSQLSERVER" Then 
		obShell.Run "NET STOP MSSQLSERVER", 1, True
	ELSE: obShell.Run "NET STOP MSSQL$"&InstanceName, 1, True
	End If
End Function

'-----------------------------End StopSqlServer --------------------------------------

' ------------- GetBackupType -----------------------
' Read a SQB file header and retrun full, differential, or log. If the header is missing, return unknown.
' Also check the database name and return invalid if the file is not one for the specified database

Function GetBackupType(FileName, InstanceName, DatabaseName, Password)
	Dim strCommand, obExec, strLine, itype, strDBName
	iType=0
	strCommand="""" & sqlBackupC &""" -SQL ""RESTORE SQBHEADERONLY FROM DISK='" &FileName&"'"
	If LEN(Password)>0 Then strCommand=strCommand &" WITH PASSWORD='"&Password&"'"
	strCommand=strCommand & """ -E -I """ &InstanceName&""""
	'Run this command and read the output. For this we need a handle on StdOut
	Set obExec=obShell.Exec(strCommand)

	Do While obExec.Status = 0
		WScript.Sleep 100
	Loop

	Do Until obExec.StdOut.AtEndOfStream
		strLine=obExec.StdOut.ReadLine
		If LEFT(strLine, 11)="Backup type" Then iType=CINT(MID(strLine, InStr(1, strLine, ":", 1)+2, 1))
		If LEFT(strLine, 13)="Database name" Then strDBName=MID(strLine, InStr(1, strLine, ":", 1)+2)
	Loop

If NOT strDBName=DatabaseName Then GetBackupType="Invalid"
	SELECT CASE iType
		CASE 0 GetBackupType="Unknown"
		CASE 1 GetBackupType="Full"
		CASE 5 GetBackupType="Differential"
		CASE 2 GetBackupType="Log"
	END SELECT

End Function

' ----------------------- End GetBackupType --------------------------