Backup and Restore

SMO also provides utility classes to simplify some of the essential database operations. Some of the most widely used classes are Backup and Restore. The following sections demonstrate how to use these classes with a few examples.

Back Up the AdventureWorks Sample Database to a Disk with Mirroring

To back up the database, begin by creating a backup object and initializing it with options:

Backup backupInstance = new Backup();
backupInstance.Database = "AdventureWorks";
backupInstance.Action = BackupActionType.Database;
backupInstance.Checksum = true;
backupInstance.FormatMedia = true;
backupInstance.Initialize = true;
backupInstance.SkipTapeHeader = true;

Create backup devices:

backupInstance.Devices.AddDevice(@"c:\back1a.bak", DeviceType.File);
backupInstance.Devices.AddDevice(@"c:\back1b.bak", DeviceType.File);

Create a backup device list for mirroring:

BackupDeviceList l1 = new BackupDeviceList();
l1.AddDevice(@"c:\Backups\back2a.bak", DeviceType.File);
l1.AddDevice(@"c:\Backups\back2b.bak", DeviceType.File);

BackupDeviceList l2 = new BackupDeviceList();
l2.AddDevice(@"c:\Backups\back3a.bak", DeviceType.File);
l2.AddDevice(@"c:\Backups\back3b.bak", DeviceType.File);

BackupDeviceList[] la = new BackupDeviceList[2];
la[0] = l1;
la[1] = l2;
backupInstance.Mirrors = la;

Create a script backup operation:

StringCollection scriptBacthes = backupInstance.Script(serverInstance);

Perform Asynchronous Database Backup

To perform asynchronous database backup, begin by declaring a progress event notification handler function that prints the completion percentage to the disk:

Visual Basic .NET

Sub OnPercentComplete (ByVal sender As Object, ByVal a As
PercentCompleteEventArgs)
      Console.WriteLine(a.Percent.ToString +  "% backed-up")
End Sub

C# .NET

void OnPercentComplete (object sender,                  PercentCompleteEventArgs a)
{
      Console.WriteLine("Progress = {0}%", a.Percent);
}

Declare an operation completion notification function that checks for errors during backup and reports a final status:

Visual Basic .NET

[View full width]Sub OnComplete (ByVal sender As Object, ByVal a As
ServerMessageEventArgs)
If backup.AsyncStatus.ExecutionStatus <> ExecutionStatus.Succeeded
            Console.WriteLine("Backup had an error: " +  backup.AsyncStatus.LastException
.Message);
     Else
            Console.WriteLine("Backup completed successfully");
End If
End Sub

C# .NET

[View full width]void OnComplete(object sender, ServerMessageEventArgs e)
{
if( backup.AsyncStatus.ExecutionStatus !=         ExecutionStatus.Succeeded )
{
            Console.WriteLine("Backup had an error: " + backup.AsyncStatus.LastException
.Message);
}
    else
    {
              Console.WriteLine("Backup completed successfully");
    }
}

Create a Backup object and initialize it:

Visual Basic .NET

Dim backupInstance As Backup
backupInstance = new Backup()
backupInstance.Database = "AdventureWorks"
backupInstance.BackupSetName = "AdventureWorks Backup"
backupInstance.BackupSetDescription = "Weekly Backup of AdventureWorks"
backupInstance.MediaName = "Set 1"
backupInstance.MediaDescription = "Backup Media Set # 1"
backupInstance.Devices.AddDevice(@"c:\north.bak", DeviceType.File)
backupInstance.Initialize = true
backupInstance.UnloadTapeAfter = true
backupInstance.PercentCompleteNotification = 10
backupInstance.RetainDays = 14
backupInstance.Action = BackupActionType.Database

C# .NET

Backup backupInstance = new Backup();
backupInstance.Database = "AdventureWorks";
backupInstance.BackupSetName = "AdventureWorks Backup";
backupInstance.BackupSetDescription = "Weekly Backup of AdventureWorks";
backupInstance.MediaName = "Set 1";
backupInstance.MediaDescription = "Backup Media Set # 1";
backupInstance.Devices.AddDevice(@"c:\north.bak", DeviceType.File);
backupInstance.Initialize = true;
backupInstance.UnloadTapeAfter = true;
backupInstance.PercentCompleteNotification = 10;
backupInstance.RetainDays = 14;
backupInstance.Action = BackupActionType.Database;

Add event handlers to the backup object:

Visual Basic .NET

AddHandler backupInstance.PercentComplete, AddressOf OnPercentComplete
AddHandler backupInstance.Complete, AddressOf OntComplete

C# .NET

backupInstance.PercentComplete += new
PercentCompleteEventHandler(OnPercentComplete);
backupInstance.Complete += new ServerMessageEventHandler(OnComplete);

Start the asynchronous backup operation. You could also have used the SqlBackup function call to perform the operation synchronously:

backupInstance.SqlBackupAsync(serverInstance);

Restore a Complete Database by Replacing the Existing One

Another backup and restore operation that can be scripted is restoring a database by replacing the existing one. Start by creating a Restore object and initialize it:

Visual Basic .NET

Dim restoreInstance As Restore
restoreInstance = new Restore()
restoreInstance.Database = databaseName;
restoreInstance.Devices.AddDevice(BackupFileName, DeviceType.File)
restoreInstance.ReplaceDatabase = true
restoreInstance.PercentCompleteNotification = 25

C# .NET

Restore restoreInstance = new Restore();
restoreInstance.Database = databaseName;
restoreInstance.Devices.AddDevice(BackupFileName, DeviceType.File);
restoreInstance.ReplaceDatabase = true;
restoreInstance.PercentCompleteNotification = 25;

Add an event handler to show periodic progress, as in the previous example.

Visual Basic .NET

AddHandler restoreInstance.PercentComplete, AddressOf OnPercentComplete

C# .NET

restoreInstance.PercentComplete += new PercentCompleteEventHandler(OnPercentComplete);

Finally, start the synchronous restore process:

restoreInstance.SqlRestore(serverInstance);