Schema Object Restore from Backup SDK 8

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