Backing up Selected Portions of a Database

SQL-DMO

SQL-DMO

Backing up Selected Portions of a Database

Backing up selected portions of a database examples illustrate backup operations against a discrete subset of database data.

When using SQL-DMO to perform a backup operation against a portion of a database, the Backup object used provides, at least, a source database, the source portion, and a target device. A backup against a subset of database data can back up all data in an operating system file implementing database storage, all data in all files within a filegroup, or committed transaction log records.

Generally, backup of a portion of a database is chosen when backup of an entire database is not a viable option due to database size or high-frequency of transactions. However, backup of a file or filegroup can be an effective strategy even for relatively small databases when server configuration lends itself to a file-based backup operation.

Examples
A. Backing Up a Database File

This example illustrates using SQL-DMO to perform a backup of a single operating system file implementing database storage.

' Create a Backup object and set action and source database properties.
Dim oBackup As New SQLDMO.Backup
oBackup.Action = SQLDMOBackup_Files
oBackup.Database = "Northwind"

oBackup.DatabaseFiles = "Northwind_txt1"

' Example illustrates backup implemented to a single operating system
' file. A file naming convention could be easily applied allowing
' rapid identification of a specific backup.
oBackup.Files = "c:\program files\microsoft sql server\mssql\backup\NorthText.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 = "NorthText.bak " & Date & " " & Time
oBackup.BackupSetName = "NorthDBFileText"
oBackup.BackupSetDescription = _
    "Backup of a database file by logical name."

' 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. Backing Up a Database Filegroup

This example illustrates using SQL-DMO to perform a backup of operating system file implementing the PRIMARY filegroup of a database.

' Create a Backup object and set action and source database properties.
Dim oBackup As New SQLDMO.Backup
oBackup.Action = SQLDMOBackup_Files
oBackup.Database = "Northwind"

oBackup.DatabaseFileGroups = "PRIMARY"

' Example illustrates backup implemented to a single operating system
' file. A file naming convention could be easily applied allowing
' rapid identification of a specific backup.
oBackup.Files = "c:\program files\microsoft sql server\mssql\backup\NorthFGPrim.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 = "NorthFGPrim.bak " & Date & " " & Time
oBackup.BackupSetName = "NorthFGPrim"
oBackup.BackupSetDescription = _
    "Backup of PRIMARY filegroup 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
C. Backing Up a Database Transaction Log

This example illustrates using SQL-DMO to perform a backup of a database transaction log.

' Create a Backup object and set action and source database properties.
Dim oBackup As New SQLDMO.Backup
oBackup.Action = SQLDMOBackup_Log
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_Log_" & Date & "_" & Time
oBackup.BackupSetDescription = _
    "Backup of Northwind sample database transaction log."

' 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

Backup Object

SQLServer Object