sample VB.NET code for sql data compare with backups

RBohannon

Hello,

Do you have any sample code for calling SDC 6 from VB when comparing two backup files? The output will be a SQL change script. I'm using VB.NET 2.0 and SQL Server 2005.

Thank you.

richardjm

You're in luck, we're just about to release 6.1 of SQL Data Compare and we've updated the code samples to give you code to do this sort of thing...


You will need a few extra references in your project...

.NET dependencies

RedGate.BackupReader.BackupReader.dll (needed to compare against any database backup file)
RedGate.BackupReader.SqbReader.dll (if you intend to compare against SQL Backup files)
RedGate.SQLCompare.ASTParser.dll (a new component of the SQL Compare engine - necessary)
RedGate.SQLCompare.Rewriter.dll (another new component of the SQL Compare engine - necessary)
RedGate.SQLCompare.BackupReader.dll (needed to compare against any database backup file)
System.Data.SQLite.dll (needed for comparison to any database backup file)

Non .NET dependencies

RedGate.BackupReader.CryptoHelper.dll (only needed to compare against Encrypted SQL Backup files)
rglz.dll (needed for SQL Backup Compression level 1 (or compressed temporary files - 6.1 only))
zlib1.dll (needed for SQL Backup Compression levels 2 & 3) 

And here's the example code I've written, the only real difference between comparing to a live database and a backup database is the method of registration - apart from that they can be used the same ( except of course synchronizing to a backup ).

Option Explicit On

Imports RedGate.SQLCompare.BackupReader
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore
Imports RedGate.SQL.Shared

Public Class BackupComparisonExample
    Sub RunExample()
        Dim session As New ComparisonSession

        'register the databases for comparison
        Dim backupDB As New BackupDatabase
        Dim liveDB As New Database

        Try
            ' First register the backup file
            Console.WriteLine("Registering backup")
            backupDB.Status = New StatusEventHandler(AddressOf StatusCallback)
            backupDB.RegisterForDataCompare(New String() {"c:\\widgetdev.bak"}, Nothing)

            ' Secondly register the live database
            Console.WriteLine("Registering live database")
            liveDB.Status = New StatusEventHandler(AddressOf StatusCallback)
            liveDB.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.Default)


            Dim mappings As New SchemaMappings
            mappings.CreateMappings(backupDB, liveDB)

            'compare the databases
            session.CompareDatabases(backupDB, liveDB, mappings)

            Dim mapping As TableMapping

            For Each mapping In mappings.TableMappings
                Dim table As ViewTableSuperClass = mapping.Obj1
                Dim difference As TableDifference = session.TableDifferences(table.FullyQualifiedName)

                ' Any tables that couldn't be compared we don't output the results
                If difference Is Nothing Then
                    Continue For
                End If

                Dim row As Row
                For Each row In difference.ResultsStore  'loop through all the rows

                    If (row.Type <> row.RowType.Same) Then 'go through the non same records
                        Dim field As FieldPair
                        Dim i As Int32 = 0
                        Console.WriteLine("{0} Row {1} type {2}", table.FullyQualifiedName, row.Index, row.Type.ToString())
                        For Each field In difference.ResultsStore.Fields
                            'work out where about in the results the field data is stored
                            'if we were comparing identical records, or records present in one
                            'database but not the other then we would not need to 
                            'use the OrdinalInResults1 and OrdinalInResults2 properties
                            'but just OrdinalInResults
                            Dim field1 As Int32 = field.OrdinalInResults1
                            Dim field2 As Int32 = field.OrdinalInResults2

                            If (field1 <> field2) Then
                                'get the values
                                Dim value1 As Object = row.Values(field1)
                                Dim value2 As Object = row.Values(field2)
                                If (value1 Is Nothing) Then
                                    value1 = "NULL"
                                End If
                                If (value2 Is Nothing) Then
                                    value2 = "NULL"
                                End If
                                If row.FieldDifferent(i) Then
                                    Console.WriteLine("{0}:{1} <> {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
                                Else
                                    Console.WriteLine("{0}:{1} == {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
                                End If
                            Else
                                'this is part of the unique index we are comparing on
                                Dim value As Object = row.Values(field1)
                                Console.WriteLine("*{0}:{1}", field.Field(False).Name, value.ToString())
                            End If
                            i += 1
                        Next
                    End If
                Next
            Next
        Finally
            'dispose of the objects
            session.Dispose()
            backupDB.Dispose()
            liveDB.Dispose()
        End Try
    End Sub

    Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)

        If Not (e.Message Is Nothing) Then
            Console.WriteLine(e.Message)
        End If

        If e.Percentage <> -1 Then
            Console.WriteLine("{0}%", e.Percentage)
        End If
    End Sub
End Class

RBohannon

Thanks. Your code snippet will work with 6.0, right? I'd like to put this code into production next week.

When is 6.1 coming out?

richardjm

Should do the API is compatible. We're aiming for 6.1 to be out in the next few weeks just fixing a few issues on it and doing final testing.

RBohannon

Great. I'll integrate your code into my program today and let you know how it goes. Thanks for the help.

If I have issues in the future, does Redgate have a public code library where I can find sample code? I couldn't find one.

richardjm

We have mainly this forum you're looking at and the API documentation. There's also a zip file containing toolkit samples which you can download via the support area of the website.

Also I forgot another dll (Rob has just berated me for being forgetful)

zlib1.dll (not .NET, needed for SQL Backup Compression levels 2 & 3)

I've updated the dlls in the post above (SQLite.dll *is* .NET) - D'Oh)

- Got that Friday feeling.

elizas

Using the SQL SMO object we can easily take backup of SQL database through code.


http://www.mindfiresolutions.com/Backup-SQL-Database-Through-Code-In-VBNET-846.php