Section 12.2. Backing Up and Restoring Data


12.2. Backing Up and Restoring Data

The SMO backup and restore classes serve several purposes:

  • To back up and restore data and logs

  • To perform integrity checks

  • To examine database consistency

The SMO backup and restore classes are described in Table 12-4. These classes are located in the Microsoft.SqlServer.Management.Smo namespace.

Table 12-4. SMO classes for managing backup and restore operations

Class

Description

Backup

Provides programmatic access to SQL Server backup operations.

BackupDevice

Represents a SQL Server backup devicea tape drive or disk drive used in a backup operation.

BackupDeviceCollection

Represents a collection of backup devices as BackupDevice objects. The BackupDevices property of the Server class returns the collection of all backup devices on an instance of SQL Server.

BackupDeviceItem

Provides programmatic access to SQL Server backup devices by name.

PercentCompleteEventArgs

Represents the details of the event that occurs when a backup or restore process reaches a percent-complete marker.

RelocateFile

Provides a mechanism to relocate files during a restore operation. The RelocateFile class is used with the RelocateFiles property of the Restore class.

Restore

Provides programmatic access to SQL Server restore operations. The Restore class lets you restore all or part of a database and transaction log records, verify the integrity of a backup medium, identify the contents of a backup medium, and monitor the status of a restore operation.

VerifyCompleteEventArgs

Represents the details of the event that occurs when a backup verification completes.


This section shows how to use SMO backup and restore classes programmatically.

This example backs up the AdventureWorks database to a file:

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             BackupDeviceItem bdi =                 new BackupDeviceItem("AdventureWorks.bak", DeviceType.File);             Backup bu = new Backup(  );             bu.Database = "AdventureWorks";             bu.Devices.Add(bdi);             bu.Initialize = true;             // add percent complete and complete event handlers             bu.PercentComplete +=                 new PercentCompleteEventHandler(Backup_PercentComplete);             bu.Complete +=new ServerMessageEventHandler(Backup_Complete);             Server server = new Server("localhost");             bu.SqlBackup(server);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }         protected static void Backup_PercentComplete(             object sender, PercentCompleteEventArgs e)         {             Console.WriteLine(e.Percent + "% processed.");         }         protected static void Backup_Complete(object sender, ServerMessageEventArgs e)         {             Console.WriteLine(Environment.NewLine + e.ToString(  ));         }     } 

Once complete, the backup file AdventureWorks.bak is located (by default) in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup directory. Results are shown in Figure 12-6.

Figure 12-6. Results for backup example


The Backup class provides programmatic access to SQL Server backup operations. Three properties are set to configure the Backup object in this example:


Database

Specifies the database to backup


Devices

Sets the backup device as a BackupDeviceItem instance


Initialize

Specifies whether the backup device is initialized as part of the backup operation

There are other optional properties that further control the backup operation.

A PercentCompleteEventHandler is added so that the progress of the backup operation can be displayed to the console. A ServerMessageEventHandler is added to display the status of the BACKUP DATABASE operation.

Finally, the SqlBackup( ) method is called to perform the database backup operation.

The following example restores the backup created in the preceding example:

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             BackupDeviceItem bdi = new BackupDeviceItem(                 "AdventureWorks.bak", DeviceType.File);             Restore r = new Restore(  );             r.Database = "AdventureWorks";             r.ReplaceDatabase = true;             r.Devices.Add(bdi);             //add percent complete and complete event handlers             r.PercentComplete +=                 new PercentCompleteEventHandler(Restore_PercentComplete);             r.Complete += new ServerMessageEventHandler(Restore_Complete);             r.SqlRestore(server);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }         protected static void Restore_PercentComplete(             object sender, PercentCompleteEventArgs e)         {             Console.WriteLine(e.Percent + "% processed.");         }         protected static void Restore_Complete(object sender, ServerMessageEventArgs e)         {             Console.WriteLine(Environment.NewLine + e.ToString(  ));         }     } 

Results are shown in Figure 12-7.

The Restore class provides programmatic access to SQL Server restore operations. Three properties are set to configure the Restore object in this example:

Figure 12-7. Results for restore example


  • The Database property, which specifies the database on which the restore operation runs.

  • The ReplaceDatabase property, which specifies whether the backup operation creates a new image of the restored database.

  • The Devices property, which sets the backup device for the restore operation as a BackupDeviceItem instance.

There are other optional properties not used in this example that further control the backup operation.

A PercentCompleteEventHandler event handler is added so that the progress of the restore operation can be displayed to the console. A ServerMessageEventHandler event handler is added to display the details of the RESTORE DATABASE event.

Finally, the SqlRestore( ) method is called to perform the database restore operation.

The restore operation requires exclusive access to the database being restored.




Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net