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 MirroringTo 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 BackupTo perform asynchronous database backup, begin by declaring a progress event notification handler function that prints the completion percentage to the disk: Visual Basic .NETSub OnPercentComplete (ByVal sender As Object, ByVal a As PercentCompleteEventArgs) Console.WriteLine(a.Percent.ToString + "% backed-up") End Sub C# .NETvoid 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
C# .NET
Create a Backup object and initialize it: Visual Basic .NETDim 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# .NETBackup 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 .NETAddHandler backupInstance.PercentComplete, AddressOf OnPercentComplete AddHandler backupInstance.Complete, AddressOf OntComplete C# .NETbackupInstance.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 OneAnother 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 .NETDim restoreInstance As Restore restoreInstance = new Restore() restoreInstance.Database = databaseName; restoreInstance.Devices.AddDevice(BackupFileName, DeviceType.File) restoreInstance.ReplaceDatabase = true restoreInstance.PercentCompleteNotification = 25 C# .NETRestore 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 .NETAddHandler restoreInstance.PercentComplete, AddressOf OnPercentComplete C# .NETrestoreInstance.PercentComplete += new PercentCompleteEventHandler(OnPercentComplete); Finally, start the synchronous restore process: restoreInstance.SqlRestore(serverInstance); |