microsoft:scripting:vbscript:scripts:sqlmirroring

Version 2

The below script should be executed with cscript on the source server.
It takes care of setting up database mirroring for one or all databases on a sourceserver.
Modify the directory and server variables before executing.
The account running this script has to have the correct perissions on both servers.

To mirror all databases:

cscript SQLMirroring.vbs

To mirror just a single database (can be used multiple times to mirror only some databases):

cscript SQLMirroring.vbs databasename

'SQLMirroring.vbs
'For configuring mirroring of databases between strSourceServer and strTargetServer.
'This script needs to be run on strSourceServer.
'20091121, v1.
'20111105, v2.
'	Modified to be able to pass a databasename as an argument to the script
'	to only mirror that database.
'	Change compressing backups to only use zip (not 7z).
 
'TODO:
'Add check for psexec.exe accept-license-on-first-run.
'Add check for SQL and share connectivity between both systems.
 
Const bOverWrite = True
Const intForReading = 1
Const HideWindow = 0
Const WaitOnReturn = True
Const str7zipExe = "D:\Tools\7-zip\7za.exe"
'*****
'DON'T FORGET TO RUN PSEXEC ONCE MANUALLY TO ACCEPT THE LICENSE!
'*****
Const strPsExecExe = "D:\Tools\psexec.exe"
 
strBackupDir = "D:\MirBackup\"
strTargetBackupDir = "D:\Backup\"
strTargetDataDir = "E:\Data\"
strTargetLogDir = "E:\Logs\"
 
strSourceServer = "sourceserver.domain.com"
strSourceBackupDirUNC = "\\" & strSourceServer & "\D$\Backup"
strTargetServer = "targetserver.domain.com"
strTargetBackupDirUNC = "\\" & strTargetServer & "\D$\Backup"
 
strEndpointName = "Endpoint_Mirroring"
strMirrorPort = "5022"
 
Set objParam = Wscript.Arguments
 
If objParam.Count = 0 Then
 
	strDatabaseName = ""
 
Else
 
	strDatabaseName = objParam(0)
 
End If
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
 
strConnSrc = "Provider=SQLOLEDB; Data Source=" & strSourceServer & "; Initial Catalog=master; Trusted_Connection=yes; Network Library=dbmssocn;"
strConnTarget = "Provider=SQLOLEDB; Data Source=" & strTargetServer & "; Initial Catalog=master; Trusted_Connection=yes; Network Library=dbmssocn;"
 
Set objRS = CreateObject("ADODB.RecordSet")
Set objRS2 = CreateObject("ADODB.RecordSet")
Set objMirRS = CreateObject("ADODB.RecordSet")
 
Set objConn = WScript.CreateObject("ADODB.Connection")
 
objConn.CommandTimeout=3600
 
objConn.Open strConnSrc
 
'If no databasename was passed as an argument to this script, select all databases except master, model, msdb and tempdb.
If strDatabaseName = "" Then
 
	strSQL = "SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')"
 
'If a databasename was passed as a scriptargument, select info about this database only.
Else
 
	strSQL = "SELECT name FROM sys.databases WHERE name = '" & strDatabaseName & "'"
 
End If
 
objRS.Open strSQL, strConnSrc
 
Do Until objRS.EOF
 
	'SELECT name,physical_name FROM dbname.sys.database_files
	strSQL = "SELECT name,physical_name FROM [" & objRS("name").Value & "].sys.database_files"
 
	objRS2.Open strSQL, strConnSrc
 
	Set tsDBFileGrpLog = objFso.CreateTextFile(strBackupDir & objRS("name").Value & ".txt", bOverwrite)
 
	Do Until objRS2.EOF
 
		tsDBFileGrpLog.WriteLine(objRS2("name").Value & chr(9) & objRS2("physical_name").Value)
 
		objRS2.MoveNext
 
	Loop
 
	objRS2.Close
 
	tsDBFileGrpLog.Close
 
	strSQL = "SELECT recovery_model_desc FROM sys.databases WHERE name = '" & objRS("name").Value & "'"
 
	objRS2.Open strSQL, strConnSrc
 
	bIsNotFull = False
 
	Do Until objRS2.EOF
 
		If Not LCase(objRS2("recovery_model_desc").Value) = "full" Then
 
			bIsNotFull = True
 
		End If
 
		objRS2.MoveNext
 
	Loop
 
	objRS2.Close
 
	If bIsNotFull = False Then
 
		'BACKUP DATABASE dbname TO DISK = N'X:\Dir\dbname.bak' WITH INIT, NAME = N'dbname-Full Database Backup'
		strSQL = "BACKUP DATABASE [" & objRS("name").Value & "] TO DISK = N'" & strBackupDir & objRS("name").Value & ".bak' WITH INIT, NAME = N'" & objRS("name").Value & "-Full Database Backup'"
 
		WScript.Echo Now() & " - Backup database " & objRS("name").Value
 
		objConn.Execute(strSQL)
 
		'BACKUP LOG dbname TO DISK = N'X:\Dir\dbname.bak' WITH NOINIT, NAME = N'dbname-Transaction Log Backup'
		strSQL = "BACKUP LOG [" & objRS("name").Value & "] TO DISK = N'" & strBackupDir & objRS("name").Value & ".bak' WITH NOINIT, NAME = N'" & objRS("name").Value & "-Transaction Log Backup'"
 
		WScript.Echo Now() & " - Backup log of " & objRS("name").Value
 
		objConn.Execute(strSQL)
 
		'ALTER DATABASE dbname SET OFFLINE
		strSQL = "ALTER DATABASE [" & objRS("name").Value & "] SET OFFLINE"
 
		WScript.Echo Now() & " - Set database " & objRS("name").Value & " offline."
 
		objConn.Execute(strSQL)
 
	Else
 
		WScript.Echo Now() & " - Skipping database " & objRS("name").Value & " because it is not in Full Recovery mode."
 
	End If
 
	objRS.MoveNext
 
Loop
 
objRS.Close
 
objConn.Close
 
Set ToZipFolder = objFso.GetFolder(strBackupDir)
 
For Each file In ToZipFolder.Files
 
	'Continue if strDatabaseName is not defined, or if it is defined and the current .bak-file matches strDatabaseName.
	If (strDatabaseName = "" And objFso.GetExtensionName(file) = "bak") Or (strDatabaseName <> "" And strDatabaseName & ".bak" = file.Name) Then
 
		WScript.Echo Now() & " - Compressing: " & file.Name
 
		'Compress to .zip with fastest compression.
		objShell.Run chr(34) & str7zipEXE & chr(34) & " a -tzip " & chr(34) & strBackupDir & Left(file.Name, Len(file.Name) - 4) & ".zip" & chr(34) & " " & chr(34) & strBackupDir & file.Name & chr(34) & " -mx=1", HideWindow, WaitOnReturn
 
	End If
 
Next
 
Set ToZipFoler = Nothing
 
'Create the mirroring Encpoint on the sourceserver.
CreateEndpoint strSourceServer, strEndpointName, strConnSrc
 
'Create the mirroring Endpoint on the targetserver.
CreateEndpoint strTargetServer, strEndpointName, strConnTarget
 
Set BackupFolder = objFso.GetFolder(strBackupDir)
 
For Each file In BackupFolder.Files
 
	'Continue if strDatabaseName is not defined, or if it is defined and the current .zip-file matches strDatabaseName.
	If (strDatabaseName = "" And objFso.GetExtensionName(file) = "zip") Or (strDatabaseName <> "" And strDatabaseName & ".zip" = file.Name) Then
 
		WScript.Echo Now() & " - Copying: " & file.Name
 
		objFso.CopyFile strBackupDir & file.Name, strTargetBackupDirUNC & "\", bOverWrite
 
		'If the file is equal in size on both sides, unpack it on the targetserver.
		If objFso.FileExists(strTargetBackupDirUNC & "\" & file.Name) And objFso.FileExists(strBackupDir & "\" & file.Name) Then
 
			Set objLocalFile = objFso.GetFile(strBackupDir & "\" & file.Name)
			Set objTargetFile = objFso.GetFile(strTargetBackupDirUNC & "\" & file.Name)
 
			If objLocalFile.Size = objTargetFile.Size Then
 
				WScript.Echo Now() & " - Unpacking: " & file.Name & " on " & strTargetServer
 
				objShell.Run chr(34) & strPsExecExe & chr(34) & " \\" & strTargetServer & " " & chr(34) & str7ZipExe & chr(34) & " e " & chr(34) & strTargetBackupDir & file.Name & chr(34) & " -o" & chr(34) & strTargetBackupDir & chr(34) & " -y", HideWindow, WaitOnReturn
 
			Else
 
				WScript.Echo Now() & " - " & "File mismatch for " & file.Name & "! Please check the file."
 
			End If
 
			Set objTargetFile = Nothing
			Set objLocalFile = Nothing
 
		End If
 
	End If	
 
Next
 
For Each file in BackupFolder.Files
 
	'Continue if strDatabaseName is not defined, or if it is defined and the current .txt-file matches strDatabaseName.
	If strDatabaseName = "" Or (strDatabaseName <> "" And strDatabaseName & ".txt" = file.Name) Then
 
		'Continue if there is a textfile in the backupdir and an extracted .bak-file in the backupdir of the targetserver.
		If objFso.GetExtensionName(file) = "txt" AND objFso.FileExists(strBackupDir & file.Name) And objFso.FileExists(strTargetBackupDirUNC & "\" & Left(file.Name, Len(file.Name) - 4) & ".bak") Then
 
			Set tsReadDBFileGrpLog = objFso.OpenTextFile(strBackupDir & "\" & Left(file.Name, Len(file.Name) - 4) & ".txt", intForReading)
 
			arrName = Array("empty")
			arrPhysName = Array("empty")
 
			c = 0
			d = 0
 
			Do Until tsReadDBFileGrpLog.AtEndOfStream
 
				strLine = tsReadDBFileGrpLog.ReadLine
 
				arrLine = Split(strLine, chr(9))
 
				ReDim Preserve arrName(c)
				arrName(c) = arrLine(0)
				c = c + 1
 
				ReDim Preserve arrPhysName(d)
				arrPhysName(d) = Right(arrLine(1), Len(arrLine(1)) - InstrRev(arrLine(1), "\"))
				d = d + 1
 
			Loop
 
			strMove = ""
 
			For i = 0 To UBound(arrName)
 
				If InStr(LCase(arrName(i)), "log") > 0 Or InStr(LCase(arrPhysName(i)), ".ldf") Then
 
					strMove = strMove & ", Move N'" & arrName(i) & "' TO N'" & strTargetLogDir & arrPhysName(i) & "'"
 
				Else
 
					strMove = strMove & ", MOVE N'" & arrName(i) & "' TO N'" & strTargetDataDir & arrPhysName(i) & "'"
 
				End If
 
			Next
 
			strDBName = Left(file.Name, Len(file.Name) - 4)
 
			objConn.Open strConnTarget
 
			strSQL = "RESTORE DATABASE [" & strDBName & "] FROM DISK = N'" & strTargetBackupDir & strDBName & ".bak' WITH  FILE = 1" & strMove & ", NORECOVERY"
 
			WScript.Echo Now() & " - Restore database " & strDBName
 
			objConn.Execute(strSQL)
 
			strSQL = "RESTORE LOG [" & strDBName & "] FROM DISK = N'" & strTargetBackupDir & strDBName & ".bak' WITH FILE = 2, NORECOVERY"
 
			WScript.Echo Now() & " - Restore log for " & strDBName
 
			objConn.Execute(strSQL)
 
			strSQL = "ALTER DATABASE [" & strDBName & "] SET PARTNER = 'TCP://" & strSourceServer & ":" & strMirrorPort & "'"
 
			WScript.Echo Now() & " - Set partner on mirror for " & strDBName
 
			objConn.Execute(strSQL)
 
			objConn.Close
 
			objConn.Open strConnSrc
 
			strSQL = "ALTER DATABASE [" & strDBName & "] SET ONLINE"
 
			WScript.Echo Now() & " - Set database " & strDBName & " online on principal"
 
			objConn.Execute(strSQL)
 
			strSQL = "ALTER DATABASE [" & strDBName & "] SET PARTNER = 'TCP://" & strTargetServer & ":" & strMirrorPort & "'"
 
			WScript.Echo Now() & " - Set partner on primary for " & strDBName
 
			objConn.Execute(strSQL)
 
			objConn.Close
 
		End If
 
	End If
 
Next
 
Function CreateEndpoint(strServer,strEndpointName, strConn)
 
	strSQL = "SELECT name FROM sys.database_mirroring_endpoints"
 
	objMirRS.Open strSQL, strConn
 
	bEndpointExists = False
 
	Do Until objMirRS.EOF
 
		If objMirRS("name").Value = strEndpointName Then
 
			WScript.Echo Now() & " - Found endpoint name: " & objMirRS("name").Value & " on " & strServer
 
			bEndpointExists = True
 
			Exit Do
 
		End If
 
	Loop
 
	If bEndpointExists = False Then
 
		objConn.Open strConn
 
		strSQL = "CREATE ENDPOINT " & strEndpointName & " STATE=STARTED AS TCP (LISTENER_PORT=" & strMirrorPort & ") FOR DATABASE_MIRRORING (ROLE=ALL)"
 
		WScript.Echo Now() & " - Create endpoint " & strEndpointName & " on " & strServer
 
		objConn.Execute(strSQL)
 
		objConn.Close
 
	End If
 
	objMirRS.Close
 
End Function
 
'Cleanup
Set objConn = Nothing
Set objMirRS = Nothing
Set objRS2 = Nothing
Set objRS = Nothing
Set objShell = Nothing
Set objFso = Nothing