Comparing a live database to a backup

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