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.