Comparing backup to live database

From RedGateWiki
Jump to: navigation, search

<--- 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.  
Personal tools