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

Rate

1 (1)

Share

Share

Rate

1 (1)