sample VB.NET code for sql data compare with backups

RBohannonRBohannon Posts: 25
edited March 17, 2010 6:25AM in SQL Toolkit Previous Versions

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.


  • richardjmrichardjm Posts: 598
    edited January 11, 2008 10:45AM
    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
                ' 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())
                                        Console.WriteLine("{0}:{1} == {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
                                    End If
                                    '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
                        End If
                'dispose of the objects
            End Try
        End Sub
        Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
            If Not (e.Message Is Nothing) Then
            End If
            If e.Percentage <> -1 Then
                Console.WriteLine("{0}%", e.Percentage)
            End If
        End Sub
    End Class
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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?
  • 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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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.
  • 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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • Using the SQL SMO object we can easily take backup of SQL database through code.
Sign In or Register to comment.