Comparing backup to live database

<--- Back to SQL Comparison SDK

1. using System;
2. using System.Collections.Generic;
3. using System.Text;
4. using RedGate.Shared.SQL;
5. using RedGate.SQLCompare.Engine;
6. using RedGate.SQLCompare.Engine.ReadFromBackup;
7. using RedGate.BackupReader;
8. using RedGate.SQLDataCompare.Engine;
9. using RedGate.SQLDataCompare.Engine.ResultsStore;
10. using RedGate.Shared.Utils;
11.  
12. namespace SDCCompareToBackup
13. {
14.         class Program
15.         {
16.                 static void Main(string[] args)
17.                 {
18.                         try
19.                         {
20.                                 // First register the backup file
21.                                 Console.WriteLine("Registering backup");
22.                                 BackupDatabaseSource backupDS = new BackupDatabaseSource();
23.                                 backupDS.Files.Add(@"C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\WidgetDev.bak");
24.                                 BackupSetDatabase backupDB = new BackupSetDatabase();
25.                                 backupDB.Status += new StatusEventHandler(StatusCallBack);
26.                                 backupDB.RegisterForDataCompare(backupDS.ToConnectionProperties(),Options.Default);
27.  
28.                                 // Secondly register the live database
29.                                 Console.WriteLine("Registering live database");
30.                                 Database liveDB = new Database();
31.                                 liveDB.Status += new StatusEventHandler(StatusCallBack);
32.                                 liveDB.RegisterForDataCompare(new ConnectionProperties("PS-BRIAND\\SQL2008R2", "WidgetLive"));
33.  
34.                                 // Create the mappings between the two schemas
35.                                 SchemaMappings mappings = new SchemaMappings();
36.                                 mappings.CreateMappings(backupDB, liveDB);
37.  
38.                                 // Compare the database
39.                                 ComparisonSession session = new ComparisonSession();
40.                                 session.Status += new StatusEventHandler(StatusCallBack);
41.                                 session.CompareDatabases(backupDB, liveDB, mappings,SessionSettings.Default);
42.                                 // Show the results in visual form
43.                                 foreach (TableMapping mapping in mappings.TableMappings)
44.                                 {
45.                                         TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
46.                                         //display the different records
47.  
48.                                         // Any tables that couldn't be compared we don't output the results
49.                                         if (difference == null)
50.                                                 continue;
51.  
52.                                         //loop through all the rows
53.                                         foreach (Row row in difference.ResultsStore)
54.                                         {
55.                                                 //go through the non same records
56.                                                 if (row.Type != Row.RowType.Same)
57.                                                 {
58.                                                         Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row.Index, row.Type.ToString());
59.                                                         int i = 0;
60.                                                         foreach (FieldPair field in difference.ResultsStore.Fields)
61.                                                         {
62.                                                                 int field1 = field.OrdinalInResults1;
63.                                                                 int field2 = field.OrdinalInResults2;
64.  
65.                                                                 if (field1 != field2)
66.                                                                 {
67.                                                                         //get the values
68.                                                                         object value1 = row.Values[field1];
69.                                                                         object value2 = row.Values[field2];
70.                                                                         if (value1 == null)
71.                                                                                 value1 = "NULL";
72.                                                                         if (value2 == null)
73.                                                                                 value2 = "NULL";
74.                                                                         Console.WriteLine("{0}\t{1}\t{2}\t{3}", field.Field(false).Name, value1.ToString(), row.FieldDifferent(i) ? "<>" : "==", value2.ToString());
75.                                                                 }
76.                                                                 else
77.                                                                 {
78.                                                                         //this is part of the unique index we are comparing on
79.                                                                         object value = row.Values[field1];
80.                                                                         Console.WriteLine("*{0}\t{1}", field.Field(false).Name, value.ToString());
81.                                                                 }
82.                                                                 i++;
83.                                                         }
84.                                                 }
85.                                         }
86.                                 }
87.                         }
88.                         catch (Exception e)
89.                         {
90.                                 Console.WriteLine("ERROR\n{0}", e.Message);
91.                         }
92.  
93.                 }
94.  
95.                 private static void StatusCallBack(object sender, StatusEventArgs e)
96.                 {
97.                         //fired by the SqlProvider to indicate events
98.  
99.                         if (e.Message != null)
100.                         {
101.                                 Console.WriteLine(e.Message);
102.                         }
103.  
104.                         if (e.Percentage != -1)
105.                         {
106.                                 Console.WriteLine("{0}%", e.Percentage);
107.                         }
108.                 }
109.         }
110. }
111.