SQLBkup 3 - Disaster Recovery issues

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  February 20, 2006 12:48PM

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

  0 · Share on Twitter