SQLBkup 3 - Disaster Recovery issues

Brian DonahueBrian Donahue Posts: 6,590 New member
edited February 20, 2006 12:48PM in Knowledge Base
  • 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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 &#40;same disk config, etc&#41;
    'This script requires SQL Backup command-line utility from Red Gate Software
    
    
    ' ------------------ GLOBALS -------------------------
    Dim obShell
    Set obShell=WScript.CreateObject&#40;"WScript.Shell"&#41;
    
    Dim obFSO
    Set obFSO=WScript.CreateObject&#40;"Scripting.FileSystemObject"&#41;
    
    Dim sqlBackupC
    Dim strPassword
    
    'Recordset containing current file list
    Dim obFilesRS
    Set obFilesRS=WScript.CreateObject&#40;"ADODB.Recordset"&#41;
    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&#40;"HKLM\Software\Microsoft\Microsoft SQL Server\InstalledInstances"&#41;
    
    
    ' --- Do for each instance --
    For Each strInstance IN aInstances
    WScript.Echo&#40;"Recovering " &strInstance&#41;
    'If SQL Server running, stop it
    	StopSqlServer strInstance
    'Start SQL Server &#40;single-user&#41;
    	StartSqlServer strInstance, True
    'If it did not start, rebuildm
    	IF strInstance="MSSQLSERVER" Then 
    	iResult=obShell.Run&#40;"NET START MSSQLSERVER", 1, True&#41;
    	ELSE: iResult=obShell.Run&#40;"NET START MSSQL$"&strInstance, 1, True&#41;
    	End If
    	If iResult=2 Then RebuildMaster strInstance
    'restore system databases
    	RestoreSystemDatabases strInstance
    'read database list from master, restore each database &#40;exc. tempdb&#41;
    	RestoreAllDatabases strInstance
    'Start SQL Server &#40;multi-user&#41;
    	StartSqlServer strInstance, False
    
    NEXT ' --- End do for each instance ---
    
    '-- Functions --
    
    '-----------------------------------RestoreAllDatabases----------------------------------
    'Restore all databases but master
    
    Function RestoreAllDatabases&#40;InstanceName&#41;
    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="[email protected]"	
    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&#40;"ADODB.Connection"&#41;
    obMasterConnection.Open="Provider=SQLOLEDB;Data Source="&ServerName&";Initial Catalog=master;Integrated Security=SSPI"
    Set obDatabasesRS=WScript.CreateObject&#40;"ADODB.Recordset"&#41;
    Set obDatabasesRS.ActiveConnection=obMasterConnection
    obDatabasesRS.Open "Select &#91;name&#93; FROM sysdatabases WHERE &#91;name&#93; NOT IN &#40;'master','tempdb', 'msdb'&#41;"
    obDatabasesRS.MoveFirst
    Do Until obDatabasesRS.EOF
    	DatabaseName=obDatabasesRS.Fields&#40;0&#41;
    	'Populate obFilesRS
    	GetBackupFileList ServerName, DatabaseName
    	'The recordset pointer should already be set to the last Full
    	Do Until obFilesRS.BOF
    	strFile=obFilesRS.Fields&#40;0&#41;
    		strBackupType=GetBackupType&#40;obFilesRS.Fields&#40;0&#41;, InstanceName, DatabaseName, strPassword&#41;
    		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 & " &#91;"&DatabaseName&"&#93; FROM DISK='"&obFilesRS.Fields&#40;0&#41;&"'"
    		If strBackupType="Full" Then 
    			strRestoreCommand=strRestoreCommand &" WITH REPLACE, NORECOVERY"
    			ELSE: strRestoreCommand=strRestoreCommand &" WITH NORECOVERY"
    		End If
    		If LEN&#40;strPassword&#41;&gt;0 Then strRestoreCommand=strRestoreCommand & ", PASSWORD='"&strPassword&"'"
    		strRestoreCommand=strRestoreCommand &""" -E -I """ &InstanceName&""""
    		iResultCode=obShell.Run&#40;strRestoreCommand, 1 , True&#41;
    		WScript.Echo "Restored "&obFilesRS.Fields&#40;0&#41;&":"&iResultCode
    		obFilesRS.MovePrevious
    	Loop
    	'iResultCode=obShell.Run&#40;"""" &sqlBackupC& """ RESTORE DATABASE " &DatabaseName&" WITH RECOVERY", 1, True&#41;
    	obMasterConnection.Execute "RESTORE DATABASE &#91;"&DatabaseName&"&#93; WITH RECOVERY"
    SET obFilesRS=Nothing
    Set obFilesRS=WScript.CreateObject&#40;"ADODB.Recordset"&#41;
    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&#40;InstanceName&#41;
    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&#40;"%PROGRAMFILES%"&#41; &"Microsoft SQL Server\80\Tools\Binn\REBUILDM.exe"
    	If NOT obFSO.FileExists&#40;reMPath&#41; 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&#40;InstanceName&#41;
    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 &#40;exit script on fail&#41;
    		RestoreSystemDatabase "master", InstanceName, masterFile, True
    		'Start the SQL Server; restoring MASTER will stop the server.
    		StartSqlServer InstanceName, False
    	'Attempt restore of msdb &#40;exit script on fail&#41;
    		RestoreSystemDatabase "msdb", InstanceName, msdbFile, True
    	'Attempt restore of model &#40;allow fail?&#41;
    		RestoreSystemDatabase "model", InstanceName, modelFile, Flase
    END FUNCTION
    '------------------------------------ End restoresystemdatabases-----------------------------
    
    '-------------------------------------RestoreSystemDatabase-------------------------------
    'Support function for RestoreSystemDatabases
    
    Function RestoreSystemDatabase&#40;DatabaseName, InstanceName, BackupFileName, ExitOnFail&#41;
    Dim retCode, stdin
    Set stdin=WScript.StdIn
    
    'Check that we can access SQLBackupc.exe
    	sqlBackupC=obShell.ExpandEnvironmentStrings&#40;"%PROGRAMFILES%"&#41; & "\Red Gate\SQL Backup\SQLBackupC.exe"
    	If Not obFSO.FileExists&#40;sqlBackupC&#41; Then
    		stdout.WriteLine "Enter the full path to SQLBackupC.exe"
    		sqlBackupC=stdin.ReadLine
    	End If
    	retCode=obShell.Run&#40;"""" &sqlBackupC & """ -SQL ""RESTORE DATABASE &#91;" & DatabaseName & "&#93; FROM DISK='"&BackupFileName&"' WITH REPLACE"" -E -I """ &InstanceName& """", 1, True&#41;
    	If retCode=710 Then 'prompt for password
    		'Try saved password
    		retCode=obShell.Run&#40;"""" & sqlBackupC & """ -SQL ""RESTORE DATABASE &#91;" & DatabaseName & "&#93; FROM DISK='"&BackupFileName&"' WITH REPLACE, PASSWORD='"&strPassword&"'"" -E -I """ &InstanceName& """", 1, True&#41;
    		If retCode=710 Then
    			WScript.Echo "Please enter encryption password for " &DatabaseName
    			strPassword=stdin.ReadLine
    			retCode=obShell.Run&#40;"""" & sqlBackupC & """ -SQL ""RESTORE DATABASE &#91;" & DatabaseName & "&#93; FROM DISK='"&BackupFileName&"' WITH REPLACE, PASSWORD='"&strPassword&"'"" -E -I """ &InstanceName& """", 1, True&#41;
    		End If
    	End If
    	'If retCode &gt; 1 AND ExitOnFail=True Then
    '		WScript.Echo "A critical restore failed &#40;Command returned "&retCode&"&#41;."
    '		WScript.Quit&#40;1&#41;
    '	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&#40;InstanceName, SingleUser&#41;
    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&#40;"HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount&#41;
    			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&#40;"HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount&#41;
    			If Not SQLArgValue Is Nothing Then 
    				If SQLArgValue="-m" Then 
    					obShell.RegDelete&#40;"HKLM\Software\Microsoft\MSSQLSERVER\"&InstanceName&"\Parameters\SQLArg"&iCount&#41;
    					Break
    				End If
    			End If
    			iCount=iCount+1
    			WEND
    		ON ERROR GOTO 0	
    	End If
    	IF InstanceName="MSSQLSERVER" Then 
    	retVal=obShell.Run&#40;"NET START MSSQLSERVER", 1, True&#41;
    	ELSE: retVal=obShell.Run&#40;"NET START MSSQL$"&InstanceName, 1, True&#41;
    	End If
    	If retVal &gt; 2 Then 
    		WSCript.Echo&#40;"Could not start SQL Server """&InstanceName&""". Command Returned "&retVal&#41;
    		WScript.Quit&#40;1&#41;
    	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&#40;ServerName, DatabaseName&#41;
    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&#40;group&#41;, G=file, diff P=partial, Q=Partial Diff
    HistoryQuery="SELECT DISTINCT '0' CHECKBOX, b.physical_device_name, a.type, a.backup_start_date &#91;Backup Date&#93;, " &vbCrLf &_
    "  a.name &#91;Backup Name&#93;," &vbCrLf &_
    "  &#40;SELECT &#40;SUM&#40;backed_up_page_count&#41; * 8192 / 1048576&#41; FROM msdb..backupfile WHERE backup_set_id = a.backup_set_id&#41; &#91;Backed Up Size&#93;," &vbCrLf &_
    "  &#40;SELECT COUNT&#40;*&#41; FROM msdb..backupmediafamily WHERE media_set_id = a.media_set_id&#41; &#91;Files&#93;," &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 &#40;'D','L','I'&#41;" &vbCrLf &_
    "  AND a.database_name = '" & DatabaseName &"'" &vbCrLf &_
    "  AND DATEDIFF&#40;dd, a.backup_start_date, GETDATE&#40;&#41;&#41; &lt;= 7" &vbCrLf &_
    "  AND b.device_type = 7" &vbCrLf &_
    "  AND a.media_set_id NOT IN " &vbCrLf &_
    "	&#40;" &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 &gt; y.media_set_id" &vbCrLf &_
    "	&#41;" &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&#40;"ADODB.Connection"&#41;
    Set obHistoryRS=WScript.CreateObject&#40;"ADODB.RecordSet"&#41;
    
    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&#40;1&#41;
    		'Lock on the file's parent directory
    		FolderName=LEFT&#40;FileName, InStrRev&#40;FileName, "\", -1, 1&#41;&#41;
    		Set obFolder=obFSO.GetFolder&#40;FolderName&#41;
    
    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&#40;FolderName&#41;
    		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&#40;"name"&#41;=obFile.Path
    	obFilesRS&#40;"date"&#41;=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&#40;"""" &sqlBackupC&""" -SQL ""RESTORE FILELISTONLY FROM DISK='"&obFilesRS.Fields&#40;0&#41;&"'""",1,True&#41;
    	If iResult=710 Then
    		'Try again with curretn password
    		iResult=obShell.Run&#40;"""" &sqlBackupC&""" -SQL ""RESTORE FILELISTONLY FROM DISK='"&obFilesRS.Fields&#40;0&#41;&"' WITH PASSWORD='"&strPassword&"'""",1,True&#41;
    		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&#40;obFilesRS.Fields&#40;0&#41;, ServerName, DatabaseName, strPassword&#41;
    		WScript.Echo obFilesRS.Fields&#40;0&#41; &" : "&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&#40;InstanceName&#41;
    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&#40;FileName, InstanceName, DatabaseName, Password&#41;
    	Dim strCommand, obExec, strLine, itype, strDBName
    	iType=0
    	strCommand="""" & sqlBackupC &""" -SQL ""RESTORE SQBHEADERONLY FROM DISK='" &FileName&"'"
    	If LEN&#40;Password&#41;&gt;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&#40;strCommand&#41;
    
    	Do While obExec.Status = 0
    		WScript.Sleep 100
    	Loop
    
    	Do Until obExec.StdOut.AtEndOfStream
    		strLine=obExec.StdOut.ReadLine
    		If LEFT&#40;strLine, 11&#41;="Backup type" Then iType=CINT&#40;MID&#40;strLine, InStr&#40;1, strLine, ":", 1&#41;+2, 1&#41;&#41;
    		If LEFT&#40;strLine, 13&#41;="Database name" Then strDBName=MID&#40;strLine, InStr&#40;1, strLine, ":", 1&#41;+2&#41;
    	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 --------------------------
    
Sign In or Register to comment.