Notes

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====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:
<code>
cscript SQLMirroring.vbs
</code>

To mirror just a single database (can be used multiple times to mirror only some databases):
<code>
cscript SQLMirroring.vbs databasename
</code>

<code vb>
'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

</code>