Custom Log Shipping using VB.NET
This is a first version of a custom solution for SQL Server log shipping using VB.NET 2005 and .NET Framework v.2.0. The script has to run on a server where you have SQL Server 2005 (or at least tools) installed because it uses new SMO. For some functions of the script you will have to install .NET Framework v.2.0. I tested this program against SQL Server 2000 on both ends running the program from the third SQL Server with SQL Server 2005 installed. The code was written in VS.NET 2005.
I used 2 ways to get the list of databases to be backed up. The first one queries a table with databases names, logical file names, etc. (DBname, LogicalName, FileName, srvSource, SrvTarget, RelocDirectory, NetworkDestination, NetworkSource) so that you I don't have to hard code that and anybody can add/subtract databases for replication. The commented out part of the script provides another way of getting databases names by querying system tables. But then you will have to edit the program for each server you want to connect to.
The program reads the table info, builds arrays, connects to the source server and source databases, backes them up, moves to the destination SQL Server, restores, deletes the backup files, e-mails the results. my next step will be to create a table with e-mail addresses and query it instead of hard coding recepients.
The program goes through database names twice because I need to have both DB name and logical file names (data and log).
When the databases are restored they are left in read-only mode with replacing the data/log files.
If you want to connect to SQL Server using SQL login there is a commented out part of the rpogram that shows you how to do that.
There is plenty of room for improvement I just did not want ot give you all a perfect, finished product. You'll get more from it if you work on it yourself and share with others the results.
Imports System.Data.SqlClient
Imports System.Console
Imports System.IO
Imports System
Imports System.Net.Mail
Imports System.Threading
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Module Module1
Dim srv As Server 'uses SMO
Dim bk As Backup 'uses SMO
Dim bdi As BackupDeviceItem 'uses SMO
Dim bkf As String 'uses SMO, filename
Dim myDate As Date 'uses SMO
Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim DReader As SqlDataReader
Dim strStatement As String
Dim strDatabases() As String 'databases names
Dim strLogicalName() As String 'logical file names for each database
Dim strCommand As String 'Command text
Dim RecordsAffected As Integer
Dim intCounter As Integer
Dim i As Integer
Dim strDestination() As String
Dim strPath As String
Dim strRelocDirectory() As String 'Directory name, i.e. D:\DestinationDirectory Dim strNetworkDestination() As String 'Destination directory, i.e. \\destinationSQLserver\g$\Directory
Dim strNetworkSource() As String 'Source directory, i.e. \\sourceSQLserver\D$\Directory
Dim oSrvSource() As String 'Source SQL Server name for the connection (backup)
Dim oSrvTarget() As String 'Target SQL Server for the connection (restore)
Dim rs As Restore
Dim rsf As String
Dim conn As ServerConnection
Dim oMessage As MailMessage
Dim oSMTPClient As SmtpClient
Dim sFrom As String
Dim sTo As String
Dim sSubject As String
Dim sMessageBody As String
Sub Main()
Try
cnn = New SqlConnection("server=SQLServer; database=Operations; Integrated Security=SSPI; Connection Timeout=0")
cnn.Open()
sFrom = "LogShipping@mhd.com"
sTo = "RecepientAddress@mhd.com"
'Create a list of databases names, save them in a temporary table
'*cmd = New SqlCommand("SELECT dbo.sysdatabases.name AS DBname, dbo.sysaltfiles.name AS LogicalName, " & _
'* "dbo.sysaltfiles.filename INTO ##db_list FROM dbo.sysaltfiles INNER JOIN dbo.sysdatabases ON " & _
'* "dbo.sysaltfiles.dbid = dbo.sysdatabases.dbid WHERE dbo.sysdatabases.name " & _
'* "NOT IN ('Operations', 'Northwind', 'pubs', 'tempdb', 'master', 'model', 'msdb')", cnn)
'*RecordsAffected = cmd.ExecuteNonQuery()
'*WriteLine("Records affected " & RecordsAffected)
'*cmd = New SqlCommand("SELECT * FROM ##db_list", cnn)
cmd = New SqlCommand("SELECT * FROM LS_DBList", cnn)
DReader = cmd.ExecuteReader
sMessageBody = "Date:" & vbTab & Now.ToString & vbCrLf & vbCrLf
'Build an array of databases names and logical filenames
While DReader.Read()
ReDim Preserve strDatabases(intCounter)
ReDim Preserve strLogicalName(intCounter)
ReDim Preserve oSrvSource(intCounter)
ReDim Preserve oSrvTarget(intCounter)
ReDim Preserve strRelocDirectory(intCounter)
ReDim Preserve strNetworkDestination(intCounter)
ReDim Preserve strNetworkSource(intCounter)
strDatabases(intCounter) = Trim(DReader(0).ToString)
strLogicalName(intCounter) = Trim(DReader(1).ToString)
oSrvSource(intCounter) = Trim(DReader(3).ToString)
oSrvTarget(intCounter) = Trim(DReader(4).ToString)
strRelocDirectory(intCounter) = Trim(DReader(5).ToString)
strNetworkDestination(intCounter) = Trim(DReader(6).ToString)
strNetworkSource(intCounter) = Trim(DReader(7).ToString)
intCounter = intCounter + 1
End While
'Reset the counter
intCounter = 0
For i = LBound(strDatabases) To UBound(strDatabases)
conn = New ServerConnection(oSrvSource(intCounter))
conn.ConnectTimeout = 0
conn.LoginSecure = True
conn.StatementTimeout = 0
srv = New Server(conn)
sMessageBody = sMessageBody & "Connected to SQL Server " & oSrvSource(intCounter) & vbCrLf
bk = New Backup
bk.Action = BackupActionType.Database
If intCounter = 0 Then
sMessageBody = sMessageBody & "Backup Directory: " & strRelocDirectory(intCounter) & vbCrLf
sMessageBody = sMessageBody & "Network destination Directory: " & strNetworkDestination(intCounter) & vbCrLf
sMessageBody = sMessageBody & "Network Restore source Directory: " & strNetworkSource(intCounter) & vbCrLf & vbCrLf
Call BackupCode()
Else
If strDatabases(intCounter) <> strDatabases(intCounter - 1) Then
sMessageBody = sMessageBody & "Backup Directory: " & strRelocDirectory(intCounter) & vbCrLf
sMessageBody = sMessageBody & "Network destination Directory: " & strNetworkDestination(intCounter) & vbCrLf
sMessageBody = sMessageBody & "Network Restore source Directory: " & strNetworkSource(intCounter) & vbCrLf & vbCrLf
Call BackupCode()
End If
End If
Thread.Sleep(4000) 'going through the loop twice makes it 8 sec between operations
bk = Nothing
conn = Nothing
srv = Nothing
sMessageBody = sMessageBody & "Disconnected from SQL Server: " & oSrvSource(intCounter) & vbCrLf & vbCrLf
intCounter = intCounter + 1
Next i
Catch ex As Exception
sMessageBody = sMessageBody & "An Error occured: " & ex.Message & vbCrLf & vbCrLf
bk = Nothing
conn = Nothing
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")
End Try
DReader.Close()
cnn.Close()
cnn = Nothing
'#################################################################
'Starting the restore process
'#################################################################
Try
sMessageBody = sMessageBody & "Starting the RESTORE process at: " & Now.ToString & vbCrLf & vbCrLf
intCounter = 0
For i = LBound(strDatabases) To UBound(strDatabases)
conn = New ServerConnection(oSrvTarget(intCounter))
'conn.LoginSecure = False
'conn.Login = "user"
'conn.Password = "password"
conn.ConnectTimeout = 0
conn.LoginSecure = True
conn.StatementTimeout = 0
srv = New Server(conn)
sMessageBody = sMessageBody & "Connected to SQL Server: " & oSrvTarget(intCounter) & vbCrLf
rs = New Restore()
If intCounter = 0 Then
Call RestoreCode()
Else
If strDatabases(intCounter) <> strDatabases(intCounter - 1) Then
Call RestoreCode()
End If
End If
Thread.Sleep(5000)
rs = Nothing
conn = Nothing
srv = Nothing
sMessageBody = sMessageBody & "Disconnected from SQL Server: " & oSrvTarget(intCounter) & vbCrLf & vbCrLf
intCounter = intCounter + 1
Next i
Catch ex As Exception
sMessageBody = sMessageBody & "An Error occured: " & ex.Message & vbCrLf & vbCrLf
'Call SendMessage(sFrom, sTo, sMessageBody)
rs = Nothing
conn = Nothing
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")
End Try
Call SendMessage(sFrom, sTo, sMessageBody)
End Sub
Sub SendMessage(ByVal sFrom As String, ByVal sTo As String, ByVal sMessageBody As String)
oSMTPClient = New SmtpClient("smtp.server.com")
sSubject = "Logshipping information"
oMessage = New MailMessage(sFrom, sTo)
oMessage.Subject = sSubject
oMessage.Body = sMessageBody
oSMTPClient.UseDefaultCredentials = True
oSMTPClient.Send(oMessage)
End Sub
Sub BackupCode()
bkf = strDatabases(intCounter) & "_Backup_" & _
Replace(Replace(Replace(Replace(Now.ToString, ":", ""), " ", ""), "-", ""), "/", "") & ".bak"
bk.BackupSetDescription = "Full backup of " & strDatabases(intCounter)
bk.BackupSetName = UCase(strDatabases(intCounter)) & " Backup"
bk.Database = strDatabases(intCounter)
ReDim Preserve strDestination(intCounter)
strDestination(intCounter) = strNetworkDestination(intCounter)
strPath = strNetworkSource(intCounter)
strPath = strPath & bkf
strDestination(intCounter) = strDestination(intCounter) & bkf
bkf = "D:\ReplicationSource\" & bkf
bdi = New BackupDeviceItem(bkf, DeviceType.File)
bk.Devices.Add(bdi)
bk.Checksum = True
bk.ContinueAfterError = True
bk.Incremental = False
myDate = Today.AddDays(30)
bk.ExpirationDate = myDate
bk.LogTruncation = BackupTruncateLogType.NoTruncate
bk.MediaDescription = "File backup"
bk.Initialize = True
bk.PercentCompleteNotification = 30
bk.Restart = True
Console.WriteLine("Starting backup of database: " & UCase(strDatabases(intCounter)))
Console.WriteLine()
bk.Wait()
sMessageBody = sMessageBody & "Starting backup of database: " & UCase(strDatabases(intCounter)) & _
" at: " & vbTab & Now.ToString & vbCrLf
bk.SqlBackup(srv)
Console.WriteLine("Backup complete")
WriteLine("Database " & UCase(strDatabases(intCounter)) & " was backed up")
WriteLine()
sMessageBody = sMessageBody & "Database " & UCase(strDatabases(intCounter)) & " was backed up" & _
" at: " & vbTab & Now.ToString & vbCrLf
If File.Exists(strPath) Then
File.Move(strPath, strDestination(intCounter))
WriteLine("{0} was successfully moved to {1}.", strPath, strDestination(intCounter))
WriteLine()
sMessageBody = sMessageBody & strPath & " was successfully moved to " & _
strDestination(intCounter) & vbCrLf & vbCrLf
Else
WriteLine("Could not find {0} to move to " & strDestination(intCounter), strPath)
WriteLine()
sMessageBody = sMessageBody & "Could not find " & strPath & _
" to move to " & strDestination(intCounter) & vbCrLf & vbCrLf
End If
End Sub
Sub RestoreCode()
rs.Action = RestoreActionType.Database
rs.StandbyFile = strRelocDirectory(intCounter) & strDatabases(intCounter) & "_undo.dat"
'rs.NoRecovery = True
rs.Database = strDatabases(intCounter)
rs.ReplaceDatabase = True
rs.Devices.AddDevice(strDestination(intCounter), DeviceType.File)
rs.RelocateFiles.Add(New RelocateFile(strLogicalName(intCounter), strRelocDirectory(intCounter) & _
strDatabases(intCounter) & "_data.mdf"))
rs.RelocateFiles.Add(New RelocateFile(strLogicalName(intCounter + 1), strRelocDirectory(intCounter) & _
strDatabases(intCounter) & "_log.ldf"))
rs.Checksum = True
rs.Restart = True
rs.ContinueAfterError = True
rs.PercentCompleteNotification = 10
rs.RestrictedUser = True
rs.Wait()
sMessageBody = sMessageBody & "Starting restore for the database: " & rs.Database & _
" at: " & vbTab & Now.ToString & vbCrLf
rs.SqlRestore(srv)
WriteLine("Completed restore of DB: " & rs.Database)
File.Delete(strDestination(intCounter))
sMessageBody = sMessageBody & "Completed restore of DB: " & rs.Database & _
" at: " & vbTab & Now.ToString & vbCrLf
sMessageBody = sMessageBody & "Deleted: " & strDestination(intCounter) & vbCrLf & vbCrLf
WriteLine("Deleted {0}", strDestination(intCounter))
WriteLine()
End Sub
End Module
