Backing Up a Database
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Database backup examples illustrate backup operations against an entire database.
When using SQL Distributed Management Objects (SQL-DMO) to perform a backup operation against an entire database, the Backup object used provides, at least, a source database and a target device. A backup against an entire database can back up all data (complete) or only that data changed after the last backup (differential).
Use database backup when backup of the database transaction log is not part of a database maintenance plan. Small databases and databases that change infrequently are good targets for database backup. When these conditions exist, regular complete backup, or an initial complete backup and subsequent, intermittent differential backups, can safely protect data in most cases.
Examples
A. Performing a Complete Database Backup
This example illustrates using SQL-DMO to perform a complete database backup.
' Create a Backup object and set action and source database properties.
Dim oBackup As New SQLDMO.Backup
oBackup.Action = SQLDMOBackup_Database
oBackup.Database = "Northwind"
' Example illustrates a striped backup using two target devices. Note:
' Device creation is not illustrated in this example.
oBackup.Devices = "[NorthDev1],[NorthDev2]"
' Optional. Backup set name and description properties provide
' descriptive text when backup header is displayed for the device(s).
oBackup.BackupSetName = "Northwind_Full"
oBackup.BackupSetDescription = "Full backup of Northwind sample."
' Call SQLBackup method to perform the backup. In a production
' environment, consider wrapping the method call with a wait pointer
' or use Backup object events to provide feedback to the user.
'
' Note: Create and connect of SQLServer object used is not
' illustrated in this example.
oBackup.SQLBackup oSQLServer
B. Performing a Differential Backup on a Database
This example illustrates using SQL-DMO to perform a differential database backup.
' Create a Backup object and set action and source database properties.
Dim oBackup As New SQLDMO.Backup
oBackup.Action = SQLDMOBackup_Differential
oBackup.Database = "Northwind"
' Example illustrates backup implemented to a single operating system
' file. A file naming convention could be easily applied allowing
' rapid identification of a specific differential backup.
oBackup.Files = _
"c:\program files\microsoft sql server\mssql\backup\NorthDiff.bak"
' Optional. When backup is directed to one or more files, set media
' name, backup set name and description to provide in-file
' documentation of the file and backup set contained.
oBackup.MediaName = "NorthDiff.bak " & Date & " " & Time
oBackup.BackupSetName = "NorthDiff"
oBackup.BackupSetDescription = _
"Differential backup of Northwind sample."
' Call SQLBackup method to perform the backup. In a production
' environment, consider wrapping the method call with a wait pointer
' or use Backup object events to provide feedback to the user.
'
' Note: Create and connect of SQLServer object used is not
' illustrated in this example.
oBackup.SQLBackup oSQLServer
See Also
Reference
Backup Object
SQLServer Object