Schema Object Restore from Backup SDK 8

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

This example demonstrates restoring schema objects from a backup file to a live database. The backup can be either a native SQL Server backup (MTF format) or a Red Gate SQL Backup file that has been compressed and/or encrypted. For these two functions, two support files are needed: zlib1.dll and RedGate.BackupReader.CryptoHelper.dll need to be copied to the application directory. Note, the files do not need to be “referenced” in the Visual Studio environment, but need to be copied to the output folder.


Referenced assemblies:

  • RedGate.BackupReader.dll
  • RedGate.BackupReader.SqbReader.dll
  • RedGate.Shared.SQL.dll
  • RedGate.Shared.Utils.dll
  • RedGate.SQLCompare.Engine.dll

C#

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using RedGate.BackupReader;
  5. using RedGate.BackupReader.SqbReader;
  6. using RedGate.SQLCompare.Engine.ReadFromBackup;
  7. using RedGate.Shared.Utils;
  8. using RedGate.Shared.SQL;
  9. using RedGate.SQLCompare.Engine;
  10. using RedGate.Shared.SQL.ExecutionBlock;
  11.  
  12. namespace Schema_Sync_To_Backup
  13. {
  14.         class Program
  15.         {
  16.                 static void Main(string[] args)
  17.                 {
  18.                         /* Backup reader can support combinations of FULL and DIFFERENTIAL
  19.                           * backup files. Put these in a generic string list. For our
  20.                           * demonstration purposes, we are using only one full backup.*/
  21.  
  22.                         List<string> files = new List<string>(1);
  23.                         files.Add(@"c:\WidgetStaging.bak");
  24.                         BackupDatabaseSource backupDatabaseSource = new BackupDatabaseSource();
  25.                         backupDatabaseSource.Files = files;
  26.                         BackupSetDatabase backupDatabase = new BackupSetDatabase();
  27.                         backupDatabase.Status += new StatusEventHandler(StatusCallback);
  28.                         Console.WriteLine("Registering backup " + files[0]);
  29.                         try
  30.                         {
  31.                                 backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
  32.                         }
  33.                         catch (RedGate.BackupReader.SqbReader.PasswordProtectedException)
  34.                         {
  35.                                 /* If we have reached this block, the backup is an encrypted
  36.                                   * SQL Backup file and we must ask for the password!
  37.                                   * NB the decryption requires RedGate.BackupReader.CryptoHelper.dll
  38.                                   * and zlib1.dll to be copied into your output directory */
  39.                                 Console.WriteLine("This SQL Backup file is password protected.\r\nPlease enter the password:");
  40.                                 string sqbPassword = Console.ReadLine();
  41.                                 backupDatabaseSource.Passwords.Add(sqbPassword);
  42.                                 backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.Default);
  43.                         }
  44.                         catch (RedGate.BackupReader.BackupReaderException brx)
  45.                         {
  46.                                 // If we have reached this block, something generally bad has happened.
  47.                                   Console.WriteLine("Could not register backup: " + brx.Message);
  48.                         }
  49.                         /* Now, to register the live database */
  50.                         Database liveDatabase = new Database();
  51.                         liveDatabase.Status += new StatusEventHandler(StatusCallback);
  52.                         Console.WriteLine("Registering live database WidgetProduction");
  53.                         liveDatabase.Register(new ConnectionProperties(".", "WidgetProduction"),Options.Default);
  54.                         /* Compare the backup to the live database */
  55.                         Differences schemaDifferences=backupDatabase.CompareWith(liveDatabase, Options.Default);
  56.                         foreach (Difference schemaDifference in schemaDifferences)
  57.                         {
  58.                                 /*Select only schema objects that exist exclusively in the backup.
  59.                                   * Note that with Options.Default, any dependent objects are included
  60.                                   * by default. To stop this, unset Options.IncludeDependencies */
  61.                                 if (schemaDifference.Type == DifferenceType.OnlyIn1)
  62.                                 {
  63.                                         Console.WriteLine(
  64.                                                 "\r\nMigrating {0}:{1}",schemaDifference.Type.ToString(),
  65.                                                 schemaDifference.Name);
  66.                                         schemaDifference.Selected = true;
  67.                                 }
  68.                                 else schemaDifference.Selected = false;
  69.                         }
  70.  
  71.                         /*Create migration SQL script */
  72.  
  73.                         Work work = new Work();
  74.                         work.BuildFromDifferences(schemaDifferences, Options.Default, true);
  75.                         ExecutionBlock eb = work.ExecutionBlock;
  76.  
  77.                         /* Write SQL script to console */
  78.  
  79.                         Console.WriteLine(eb.GetString());
  80.  
  81.                         /* Execute the migration script */
  82.  
  83.                         BlockExecutor executor = new BlockExecutor();
  84.                         executor.ExecuteBlock(eb, liveDatabase.ConnectionProperties.ToDBConnectionInformation());
  85.  
  86.                         /* Cleanup */
  87.  
  88.                         eb.Dispose();
  89.                         liveDatabase.Dispose();
  90.                         backupDatabase.Dispose();
  91.                 }
  92.                 /// <summary>
  93.                 /// Example callback function matching the StatusEventHandler delegate.
  94.                 /// </summary>
  95.                 /// <remarks>
  96.                 /// This reports on SQL Compare's progress by displaying any messages or percentage
  97.                 /// notifications on the console.
  98.                 /// </remarks>
  99.                 private static void StatusCallback(object sender, StatusEventArgs e)
  100.                 {
  101.                         // Fired by the SqlProvider to indicate events
  102.                         if (e.Message != null)
  103.                         {
  104.                                 Console.WriteLine("\r{0}",e.Message);
  105.                         }
  106.  
  107.                         if (e.Percentage != -1)
  108.                         {
  109.                               Console.Write("\r \r{0}%",e.Percentage);
  110.                         }
  111.  
  112.                 }
  113.         }
  114. }


VB

  1. Imports System
  2. Imports System.Collections.Generic
  3. Imports System.Text
  4. Imports RedGate.BackupReader
  5. Imports RedGate.BackupReader.SqbReader
  6. Imports RedGate.SQLCompare.Engine.ReadFromBackup
  7. Imports RedGate.Shared.Utils
  8. Imports RedGate.Shared.SQL
  9. Imports RedGate.SQLCompare.Engine
  10. Imports RedGate.Shared.SQL.ExecutionBlock
  11.  
  12. Module Module1
  13.         Sub Main(ByVal args As String())
  14.                 ' Backup reader can support combinations of FULL and DIFFERENTIAL
  15.                 ' * backup files. Put these in a generic string list. For our
  16.                 ' * demonstration purposes, we are using only one full backup.
  17.  
  18.  
  19.                 Dim files As New List(Of String)(1)
  20.                 files.Add("c:\WidgetStaging.bak")
  21.                 Dim backupDatabaseSource As New BackupDatabaseSource()
  22.                 backupDatabaseSource.Files = files
  23.                 Dim backupDatabase As New BackupSetDatabase()
  24.                 backupDatabase.Status = New StatusEventHandler(AddressOf StatusCallback)
  25.                 Console.WriteLine("Registering backup " & files(0))
  26.                 Try
  27.                         backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.[Default])
  28.                 Catch generatedExceptionName As RedGate.BackupReader.SqbReader.PasswordProtectedException
  29.                         ' If we have reached this block, the backup is an encrypted
  30.                         ' SQL Backup file and we must ask for the password!
  31.                         ' NB the decryption requires RedGate.BackupReader.CryptoHelper.dll
  32.                         ' and zlib1.dll to be copied into your output directory
  33.  
  34.                         Console.WriteLine("This SQL Backup file is password protected." & vbCr & vbLf & "Please enter the password:")
  35.                         Dim sqbPassword As String = Console.ReadLine()
  36.                         backupDatabaseSource.Passwords.Add(sqbPassword)
  37.                         backupDatabase.Register(backupDatabaseSource.ToConnectionProperties(), Options.[Default])
  38.                 Catch brx As RedGate.BackupReader.BackupReaderException
  39.                         ' If we have reached this block, something generally bad has happened.
  40.                         Console.WriteLine("Could not register backup: " & brx.Message)
  41.                 End Try
  42.                 ' Now, to register the live database
  43.  
  44.                 Dim liveDatabase As New Database()
  45.                 liveDatabase.Status = New StatusEventHandler(AddressOf StatusCallback)
  46.                 Console.WriteLine("Registering live database WidgetProduction")
  47.                 liveDatabase.Register(New ConnectionProperties(".", "WidgetProduction"), Options.Default)
  48.                 ' Compare the backup to the live database
  49.  
  50.                 Dim schemaDifferences As Differences = backupDatabase.CompareWith(liveDatabase, Options.[Default])
  51.                 For Each schemaDifference As Difference In schemaDifferences
  52.                         'Select only schema objects that exist exclusively in the backup.
  53.                         'Note that with Options.Default, any dependent objects are included
  54.                         'by default. To stop this, unset Options.IncludeDependencies
  55.  
  56.                         If schemaDifference.Type = DifferenceType.OnlyIn1 Then
  57.                                 Console.WriteLine(vbCr & vbLf & "Migrating {0}:{1}", schemaDifference.Type.ToString(), schemaDifference.Name)
  58.                                 schemaDifference.Selected = True
  59.                         Else
  60.                                 schemaDifference.Selected = False
  61.                         End If
  62.                 Next
  63.  
  64.                 'Create migration SQL script
  65.  
  66.  
  67.                 Dim work As New Work()
  68.                 work.BuildFromDifferences(schemaDifferences, Options.[Default], True)
  69.                 Dim eb As ExecutionBlock = work.ExecutionBlock
  70.  
  71.                 ' Write SQL script to console
  72.  
  73.  
  74.                 Console.WriteLine(eb.GetString())
  75.  
  76.                 ' Execute the migration script
  77.  
  78.                 'Uncomment the next two lines to synchronize the live database
  79.                 'Dim executor As New BlockExecutor()
  80.                 'executor.ExecuteBlock(eb, liveDatabase.ConnectionProperties.ToDBConnectionInformation())
  81.  
  82.                 ' Cleanup
  83.  
  84.  
  85.                 eb.Dispose()
  86.                 liveDatabase.Dispose()
  87.                 backupDatabase.Dispose()
  88.         End Sub
  89.         ''' <summary>
  90.         ''' Example callback function matching the StatusEventHandler delegate.
  91.         ''' </summary>
  92.         ''' <remarks>
  93.         ''' This reports on SQL Compare's progress by displaying any messages or percentage
  94.         ''' notifications on the console.
  95.         ''' </remarks>
  96.         Private Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
  97.                 ' Fired by the SqlProvider to indicate events
  98.                 If e.Message IsNot Nothing Then
  99.                         Console.WriteLine(vbCr & "{0}", e.Message)
  100.                 End If
  101.  
  102.                 If e.Percentage <> -1 Then
  103.                         Console.Write(vbCr & " " & vbCr & "{0}%", e.Percentage)
  104.  
  105.                 End If
  106.         End Sub
  107. End Module
Personal tools