Scripting a Database Backup For Scheduled Execution

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.

Some SQL Distributed Management Objects (SQL-DMO) objects support Transact-SQL command batch generation. The command batch generated can be used to create a SQL Server Agent job which can be scheduled for execution.

This example illustrates backup operation definition and creation of a Transact-SQL command batch representing the operation. For more information about creating and scheduling SQL Server Agent jobs by using SQL-DMO, see SQL-DMO Examples: Jobs and Schedules.

' Dimension a string object used to capture the Transact-SQL command
' batch implementing the backup.
Dim strBackup as String

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

' Example illustrates backup of multiple file groups.
oBackup.DatabaseFileGroups = "[PRIMARY],[NorthwindTextImg]"

' 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_FileGroups_" & Date & "_" & Time
oBackup.BackupSetDescription = _
    "Backup of PRIMARY and NorthwindTextImg filegroups."

' Call GenerateSQL method to generate the Transact-SQL command batch.
' The command batch returned can provide a value for the Command
' property of a JobStep object.
' Note: A connected SQLServer object is not necessary for routine
' execution.
strBackup = oBackup.GenerateSQL

Community Additions