Professional SQL Server™ 2005 Programming

25.5. Backing Up a Database

For this one, we're actually going to switch over and use the AdventureWorks database just to give us something to make a meatier backup of.

As you might suspect from how many different objects we've seen so far, the Backup object isits own thing. It is considered a child of the Server object but has its own set of properties and methods.

To create a backup, you do the same server connection code that we've seen several times now:

private void btnBackupDB_Click(object sender, EventArgs e)
{
   // Create the server and connect to it.
   ServerConnection cn = new ServerConnection();
   cn.LoginSecure = true;

   Server svr = new Server(cn);
   svr.ConnectionContext.Connect();

We're then ready to create a new Backup object. Note that, unlike the Database object, whichwe associated with a server early on, we don't need to reference a specific server for our Backup object until we actually go to execute the backup.

// Create and define backup object
Backup bkp = new Backup();
bkp.Action = BackupActionType.Database;
bkp.Database = "AdventureWorks";
bkp.Devices.AddDevice(@"c:\SMOSMOSample.bak", DeviceType.File);

I've created the Backup object and told it what kind of a backup it should expect to do (A Database backup as opposed to, say, a Log backup). I've also set what database it's going to be backing up and defined a device for it to use.

Note that, while here I defined a file device and path on the fly, you could just as easily connect to the server and query what devices ...