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.
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:\mssql7\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
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:\mssql7\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
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
| Backup Object | SQLServer Object |