SQL Compare & Data Compare used to restore schema and data

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK


This example shows how SQL Compare and Data Compare can be used together to effectively copy one table to another database. In this code, the “WidgetPrices” table is modified in WidgetLive to match WidgetDev, then the data is also adjusted. Although this example modifies an existing table, there is nothing to stop you from using it to “copy” a table from one database to another.

One note about the references – when combining SQL Compare and Data Compare into one project, your reference path should point to the folder containing the SQL Data Comapre assemblies. Mixing and matching SQL Compare and Data Compare assemblies is not possible because the versions can be slightly different.


C#

  1. using System;
  2. using RedGate.SQLDataCompare.Engine;
  3. using RedGate.SQLCompare.Engine;
  4. using RedGate.Shared.Utils;
  5. using RedGate.Shared.SQL;
  6. using RedGate.Shared.SQL.ExecutionBlock;
  7.  
  8. namespace RedGate.Snippets
  9. {
  10.         class ObjectMigrationExample
  11.         {
  12.                 public static void Main()
  13.                 {
  14.                         using (Database widgetDev = new Database(),
  15.                                                                             widgetLive = new Database())
  16.                         {
  17.  
  18.                                 // Retrieve the schema information for the two databases
  19.                                 widgetDev.Register(new ConnectionProperties(".", "WidgetDev"), Options.Default);
  20.                                 widgetLive.Register(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  21.                                 // Compare widgetStaging to widgetProduction. Comparing in this order makes WidgetProduction the second database
  22.  
  23.                                 Differences stagingVsProduction = widgetDev.CompareWith(widgetLive, Options.Default);
  24.  
  25.                                 // Select the differences to include in the synchronization. In this case, we're selecting ONLY the WidgetPrices table.
  26.                                 foreach (Difference difference in stagingVsProduction)
  27.                                 {
  28.                                         if (difference.Name == "[dbo].[WidgetPrices]")
  29.                                                 difference.Selected = true;
  30.                                         else difference.Selected = false;
  31.                                 }
  32.  
  33.                                 Work work = new Work();
  34.  
  35.                                 // Calculate the work to do using sensible default options
  36.                                 // The script is to be run on WidgetProduction so the runOnTwo parameter is true
  37.                                 work.BuildFromDifferences(stagingVsProduction, Options.Default, true);
  38.  
  39.                                 // Disposing the execution block when it's not needed any more is important to ensure
  40.                                 // that all the temporary files are cleaned up
  41.  
  42.                                 using (ExecutionBlock block = work.ExecutionBlock)
  43.                                 {
  44.  
  45.                                         // Display the SQL used to synchronize
  46.                                         Console.WriteLine("\r\nSQL schema migration script:\r\n");
  47.                                         Console.WriteLine(block.GetString());
  48.  
  49.                                         // Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
  50.                                         // Uncomment the next two lines to automatically synchronize
  51.                                         // BlockExecutor executor = new BlockExecutor();
  52.                                         // executor.ExecuteBlock(block, ".", "WidgetLive");
  53.  
  54.                                 }
  55.                         }// end using schema databases
  56.  
  57.                         /* Now that the schema has been synchronized, we need to create
  58.                             * entirely NEW database objects for that purpose. As logical
  59.                             * as it sounds, a Database object registered for SQL Compare
  60.                             * cannot be re-used for Data Compare! The whole registration
  61.                             * process needs to begin again! */
  62.  
  63.                         using (Database widgetDev = new Database(),
  64.                             widgetLive = new Database())
  65.                         {
  66.                                 widgetLive.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  67.                                 widgetDev.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
  68.  
  69.                                 // Similar to the schema, select only the WidgetPrices table
  70.  
  71.                                 TableMappings mappings = new TableMappings();
  72.  
  73.                                 mappings.Options = new EngineDataCompareOptions(
  74.                                 MappingOptions.Default,
  75.                                 ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default, SqlOptions.Default);
  76.  
  77.                                 TableMapping mapping = (TableMapping)mappings.Join(widgetDev.Tables["[dbo].[WidgetPrices]"], widgetLive.Tables["[dbo].[WidgetPrices]"]);
  78.                                 mapping.RefreshMappingStatus();
  79.                                 mappings.Add(mapping);
  80.  
  81.                                 // Instantiate a ComparisonSession to compare the data
  82.                                 using (ComparisonSession session = new ComparisonSession())
  83.                                 {
  84.                                         session.Options = mappings.Options;
  85.                                         session.CompareDatabases(widgetLive, widgetDev, mappings, SessionSettings.Default);
  86.  
  87.                                         //Actually generate the script.
  88.  
  89.                                         SqlProvider provider = new SqlProvider();
  90.                                         provider.Options = session.Options;
  91.  
  92.                                         ExecutionBlock block = provider.GetMigrationSQL(session, true);
  93.                                         Console.WriteLine("\r\nSQL data synchronization script:\r\n");
  94.                                         Console.WriteLine(block.GetString());
  95.  
  96.                                         // Uncomment the next two lines to automatically synchronize
  97.                                         // BlockExecutor executor = new BlockExecutor();
  98.                                         // executor.ExecuteBlock(block, ".", "WidgetLive");
  99.  
  100.                                         // It's a good idea to clean up the temporary script.
  101.                                         block.Dispose();
  102.  
  103.                                 } //using ComparisonSession
  104.  
  105.                         } //end using data databases
  106.                         Console.WriteLine("Press [Enter]");
  107.                         Console.ReadLine();
  108.                 }
  109.         }
  110. }

VB

  1. Imports System
  2. Imports RedGate.SQLDataCompare.Engine
  3. Imports RedGate.SQLCompare.Engine
  4. Imports RedGate.Shared.Utils
  5. Imports RedGate.Shared.SQL
  6. Imports RedGate.Shared.SQL.ExecutionBlock
  7.  
  8. Module Module1
  9.         Sub Main()
  10.                 Using widgetDev As New Database(), widgetLive As New Database()
  11.  
  12.                         ' Retrieve the schema information for the two databases
  13.                         widgetDev.Register(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
  14.                         widgetLive.Register(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  15.                         ' Compare widgetStaging to widgetProduction. Comparing in this order makes WidgetProduction the second database
  16.  
  17.                         Dim stagingVsProduction As Differences = widgetDev.CompareWith(widgetLive, Options.[Default])
  18.  
  19.                         ' Select the differences to include in the synchronization. In this case, we're selecting ONLY the WidgetPrices table.
  20.                         For Each difference As Difference In stagingVsProduction
  21.                                 If difference.Name = "[dbo].[WidgetPrices]" Then
  22.                                         difference.Selected = True
  23.                                 Else
  24.                                         difference.Selected = False
  25.                                 End If
  26.                         Next
  27.  
  28.                         Dim work As New Work()
  29.  
  30.                         ' Calculate the work to do using sensible default options
  31.                         ' The script is to be run on WidgetProduction so the runOnTwo parameter is true
  32.                         work.BuildFromDifferences(stagingVsProduction, Options.[Default], True)
  33.  
  34.                         ' Disposing the execution block when it's not needed any more is important to ensure
  35.                         ' that all the temporary files are cleaned up
  36.  
  37.                         Using block As ExecutionBlock = work.ExecutionBlock
  38.  
  39.                                 ' Display the SQL used to synchronize
  40.                                 Console.WriteLine(vbCr & vbLf & "SQL schema migration script:" & vbCr & vbLf)
  41.  
  42.                                 ' Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
  43.                                 ' Uncomment the next two lines to automatically synchronize
  44.                                 ' BlockExecutor executor = new BlockExecutor();
  45.                                 ' executor.ExecuteBlock(block, ".", "WidgetLive");
  46.  
  47.                                 Console.WriteLine(block.GetString())
  48.                         End Using
  49.                 End Using
  50.                 ' end using schema databases
  51.                 ' Now that the schema has been synchronized, we need to create
  52.                 ' * entirely NEW database objects for that purpose. As logical
  53.                 ' * as it sounds, a Database object registered for SQL Compare
  54.                 ' * cannot be re-used for Data Compare! The whole registration
  55.                 ' * process needs to begin again!
  56.  
  57.  
  58.                 Using widgetDev As New Database(), widgetLive As New Database()
  59.                         widgetLive.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  60.                         widgetDev.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
  61.  
  62.                         ' Similar to the schema, select only the WidgetPrices table
  63.  
  64.                         Dim mappings As New TableMappings()
  65.  
  66.                         mappings.Options = New EngineDataCompareOptions(MappingOptions.[Default], ComparisonOptions.TrimTrailingSpaces Or ComparisonOptions.[Default], SqlOptions.[Default])
  67.  
  68.                         Dim mapping As TableMapping = DirectCast(mappings.Join(widgetDev.Tables("[dbo].[WidgetPrices]"), widgetLive.Tables("[dbo].[WidgetPrices]")), TableMapping)
  69.                         mapping.RefreshMappingStatus()
  70.                         mappings.Add(mapping)
  71.  
  72.                         ' Instantiate a ComparisonSession to compare the data
  73.                         Using session As New ComparisonSession()
  74.                                 session.Options = mappings.Options
  75.                                 session.CompareDatabases(widgetLive, widgetDev, mappings, SessionSettings.[Default])
  76.  
  77.                                 'Actually generate the script.
  78.  
  79.                                 Dim provider As New SqlProvider()
  80.                                 provider.Options = session.Options
  81.  
  82.                                 Dim block As ExecutionBlock = provider.GetMigrationSQL(session, True)
  83.                                 Console.WriteLine(vbCr & vbLf & "SQL data synchronization script:" & vbCr & vbLf)
  84.                                 Console.WriteLine(block.GetString())
  85.  
  86.                                 ' Uncomment the next two lines to automatically synchronize
  87.                                 ' BlockExecutor executor = new BlockExecutor();
  88.                                 ' executor.ExecuteBlock(block, ".", "WidgetLive");
  89.  
  90.                                 ' It's a good idea to clean up the temporary script.
  91.  
  92.                                 block.Dispose()
  93.                                 'using ComparisonSession
  94.                         End Using
  95.                 End Using
  96.                 'end using data databases
  97.                 Console.WriteLine("Press [Enter]")
  98.                 Console.ReadLine()
  99.         End Sub
  100. End Module
Personal tools