Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Backing Up Selected Portions of 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.

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

When using SQL Distributed Management Objects (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.

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.