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 


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


[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 


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 


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:


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 


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 


restoreInstance.PercentComplete += new PercentCompleteEventHandler(OnPercentComplete); 

Finally, start the synchronous restore process:


Inside SQL Server 2005 Tools
Inside SQL Server 2005 Tools
ISBN: 0321397967
EAN: 2147483647
Year: 2006
Pages: 149 © 2008-2017.
If you may any questions please contact us: