Backup Object

SQL-DMO

Methods

Abort Method	SQLBackup Method
GenerateSQL Method (Backup, Restore)

Events

Complete Event	PercentComplete Event
NextMedia Event

Remarks

With the Backup object, you can:

• Back up a SQL Server database or database transaction log.
  
  
• Generate a Transact-SQL BACKUP statement defining a backup.
  
  
• Monitor a backup operation, reporting status to the user.

For SQL Server, a database delimits the largest backup unit. Though many different database backup images can be maintained on any single medium, a backup cannot span more than a single database. By default, backup operations performed with the Backup object back up a complete database.

SQL Server can write a backup to one of four media types: disk, tape, named pipe, or a proprietary media called a backup device. SQL Server supports backup striping. A striped backup is one directed to more than a single device. When striped, a backup is written across the devices in equal chunks. Striping is supported to a single media type only. That is, a backup can be written to two tape devices. However, SQL Server cannot write one-half of a backup to a tape device, and the other half to a disk.

At a minimum, you must supply values for a backup source and a backup target when using the Backup object. The Database property specifies the backup operation source. SQL-DMO implements supported media types in the Backup object properties Files, Devices, Pipes, and Tapes. Use one media type property to specify the backup operation target.

To perform a complete database backup

1. Create a new Backup object.
   
   
2. Set the Database property, naming the database backed up.
   
   
3. Set a media property to name the target device(s).
   
   
4. Call the SQLBackup method.

In many installations, complete database backup is not a viable option. The Backup object offers access to a number of strategies that ensure data integrity by capturing a subset of the database image.

To back up a database transaction log

1. Create a new Backup object.
   
   
2. Set the Database property, naming the database backed up.
   
   
3. Set the Action property to SQLDMOBackup_Log.
   
   
4. Set a media property to name the target device(s).
   
   
5. Call the SQLBackup method.

To perform a differential backup

1. Create a new Backup object.
   
   
2. Set the Database property, naming the database backed up.
   
   
3. Set the Action property to SQLDMOBackup_Incremental.
   
   
4. Set a media property to name the target device(s).
   
   
5. Call the SQLBackup method.

To back up specific filegroups

1. Create a new Backup object.
   
   
2. Set the Database property, naming the database backed up.
   
   
3. Set the DatabaseFileGroups property, naming the filegroup(s) providing backup source data.
   
   
4. Set a media property to name the target device(s).
   
   
5. Call the SQLBackup method.

To back up specific files

1. Create a new Backup object.
   
   
2. Set the Database property, naming the database backed up.
   
   
3. Set the Action property to SQLDMOBackup_Files.
   
   
4. Set the DatabaseFiles property, naming the file(s) providing backup source data.
   
   
5. Set a media property to name the target device(s).
   
   
6. Call the SQLBackup method.

Settings for any other Backup object properties are optional. Use the optional settings when conditions require extraordinary processing. For example, the MediaName and MediaDescription properties provide, primarily, data used to ensure media availability for tape devices and are applicable when the backup operation defined will initialize the media. For more information about property applicability and use, see individual property documentation.

Note  The Backup object is compatible with instances of SQL Server 2000 and SQL Server version 7.0. However, the Backup2 object extends the functionality of the Backup object for use with features that are new in SQL Server 2000.

See Also

Backup2 Object