Some SQL-DMO objects supporting Transact-SQL command batch generation from objects representing Microsoft® SQL Server™ administrative tasks. 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 on 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