Comparing a live database to a backup

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

This example shows how a backup can be used as a data source in place of a live database. The backup file may be a native SQL Server (MTF) file, or a compressed and/or encrypted SQL Backup file. Note that the files and passwords are generic lists of type string. The reason for this is because BackupDatabaseSource can accept both full and differential backups, allowing you to retrieve the data in a point-in-time manner.

The data in the specified backup set is then compared to a live database and the differences are reported to the console.


C#

  1. using System;
  2. using System.Collections.Generic;
  3. using RedGate.BackupReader;
  4. using RedGate.Shared.Utils;
  5. using RedGate.SQLCompare.Engine;
  6. using RedGate.SQLCompare.Engine.ReadFromBackup;
  7. using RedGate.SQLDataCompare.Engine;
  8. using RedGate.SQLDataCompare.Engine.ResultsStore;
  9. using RedGate.Shared.SQL.BackupReaderInterfaces;
  10.  
  11. namespace SQLDataCompareCodeSnippets
  12. {
  13.         public class BackupComparisonExample
  14.         {
  15.                 public void RunExample()
  16.                 {
  17.                         BackupSetDatabase backupDB = new BackupSetDatabase();
  18.                         Database liveDB = new Database();
  19.                         ComparisonSession session = new ComparisonSession();
  20.                         SchemaMappings mappings = new SchemaMappings();
  21.  
  22.                         try
  23.                         {
  24.                                 // First register the backup file
  25.                                 Console.WriteLine("Registering backup");
  26.                                 backupDB.Status += new StatusEventHandler(StatusCallBack);
  27.  
  28.                                 IList<string> files = new string[] { "c:\\widgetdev.bak" };
  29.                                 IList<string> passwords = new List<string>();
  30.                                 IList<IBackupSet> backupSets = backupDB.GetBackupSets(files, passwords);
  31.  
  32.                                 // use the latest backup set available
  33.                                 IBackupSet backupSetToUse = backupSets[backupSets.Count - 1];
  34.  
  35.                                 BackupDatabaseSource backupDBSource = new BackupDatabaseSource(files, passwords, backupSetToUse);
  36.                                 backupDB.RegisterForDataCompare(backupDBSource.ToConnectionProperties());
  37.  
  38.                                 // Secondly register the live database
  39.                                 Console.WriteLine("Registering live database");
  40.                                 liveDB.Status += new StatusEventHandler(StatusCallBack);
  41.                                 liveDB.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  42.  
  43.                                 // Create the mappings between the two databases
  44.                                 mappings.CreateMappings(backupDB, liveDB);
  45.                                 session.CompareDatabases(backupDB, liveDB, mappings);
  46.  
  47.                                 foreach (TableMapping mapping in mappings.TableMappings)
  48.                                 {
  49.                                         TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
  50.  
  51.                                         // Any tables that couldn't be compared we don't output the results
  52.                                         if (difference == null)
  53.                                                 continue;
  54.  
  55.                                         //loop through all the rows
  56.                                         foreach (Row row in difference.ResultsStore)
  57.                                         {
  58.                                                 //go through the non same records
  59.                                                 if (row.Type != Row.RowType.Same)
  60.                                                 {
  61.                                                         Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row.Index, row.Type.ToString());
  62.                                                       int i = 0;
  63.                                                         foreach (FieldPair field in difference.ResultsStore.Fields)
  64.                                                         {
  65.                                                                 int field1 = field.OrdinalInResults1;
  66.                                                                 int field2 = field.OrdinalInResults2;
  67.  
  68.                                                                 if (field1 != field2)
  69.                                                                 {
  70.                                                                         // Get the values
  71.                                                                         object value1 = row.Values[field1];
  72.                                                                         object value2 = row.Values[field2];
  73.                                                                         if (value1 == null)
  74.                                                                                 value1 = "NULL";
  75.                                                                         if (value2 == null)
  76.                                                                                 value2 = "NULL";
  77.                                                                         Console.WriteLine("{0}\t{1}\t{2}\t{3}", field.Field(false).Name, value1.ToString(), row.FieldDifferent(i) ? "<>" : "==", value2.ToString());
  78.                                                                 }
  79.                                                                 else
  80.                                                                 {
  81.                                                                         // This is part of the unique index we are comparing on
  82.                                                                         object value = row.Values[field1];
  83.                                                                         Console.WriteLine("*{0}\t{1}", field.Field(false).Name, value.ToString());
  84.                                                                 }
  85.                                                                 i++;
  86.                                                         }// End of foreach field pair
  87.                                                 }
  88.                                         }// End of foreach row
  89.                                 }// End of foreach mappings
  90.                         }
  91.                         finally
  92.                         {
  93.                                 session.Dispose();
  94.                                 backupDB.Dispose();
  95.                                 liveDB.Dispose();
  96.                         }
  97.                 }
  98.  
  99.                 private static void StatusCallBack(object sender, StatusEventArgs e)
  100.                 {
  101.                         if (e.Message != null)
  102.                         {
  103.                                 Console.WriteLine(e.Message);
  104.                         }
  105.  
  106.                         if (e.Percentage != -1)
  107.                         {
  108.                                 Console.WriteLine("{0}%", e.Percentage);
  109.                         }
  110.                 }
  111.         }
  112. }
  113.  

VB

  1. Imports System
  2. Imports System.Collections.Generic
  3. Imports RedGate.BackupReader
  4. Imports RedGate.Shared.Utils
  5. Imports RedGate.SQLCompare.Engine
  6. Imports RedGate.SQLCompare.Engine.ReadFromBackup
  7. Imports RedGate.SQLDataCompare.Engine
  8. Imports RedGate.SQLDataCompare.Engine.ResultsStore
  9. Imports RedGate.Shared.SQL.BackupReaderInterfaces
  10.  
  11. Module Module1
  12.         Public Sub Main()
  13.                 Dim backupDB As New BackupSetDatabase()
  14.                 Dim liveDB As New Database()
  15.                 Dim session As New ComparisonSession()
  16.                 Dim mappings As New SchemaMappings()
  17.  
  18.                 Try
  19.                         ' First register the backup file
  20.                         Console.WriteLine("Registering backup")
  21.                         backupDB.Status = New StatusEventHandler(AddressOf StatusCallBack)
  22.  
  23.                         Dim files As IList(Of String) = New String() {"c:\widgetdev.bak"}
  24.                         Dim passwords As IList(Of String) = New List(Of String)()
  25.                         Dim backupSets As IList(Of IBackupSet) = backupDB.GetBackupSets(files, passwords)
  26.  
  27.                         ' use the latest backup set available
  28.                         Dim backupSetToUse As IBackupSet = backupSets(backupSets.Count - 1)
  29.  
  30.                         Dim backupDBSource As New BackupDatabaseSource(files, passwords, backupSetToUse)
  31.                         backupDB.RegisterForDataCompare(backupDBSource.ToConnectionProperties())
  32.  
  33.                         ' Secondly register the live database
  34.                         Console.WriteLine("Registering live database")
  35.                         liveDB.Status = New StatusEventHandler(AddressOf StatusCallBack)
  36.                         liveDB.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  37.  
  38.                         ' Create the mappings between the two databases
  39.                         mappings.CreateMappings(backupDB, liveDB)
  40.                         session.CompareDatabases(backupDB, liveDB, mappings)
  41.  
  42.                         For Each mapping As TableMapping In mappings.TableMappings
  43.                                 Dim difference As TableDifference = session.TableDifferences(mapping.Obj1.FullyQualifiedName)
  44.  
  45.                                 ' Any tables that couldn't be compared we don't output the results
  46.                                 If difference Is Nothing Then
  47.                                         Continue For
  48.                                 End If
  49.  
  50.                                 'loop through all the rows
  51.                                 For Each row__1 As Row In difference.ResultsStore
  52.                                         'go through the non same records
  53.                                         If row__1.Type <> Row.RowType.Same Then
  54.                                                 Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row__1.Index, row__1.Type.ToString())
  55.                                                 Dim i As Integer = 0
  56.                                                 For Each field As FieldPair In difference.ResultsStore.Fields
  57.                                                         Dim field1 As Integer = field.OrdinalInResults1
  58.                                                         Dim field2 As Integer = field.OrdinalInResults2
  59.  
  60.                                                         If field1 <> field2 Then
  61.                                                                 ' Get the values
  62.                                                                 Dim value1 As Object = row__1.Values(field1)
  63.                                                                 Dim value2 As Object = row__1.Values(field2)
  64.                                                                 If value1 Is Nothing Then
  65.                                                                         value1 = "NULL"
  66.                                                                 End If
  67.                                                                 If value2 Is Nothing Then
  68.                                                                         value2 = "NULL"
  69.                                                                 End If
  70.                                                                 Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}", field.Field(False).Name, value1.ToString(), If(row__1.FieldDifferent(i), "<>", "=="), value2.ToString())
  71.                                                         Else
  72.                                                                 ' This is part of the unique index we are comparing on
  73.                                                                 Dim value As Object = row__1.Values(field1)
  74.                                                                 Console.WriteLine("*{0}" & vbTab & "{1}", field.Field(False).Name, value.ToString())
  75.                                                         End If
  76.                                                         i += 1
  77.                                                         ' End of foreach field pair
  78.                                                 Next
  79.                                         End If
  80.                                         ' End of foreach row
  81.                                 Next
  82.                                 ' End of foreach mappings
  83.                         Next
  84.                 Finally
  85.                         session.Dispose()
  86.                         backupDB.Dispose()
  87.                         liveDB.Dispose()
  88.                 End Try
  89.         End Sub
  90.  
  91.         Private Sub StatusCallBack(ByVal sender As Object, ByVal e As StatusEventArgs)
  92.                 If e.Message IsNot Nothing Then
  93.                         Console.WriteLine(e.Message)
  94.                 End If
  95.  
  96.                 If e.Percentage <> -1 Then
  97.                         Console.WriteLine("{0}%", e.Percentage)
  98.                 End If
  99.         End Sub
  100. End Module
Personal tools