The most important thing you can do as a database administrator is perform regular database maintenance. This includes regular backups, database integrity checks and optimizations. In SQL Server 2000, Microsoft provided a way to create maintenance plans, which used xp_sqlmaint, to perform all these tasks. In SQL Server 2005, maintenance plans are defined using Integration Services packages, which generate Transact-SQL statements to perform similar tasks. If you only have a few servers it’s fairly easy to walk through the dialogs to set up your maintenance plans and SQL Server Agent will perform the maintenance based on the schedule you’ve defined.

I don’t know how many servers you manage, but in my environment I have about fifteen production servers and at least that many development and QA servers. Most of these are running SQL Server 2005, but about a half-dozen are still running SQL Server 2000. I wanted to create a standard way to establish maintenance plans on all my servers and I wanted theses maintenance plans to always cover all my databases (including the ones I’ve just created at any time).

SMO (Server Management Objects) is an object library which allows you to write programs to manage SQL Server. In my blog in December 2006, I shared the code I use to create Database Mail accounts in SQL Server 2005 (http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/12/19/26253.aspx). On my SQL Server 2000 servers, I use Gert Drapers’ xp_smtp_sendmail tool (http://www.sqldev.net/xp/xpsmtp.htm). Once the notification framework is in place (we do want to be notified when things fail, don’t we?) we can create the processes that generate our maintenance plans.

To cover my needs, I created three SMO programs in my maintenance “suite”:

• CreateDailyBackupJobs – creates jobs to do full backups of all user databases nightly, transaction log backups every 60 minutes, and full system database backups once a week (based on the parameters I normally set
• CreateAlertLogBackups,- creates transaction log backups triggered by Performance Condition Alerts, based on the transaction log filling up
• CreateWeeklyMaintJobs – creates jobs to perform integrity checks and optimizations on the user databases, once a week

All three programs are console applications which accept command-line parameters for required information. In this article I’ll describe the first program, CreateDailyBackupJobs, which will create Agent jobs to backup the user and system databases, as well as the transaction logs for the user databases. Subsequent articles will cover the other two.

CreateDailyBackupJobs: Automating Backups with SMO

The CreateDailyBackupJobs program takes care of what is perhaps the most important task for a database administrator to perform. This article shows you how to automate your backups using:

• The built-in backup tools provided by Microsoft, specifically the Transact-SQL Backup command for SQL Server 2005 and xp_sqlmaint for SQL Server 2000.
• Your third party backup tool of choice – in these examples, we use SQL Backup, from Red-Gate Software.

The first thing you’ll need to do is to select Add Reference from the Project menu. Within the Add Reference dialog, use the Ctrl key to select the following assemblies (you may have to browse to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder):

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll

Next, in the code window at the top of the code, before any declarations, insert the following lines:

Module Module1

You’ll also need to change the Sub Main() statement to:

Sub Main(ByVal CmdArgs() As String)

This change will allow you to process the command-line arguments, which is the first thing we need to do in each application.

Most of my servers use named instances so I can’t just tell the program to connect to “.”. Therefore, I pass in the server name as the first parameter.

        Dim strServerName As String
        Dim strProdServer As String
        Dim intTLogInterval As Integer
        Dim intRetDays As Integer
        Dim intFullBkupStart As Integer
        Dim strBackupPassword As String  ‘ the password for the Backup
‘(Red Gate SQL Backup Only)
        Dim intRedgate As Integer
        Dim strNotificationEmail As String
        Dim strNotificationSource As String ‘who notified?
        Dim strNotificationSMTP As String ‘What was the SMTP (not SQL 2000)?

        Dim i As Integer

        strServerName = “.”     ‘ The name of the server we’re connecting to
        strProdServer = “P”     ‘ Production servers get Transaction Log Backups
        intTLogInterval = 60    ‘ Most servers transaction log interval
‘ is once per hour
        intRetDays = 3          ‘ Most servers will retain files for 3 days
        intFullBkupStart = 2    ‘ Most backups start at 2am
        strBackupPassword = “”   ‘ the password for the Backup
‘(Red Gate SQL Backup Only)
        strNotificationEmail = “Me@MyOrganisation.com” ‘who do we notify?
        strNotificationSource = “MyServer@MyOrganisation.com” ‘who notified?
        strNotificationSMTP = “smtp.MyOrganisation.com”
‘What was the SMTP (not SQL 2000)?
        intRedgate = 0

        For i = 0 To UBound(CmdArgs)
            Dim strCmdArg As String
            strCmdArg = CmdArgs(i)
            If Left(strCmdArg, 1) = “-” Then
                Select Case Mid(strCmdArg, 2, 1)
                    Case “S” ‘the server name
                        strServerName = Mid(strCmdArg, 3, Len(strCmdArg) – 2)
                    Case “P”    ‘is it a production server
                        strProdServer = Mid(strCmdArg, 3, 1)
                    Case “I”    ‘the log interval
                        intTLogInterval = CInt(Mid(strCmdArg, 3, Len(strCmdArg) – 2))
                    Case “R”   ‘retention days
                        intRetDays = CInt(Mid(strCmdArg, 3, Len(strCmdArg) – 2))
                    Case “B”    ‘backup start hour
                        intFullBkupStart = CInt(Mid(strCmdArg, 3, Len(strCmdArg) – 2))
                    Case “W”    ‘the backup password
                        strBackupPassword = Mid(strCmdArg, 3, Len(strCmdArg) – 2)
                    Case “G”    ‘Are we doing a nice Redgate backup?
                        intRedgate = CInt(Mid(strCmdArg, 3, Len(strCmdArg) – 2))
                    Case “T”    ‘Email: who do we notify?
                        strNotificationEmail = Mid(strCmdArg, 3, Len(strCmdArg) – 2)
                    Case “F”    ‘From Whom: who was the source?
                        strNotificationSource = Mid(strCmdArg, 3, Len(strCmdArg) – 2)
                    Case “E”    ‘what SMTP server?
                        strNotificationSMTP = Mid(strCmdArg, 3, Len(strCmdArg) – 2)
                End Select
            End If
        Next

So, for example, to schedule a native backup, you could issue the following command line:

   ‘u:\DBMaint\BuildDailyBackupJob -S”MyServer\Inst01″ -PP -I15 -R3 -B2 _
-Tmyname@myorg.com

The job will connect to server “MyServer\Inst01”, cause that server to be treated as a Production Server, generate transaction log backups every 15 minutes, retain those backup files for 3 days and start the full backup job every morning at 2AM and notify mynam@myorg.com of the backup.

Alternatively, you can schedule the backup through your tool of choice. In this example, we use Red Gate SQL Backup, via the following command line:

   u:\DBMaint\BuildDailyBackupJob -S”MyServer” -PP -I30 -R5 -B5 -R3 -G1 -W”brian”

-T”myname@myorg.com” -F”hisname@myorg.com” -E”SMTP.myorg.com”

        ‘S”MyServer” use MyServer
        ‘-PP        treat it as a production server (p)
        ‘-I30       number of minutes between log backups (30 here)
        ‘-R5        number of days to retain the files before deleting them (5 here)
        ‘-B5        backup start hour 5AM in this example
        ‘-W”brian”  the backup password (redgate only). Brian in this example
        ‘-G1        Is this using Redgate’s SQL Backup (Yes in this example)
        ‘-T”who@Where”     To Email: who do we notify?
        ‘-F”From@Where”    From Whom: who was the source?
        ‘-E”SMTP.WHO.COM”  ‘what SMTP server? (SQL 2005 only)

The job will connect to server “MyServer”, cause that server to be treated a Production Server, generate transaction log backups every 30 minutes, retain those backup files for 5 days, and start the full backup job every morning at 5AM.

The first thing we need to do is to connect to the server, which is handled in this code:

   Try
      ‘Connect to the server
      Dim srvMgmtServer As Server
      srvMgmtServer = New Server(strServerName)
      Dim srvConn As ServerConnection
      srvConn = srvMgmtServer.ConnectionContext
      srvConn.LoginSecure = True

We’ll be creating three Agent jobs in this program, and, because we run this job every day, these jobs will normally already exist, so we need to delete the existing jobs with the same names first.

        Dim tblServerJobs As DataTable
        Dim rowServerJobs As DataRow
        Dim jobDumpJob As Job
        ‘Drop the existing database_dump job
        tblServerJobs = srvMgmtServer.JobServer.EnumJobs
        For Each rowServerJobs In tblServerJobs.Rows
            If rowServerJobs(“Name”) = “DailyFullBackup” _
            Or rowServerJobs(“Name”) = “TransLogBackup” _
            Or rowServerJobs(“Name”) = “SystemFullBackup” Then
                jobDumpJob = srvMgmtServer.JobServer.Jobs(rowServerJobs(“Name”))
                jobDumpJob.Drop()
            End If
        Next

The jobs are gone so we can start building the new Agent jobs. We do this within subroutines to make the program easier to follow. All servers get full backups and system database backups, but only production servers (in my environment) get transaction log backups. In fact, this program will set the user databases on non-production servers to Simple Recovery mode so the transaction logs are automatically truncated on checkpoint. Within the main subroutine all that’s left to do is execute the job creation subroutines, and build in some robust error handling:

           BuildDailyFullBackup(srvMgmtServer, intTLogInterval, intRetDays, _

intFullBkupStart, strProdServer, intRedgate, strBackupPassword, _

strNotificationEmail, strNotificationSource, strNotificationSMTP)
            ‘ We only need Transaction Log backups on Production Servers
            If strProdServer = “P” Then
                BuildDailyTlogBackup(srvMgmtServer, intTLogInterval, intRetDays,

intFullBkupStart, intRedgate, strBackupPassword, _

strNotificationEmail, strNotificationSource, strNotificationSMTP)
            End If
            BuildSystemFullBackup(srvMgmtServer, intRedgate, strBackupPassword, _

strNotificationEmail, strNotificationSource, strNotificationSMTP)
        Catch smoex As SmoException
            Console.WriteLine(“There has been an SMO error”)
            ‘Display the SMO exception message.
            Console.WriteLine(smoex.Message)
            ‘Display the sequence of non-SMO exceptions that caused the SMO exception.
            Dim ex As Exception
            ex = smoex.InnerException
            Do While ex.InnerException IsNot (Nothing)
                Console.WriteLine(ex.InnerException.Message)
                ex = ex.InnerException
            Loop
            ‘Catch other non-SMO exceptions.
        Catch ex As Exception
            Console.WriteLine(“There has been a VB error. ” + ex.Message)
            Do While ex.InnerException IsNot (Nothing)
                Console.WriteLine(ex.InnerException.Message)
                ex = ex.InnerException
            Loop
        End Try
    End Sub

Failure Reporting

The first thing we want to do is create a function for our failure reporting step. We want to use the same program whether we’re running SQL Server 2000 or SQL Server 2005, so within our BuildNotifyStep function we create the appropriate commands based on the version to which we’re connected. This function will return the Transact-SQL command which will send the appropriate notification email, based on the step that failed.

    Private Function BuildNotifyStep( _
     ByVal strJobName As String, _
     ByVal intVersion As Integer, _
     ByVal strDBName As String, _
     ByVal strNotificationEmail As String, _
     ByVal strNotificationSource As String, _
     ByVal strNotificationSMTP As String) As String
        Dim strCmd As String

        If intVersion = 9 Then
            strCmd = “declare @rc int, @subj varchar(255), _
@mesg varchar(255)” + vbCrLf
            strCmd = strCmd + “select @subj = @@servername + _
‘ – ” + strJobName + ” Job Failure'” + vbCrLf
            strCmd = strCmd + “select @mesg = ‘The ” + strJobName + ” job”
            If strDBName <> “” Then
                strCmd = strCmd + ” for database ” + strDBName
            End If
            strCmd = strCmd + ” on ‘ + @@servername + ‘ failed at ‘ + _
convert(varchar(25), getdate(), 100)” + vbCrLf
            strCmd = strCmd + “exec @rc = master.dbo.xp_smtp_sendmail” + _
vbCrLf
            strCmd = strCmd + ”  @FROM      = N'” + strNotificationSource + _
“‘,” + vbCrLf
            strCmd = strCmd + ”  @FROM_NAME = N’DB Admin’,” + vbCrLf
            strCmd = strCmd + ”  @TO        = N'” + strNotificationEmail + _
“‘,” + vbCrLf
            strCmd = strCmd + ”  @priority  = N’NORMAL’,” + vbCrLf
            strCmd = strCmd + ”  @subject   = @subj,” + vbCrLf
            strCmd = strCmd + ”  @message   = @mesg,” + vbCrLf
            strCmd = strCmd + ”  @type      = N’text/plain’,” + vbCrLf
            strCmd = strCmd + ”  @server    = N'” + strNotificationSMTP + _
“‘” + vbCrLf
        Else
            strCmd = “declare @rc int, @subj varchar(255), _
@mesg varchar(255)” + vbCrLf
            strCmd = strCmd + “select @subj = @@servername + ‘ – ” + _
strJobName + ” Job Failure'” + vbCrLf
            strCmd = strCmd + “select @mesg = ‘The ” + strJobName + ” job”
            If strDBName <> “” Then
                strCmd = strCmd + ” for database ” + strDBName
            End If
            strCmd = strCmd + ” on ‘ + @@servername + ‘ failed at ‘” + _

” + convert(varchar(25), getdate(), 100)” + vbCrLf
            strCmd = strCmd + “EXEC msdb.dbo.sp_send_dbmail” + vbCrLf
            strCmd = strCmd + ”  @profile_name = ‘DBMail’,” + vbCrLf
            strCmd = strCmd + ”  @recipients = ‘” + strNotificationEmail + _
“‘,” + vbCrLf
            strCmd = strCmd + ”  @body = @mesg,” + vbCrLf
            strCmd = strCmd + ”  @subject = @subj” + vbCrLf
        End If
        BuildNotifyStep = strCmd

    End Function

This function is used in all three programs, so it accepts the job name, server version and database name as parameters and uses them to construct a meaningful error message. If the error is a server-level error an empty string is passed in the database name parameter.

The BuildDailyFullBackup Agent job

The first subroutine we’ll build is the BuildDailyFullBackup subroutine, which takes all the parameters we received from the command line.

   Sub BuildDailyFullBackup( _
     ByRef srvMgmtServer As Server, _
     ByVal intTLogInterval As Integer, _
     ByVal intRetDays As Integer, _
     ByVal intFullBkupStart As Integer, _
     ByVal strProdServer As String, _
     ByVal intRedgate As Integer, _
     ByVal strBackupPassword As String, _
     ByVal strNotificationEmail As String, _
     ByVal strNotificationSource As String, _
     ByVal strNotificationSMTP As String)

We need to define variables for building the backup job and the various job steps used by the backup job:

        Dim intStepID As Integer            ‘Identify the current StepID
        Dim dbcDatabases As DatabaseCollection  ‘The Server’s Database Collection
        Dim dbDatabase As Database          ‘The Current Database Object
        Dim intVersion As Integer           ‘The Server’s Version Number
        Dim strJobName As String            ‘The Job Name
        Dim strBackupDir As String          ‘The Server’s Backup Directory
        Dim strCmd As String                ‘The Transact-SQL command
        Dim i As Integer                ‘An iterator

        strJobName = “DailyFullBackup”

        Dim jobUserFullBkupJob As Job           ‘The Job object
        Dim jbsUserFullBkupJobStep As JobStep       ‘The Job Step object
        Dim jbsUserFullBkupFailStep As JobStep ‘The Job Step object for the failure step
        Dim jbschUserFullBkupJobSched As JobSchedule   ‘The Job Schedule object
        Dim strUserFullBkupFailCmd As String    ‘The command string for the failure step

Next, we get the collection of server databases, and set the default initial fields for the server to include the “IsSystemObject” property, which speeds up the processing of the program significantly. We’ll also get the version number of the server we’re connected to, and store the backup directory for the server.

        dbcDatabases = srvMgmtServer.Databases
        srvMgmtServer.SetDefaultInitFields(GetType(Database), “IsSystemObject”)

        ‘Determine the SQL Server Version
        intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))
        strBackupDir = srvMgmtServer.Settings.BackupDirectory

We can now start to create the first job. We’ll instantiate the Job object, using the server’s JobServer object and the job name as properties, then set the description, category, and owner, and create the job. We’ll also initialize the iterator for later use.

       ‘Create the Daily Full Backup Job
        jobUserFullBkupJob = New Job(srvMgmtServer.JobServer, strJobName)
        jobUserFullBkupJob.Description = “Daily Full Backup”
        jobUserFullBkupJob.Category = “[Uncategorized (Local)]”
        jobUserFullBkupJob.OwnerLoginName = “sa”
        jobUserFullBkupJob.Create()
        i = 0

Within the backup job we need to clean up history and old backup files, so the first step in the job handles that. We establish dates to keep job history for one month, and we keep backup files based on the retention days parameter passed in.

The sp_delete_backuphistory command works for both SQL 2000 and SQL 2005. For purging the files, SQL 2000 uses the xp_sqlmaint function we’ll use to back up the files, whereas in SQL 2005 we need to execute the xp_delete_file stored procedure. SQL 2005 also added the @oldest_date parameter to the sp_purge_jobhistory stored procedure so we can use that to delete job history older than one month.

        ‘Cleanup history and backup files
        i += 1
        strCmd = “” + vbCrLf
        strCmd = strCmd + “declare @dtfiles datetime, @dthist datetime” + vbCrLf
        strCmd = strCmd + “select @dthist = dateadd(m, -1, getdate())” + vbCrLf
        strCmd = strCmd + “select @dtfiles = dateadd(d, -” + Trim(CStr(intRetDays)) + _

“, getdate())” + vbCrLf
        strCmd = strCmd + “” + vbCrLf
        strCmd = strCmd + “–Cleanup History” + vbCrLf
        strCmd = strCmd + “exec msdb.dbo.sp_delete_backuphistory @dthist” + vbCrLf
        If intRedgate = 0 And intVersion = 9 Then     

‘ In SQL 2000 sqlmaint purges the files, in SQL 2005 we use xp_delete_file
            strCmd = strCmd + “” + vbCrLf ‘redGate backups do their own purging
            strCmd = strCmd + “–Cleanup Maintenance” + vbCrLf
            strCmd = strCmd + “exec msdb.dbo.sp_purge_jobhistory  @oldest_date=@dthist” _

+ vbCrLf
            strCmd = strCmd + “exec master.dbo.xp_delete_file 0,N'” + strBackupDir + _

“‘,N’bak’,@dtfiles” + vbCrLf
        End If

We’ve built the command for the step, now we need to add the step to the job.

        jbsUserFullBkupJobStep = New JobStep(jobUserFullBkupJob, “Step ” + CStr(i))
        jbsUserFullBkupJobStep.DatabaseName = “master”
        jbsUserFullBkupJobStep.Command = strCmd
        jbsUserFullBkupJobStep.OnSuccessAction = StepCompletionAction.GoToStep
        jbsUserFullBkupJobStep.OnSuccessStep = i + 2
        jbsUserFullBkupJobStep.OnFailAction = StepCompletionAction.GoToNextStep
        jbsUserFullBkupJobStep.Create()
        intStepID = jbsUserFullBkupJobStep.ID
        If i = 1 Then
            jobUserFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)
            jobUserFullBkupJob.StartStepID = intStepID
            jobUserFullBkupJob.Alter()
        End If

Notice that on success we want to skip the next step, because that’s the step that will report the failure of the step we just created. Also, if this is the first step of the job (we know that it is here, but in other subroutines this will be useful) we need to set the job’s starting step ID to this step.

Now we need to build the failure notification step. We load the database name with the word “Cleanup” to indicate that it was the cleanup step that failed, if in fact that occurred. We also set the completion action for both success and failure to go to the next step.

        strUserFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, “Cleanup”, _

strNotificationEmail, strNotificationSource,
_strNotificationSMTP)
        i += 1
        jbsUserFullBkupFailStep = New JobStep(jobUserFullBkupJob, “Step ” + CStr(i))
        jbsUserFullBkupFailStep.DatabaseName = “master”
        jbsUserFullBkupFailStep.Command = strUserFullBkupFailCmd
        jbsUserFullBkupFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep
        jbsUserFullBkupFailStep.OnFailAction = StepCompletionAction.GoToNextStep
        jbsUserFullBkupFailStep.Create()

We now want to iterate through the collection of databases on the server. For this job we only want user databases, and we don’t want snapshot databases.

        For Each dbDatabase In dbcDatabases
            Dim bolProcessDB As Boolean
            Dim strDBName As String

            bolProcessDB = True
            If dbDatabase.IsSystemObject = True Then
                bolProcessDB = False
            End If
            If dbDatabase.CompatibilityLevel = CompatibilityLevel.Version90 Then
                If dbDatabase.IsDatabaseSnapshot Then
                    bolProcessDB = False
                End If
            End If
            strDBName = dbDatabase.Name
            If bolProcessDB = True Then

Now we get to the logic for each database. First we’ll build the command for the step, based on which version of SQL Server we’re using. For SQL 2000 the sqlmaint function does everything we need, but for SQL 2005 we need to execute a Transact-SQL statement to backup the file. We’ll first construct a string containing the date in YYYYMMDDHHMMSS format and use that as part of the backup file name. We then backup the database to that disk file, then do a RESTORE VERIFYONLY to make certain that the backup file is valid.

i += 1
strCmd = “” + vbCrLf
strCmd = strCmd + “–Backup Database ” + strDBName + _
” w/Verify” + vbCrLf
If intRedgate <> 0 Then
strCmd = strCmd + “declare @strbackup varchar(500),  ” _
+ “@strDate varchar(30),@database varchar(50)” _
+ vbCrLf
                    strCmd = strCmd + “declare @dt datetime, _
@command varchar (2000)” + vbCrLf
                    strCmd = strCmd + “declare @backupSetId as int” + vbCrLf
                    strCmd = strCmd + “” + vbCrLf
                    strCmd = strCmd + “select @dt = getdate()” + vbCrLf
                    strCmd = strCmd + “set @strDate = _
CONVERT(varchar, @dt , 112)” + vbCrLf
                    strCmd = strCmd + “set @strDate = @strDate + ” _
+ “Left(CONVERT(varchar, @dt , 108),2)” _
+ vbCrLf
                    strCmd = strCmd + “set @strDate = @strDate + ” _
+ “SubString(CONVERT(varchar, @dt , 108),4,2)” _
+ vbCrLf
                    strCmd = strCmd + “set @strDate = @strDate + ” _
+ “SubString(CONVERT(varchar, @dt , 108),7,2)” _
+ vbCrLf
                    strCmd = strCmd + “” + vbCrLf
                    strCmd = strCmd + “–Backup Transaction Log ” + strDBName _
+ ” w/Verify” + vbCrLf
                    strCmd = strCmd + “set @database = ‘” + strDBName + “‘” + vbCrLf
                    strCmd = strCmd + “set @strbackup = ‘” + strBackupDir _
+ “‘ + ‘\FULL_’+@@ServerName+’_’ + @database _
+ ‘_’ + @strDate + ‘.sqb'” + vbCrLf
                    strCmd = strCmd + “–Backup System Database ” + strDBName _
+ vbCrLf
                    strCmd = strCmd + “Select @command= _’-SQL “”BACKUP DATABASE [” _
+ strDBName _
                        + “] TO DISK = ”’+ @strBackup + ”’ WITH ” _

+ IIf(strBackupPassword <> “”, “PASSWORD = ”” _
                        + strBackupPassword + “”,”, “”) _
                        + ” ERASEFILES=14, ” + vbCrLf
                    strCmd = strCmd + “INIT, VERIFY, NAME = ”Database (” _
+ strDBName _
                        + “), ‘ + CONVERT(varchar, @dt , 113) + ”’ ” _
+ ” , DESCRIPTION = ”Backup on ‘ _
+ CONVERT(varchar, @dt , 113) ” _
+ ” + ‘  Database: ” _
                        + strDBName + ”  Instance: (local)”” _
+ ” “” -E ‘ ” + vbCrLf
                    strCmd = strCmd + “Execute master..sqlbackup @command”

                Else

‘ In SQL Server 2000 use the xp_sqlmaint procedure

If intVersion = 8 Then
                    ‘Full Backups
                    strCmd = strCmd + “EXECUTE master.dbo.xp_sqlmaint N’-D ” _
+ strDBName + ”  -VrfyBackup”
                    strCmd = strCmd + ” -BkUpMedia DISK -BkUpDB  _
-UseDefDir  -DelBkUps “
                    strCmd = strCmd + Trim(CStr(intRetDays)) _
+ “DAYS -BkExt “”BAK””‘” + vbCrLf

                Else
                    ‘Full Backups
                    strCmd = strCmd + “declare @strbackup varchar(500),  _

@strDate varchar(30), ” _
+ ”  @database varchar(50)” + vbCrLf
                    strCmd = strCmd + “declare @dt datetime” + vbCrLf
                    strCmd = strCmd + “declare @backupSetId as int” + vbCrLf
                    strCmd = strCmd + “” + vbCrLf
                    strCmd = strCmd + “select @dt = getdate()” + vbCrLf
                    strCmd = strCmd + “set @strDate = CONVERT(varchar, @dt , 112)” _
+ vbCrLf
                    strCmd = strCmd + “set @strDate = @strDate + ” _
+ “Left(CONVERT(varchar, @dt , 108),2)” + vbCrLf
                    strCmd = strCmd + “set @strDate = @strDate + ” _
+ ” SubString(CONVERT(varchar, @dt , 108),4,2)” + vbCrLf
                    strCmd = strCmd + “set @strDate = @strDate + ” _
+ “SubString(CONVERT(varchar, @dt , 108),7,2)” + vbCrLf
                    strCmd = strCmd + “” + vbCrLf
                    strCmd = strCmd + “set @database = ‘” + strDBName + “‘” + vbCrLf
                    strCmd = strCmd + “set @strbackup = ‘” + strBackupDir + “‘ + ‘\’ _
+ @database + ‘_backup_’ + @strDate + ‘.bak'” _
+ vbCrLf
                    strCmd = strCmd + “BACKUP DATABASE [” + strDBName + “] _
TO  DISK = @strbackup WITH NOFORMAT, _
INIT,” + vbCrLf
                    strCmd = strCmd + “NAME = N'” + strDBName _
+ “-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, _
STATS = 10″ + vbCrLf
                    strCmd = strCmd + “select @backupSetId = _
position from msdb..backupset” _
+ vbCrLf
                    strCmd = strCmd + “where database_name=N'” + strDBName + “‘” _
+ vbCrLf
                    strCmd = strCmd + “and backup_set_id=(select max(backup_set_id) _
from msdb..backupset _
where database_name=N'” + strDBName _
+ “‘ )” + vbCrLf
                    strCmd = strCmd + “if @backupSetId is null begin raiserror _
(N’Verify failed. Backup information for _
database ”” _
+ strDBName + “” not found.’, 16, 1) end” + vbCrLf
                    strCmd = strCmd + “RESTORE VERIFYONLY FROM  DISK = _
@strbackup WITH  FILE = @backupSetId, _
NOUNLOAD,  NOREWIND” + vbCrLf
                End If

The command now built, we’ll construct the step and add it to the job.

            jbsUserFullBkupJobStep = New JobStep(jobUserFullBkupJob, “Step ” _
+ CStr(i) + ” Full Backup of ” + strDBName)
                jbsUserFullBkupJobStep.DatabaseName = dbDatabase.Name
                jbsUserFullBkupJobStep.Command = strCmd
                jbsUserFullBkupJobStep.OnSuccessAction = StepCompletionAction.GoToStep
                jbsUserFullBkupJobStep.OnSuccessStep = i + 2
                jbsUserFullBkupJobStep.OnFailAction = StepCompletionAction.GoToNextStep
                jbsUserFullBkupJobStep.Create()
                intStepID = jbsUserFullBkupJobStep.ID
                If i = 1 Then
                    jobUserFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)
                    jobUserFullBkupJob.StartStepID = intStepID
                    jobUserFullBkupJob.Alter()
                End If

We then add the failure notification step.

strUserFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _
dbDatabase.Name, strNotificationEmail, _
strNotificationSource, strNotificationSMTP)
                i += 1
                jbsUserFullBkupFailStep = New JobStep(jobUserFullBkupJob, _
“Step ” + CStr(i))
                jbsUserFullBkupFailStep.DatabaseName = “master”
                jbsUserFullBkupFailStep.Command = strUserFullBkupFailCmd
                jbsUserFullBkupFailStep.OnSuccessAction = _
StepCompletionAction.GoToNextStep
                jbsUserFullBkupFailStep.OnFailAction = _
StepCompletionAction.GoToNextStep
                jbsUserFullBkupFailStep.Create()

            End If
        Next

Now we’ve cycled through all the databases, we need a dummy step so the final database has a step to go to when it’s successful, so I added this step.

        i += 1
        jbsUserFullBkupJobStep = New JobStep(jobUserFullBkupJob, “Step ” + CStr(i))
        jbsUserFullBkupJobStep.DatabaseName = “master”
        jbsUserFullBkupJobStep.Command = “select 1”
        jbsUserFullBkupJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
        jbsUserFullBkupJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess
        jbsUserFullBkupJobStep.Create()

Now we can schedule the job. We want the job to run daily, so we set the job schedule’s FrequencyTypes property to Daily and the FrequencySubDayTypes to Once. We set the ActiveStartTimeOfDay to a TimeSpan variable initialized to the time we passed in for the start time for the full backup. We set the end time to one second before midnight. We also need to set the FrequencyInterval to 1 and set the ActiveStartDate to Today. Then we can create the schedule.

      ‘Define a JobSchedule object variable by supplying the parent job
‘and name arguments in the constructor.
        jbschUserFullBkupJobSched = _
New JobSchedule(jobUserFullBkupJob, “Sched 01”)
        ‘Set properties to define the schedule frequency, and duration.
        jbschUserFullBkupJobSched.FrequencyTypes = FrequencyTypes.Daily
        jbschUserFullBkupJobSched.FrequencySubDayTypes = _
FrequencySubDayTypes.Once
        Dim tsUserFullBkupStart As TimeSpan
        tsUserFullBkupStart = New TimeSpan(intFullBkupStart, 0, 0)
        jbschUserFullBkupJobSched.ActiveStartTimeOfDay = _
tsUserFullBkupStart
        Dim tsUserFullBkupEnd As TimeSpan
        tsUserFullBkupEnd = New TimeSpan(23, 59, 59)
        jbschUserFullBkupJobSched.ActiveEndTimeOfDay = _
tsUserFullBkupEnd
        jbschUserFullBkupJobSched.FrequencyInterval = 1
        Dim dtUserFullBkupStartDate As Date = Date.Today
        jbschUserFullBkupJobSched.ActiveStartDate = _
dtUserFullBkupStartDate
        ‘Create the job schedule on the instance of SQL Agent.
        jbschUserFullBkupJobSched.Create()
    End Sub

We’ve now created the job that will do a full backup of every user database once a day at the desired hour.

Transaction Log backup Job

The next subroutine we’ll build will create the transaction log backup job. We’ll run this conditionally, because we set the Dev and QA databases to Simple Recovery mode, so we don’t need log backups. In the Main() subroutine the call looks like this:

    ‘ We only need Transaction Log backups on Production Servers
    ‘   If strProdServer = “P” Then
    ‘      BuildDailyTlogBackup(srvMgmtServer, intTLogInterval, _
    ‘   intRetDays, intFullBkupStart)
    ‘   End If

The structure of the subroutine is very similar to the full backup subroutine. First we define the necessary variables.

    Sub BuildDailyTlogBackup( _
     ByRef srvMgmtServer As Server, _
     ByVal intTLogInterval As Integer, _
     ByVal intRetDays As Integer, _
     ByVal intFullBkupStart As Integer, _
     ByVal intRedgate As Integer, _
     ByVal strBackupPassword As String, _
     ByVal strNotificationEmail As String, _
     ByVal strNotificationSource As String, _
     ByVal strNotificationSMTP As String)

        Dim intStepID As Integer            ‘Identify the current StepID
        Dim dbcDatabases As DatabaseCollection  ‘The Server’s Database Collection
        Dim dbDatabase As Database          ‘The Current Database Object
        Dim intVersion As Integer           ‘The Server’s Version Number
        Dim strJobName As String            ‘The Job Name
        Dim strBackupDir As String          ‘The Server’s Backup Directory
        Dim strCmd As String                ‘The Transact-SQL command
        Dim i As Integer                ‘An iterator

        strJobName = “TransLogBackup

‘ Add 1 hour to the start of the full backups for the
‘ start of the tlog backups.
       intFullBkupStart += 1  
‘The Job object

        Dim jobUserTlogBkupJob As Job           
‘The Job Step object

        Dim jbsUserTlogBkupJobStep As JobStep      
‘The Job Step object for the failure step

        Dim jbsUserTlogBkupFailStep As JobStep
‘The Job Schedule object

        Dim jbschUserTlogBkupJobSched As JobSchedule 
‘The command string for the failure step

        Dim strUserTlogBkupFailCmd As String       

Notice that we set the start of the transaction log backups to one hour after the start of the full backup job. In some cases the transaction log backups fail if the full backups are running at the same time, so this protects us from these problems. Given that the backups are generally run in the wee hours of the morning there’s generally not a lot of transaction activity at this time any way.

Now we can get the database collection, set the default init fields, get the server version and backup directory for the server, as we did for the full backup. We’ll also create the new job.

        dbcDatabases = srvMgmtServer.Databases
        srvMgmtServer.SetDefaultInitFields(GetType(Database), “IsSystemObject”)

        ‘Determine the SQL Server Version
        intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))
        strBackupDir = srvMgmtServer.Settings.BackupDirectory

        ‘Create the Trans Log Backup Job
        jobUserTlogBkupJob = New Job(srvMgmtServer.JobServer, strJobName)
        jobUserTlogBkupJob.Description = “Trans Log Backup”
        jobUserTlogBkupJob.Category = “[Uncategorized (Local)]”
        jobUserTlogBkupJob.OwnerLoginName = “sa”
        jobUserTlogBkupJob.Create()
        ‘strJobID = jobUserTlogBkupJob.JobID
        i = 0

In SQL 2000 the sqlmaint function purges the transaction log backup files, but in SQL 2005 we use the xp_delete_file stored procedure to delete the files, so we need to build both the step to execute the stored procedure and the step to report if there was a problem with the delete.

        If intVersion = 9 Then
            ‘Cleanup backup files
            i += 1

            strCmd = “” + vbCrLf
            strCmd = strCmd + “declare @dtfiles datetime” + vbCrLf
            strCmd = strCmd + “select @dtfiles = dateadd(d, -” _

+ Trim(CStr(intRetDays)) _
+ “, getdate())” + vbCrLf
            strCmd = strCmd + “” + vbCrLf
            strCmd = strCmd + “–Cleanup Maintenance” + vbCrLf
            strCmd = strCmd + “exec master.dbo.xp_delete_file 0,N'” _
+ strBackupDir + “‘,N’trn’,@dtfiles” + vbCrLf
            jbsUserTlogBkupJobStep = New JobStep(jobUserTlogBkupJob, “Step ” _
+ CStr(i))
            jbsUserTlogBkupJobStep.DatabaseName = “master”
            jbsUserTlogBkupJobStep.Command = strCmd
            jbsUserTlogBkupJobStep.OnSuccessAction = _
StepCompletionAction.GoToStep
            jbsUserTlogBkupJobStep.OnSuccessStep = i + 2
            jbsUserTlogBkupJobStep.OnFailAction = _
StepCompletionAction.GoToNextStep
            jbsUserTlogBkupJobStep.Create()
            intStepID = jbsUserTlogBkupJobStep.ID

            If i = 1 Then
                jobUserTlogBkupJob.ApplyToTargetServer(srvMgmtServer.Name)
                jobUserTlogBkupJob.StartStepID = intStepID
                jobUserTlogBkupJob.Alter()
            End If

            strUserTlogBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _

“Cleanup”, strNotificationEmail, _
               strNotificationSource, strNotificationSMTP)
            i += 1
            jbsUserTlogBkupFailStep = New JobStep(jobUserTlogBkupJob, _
“Step ” + CStr(i))
            jbsUserTlogBkupFailStep.DatabaseName = “master”
            jbsUserTlogBkupFailStep.Command = strUserTlogBkupFailCmd
            jbsUserTlogBkupFailStep.OnSuccessAction = _
StepCompletionAction.GoToNextStep
            jbsUserTlogBkupFailStep.OnFailAction = _
StepCompletionAction.GoToNextStep
            jbsUserTlogBkupFailStep.Create()
        End If

Like for the full backup job, we want to iterate through the collection of databases on the server, and we only want user databases, but no snapshot databases.

Now we get to the log backup job step. We set the database name, increment the step number, and start the job step command string.

                Dim strDBName As String
                strDBName = dbDatabase.Name

                i += 1

                strCmd = “” + vbCrLf
                strCmd = strCmd + “–Backup Database ” _
+ strDBName + ” w/Verify” + vbCrLf

We’ll only back up the transaction log if the recovery model is set to Full (I don’t have any databases set to Bulk Logged). If the server is SQL 2000 we’ll build a call to xp_sqlmaint, but if it’s SQL 2005 we’ll build the date string for the file name, execute the BACKUP LOG statement to back it up to the disk file in the correct backup directory, and then do a RESTORE VERIFYONLY to make sure the backup is good.

                ‘Trans Log Backups
                If dbDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Full
Then
                    If intRedgate <> 0 Then ” 
                        strCmd = strCmd + “declare @strbackup varchar(500), _
@strDate varchar(30),  @database varchar(50)” _
+ vbCrLf
                        strCmd = strCmd + “declare @dt datetime, _
@command varchar (2000)” + vbCrLf
                        strCmd = strCmd + “declare @backupSetId as int” _
+ vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “select @dt = getdate()” + vbCrLf
                        strCmd = strCmd + “set @strDate = _
CONVERT(varchar, @dt , 112)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ Left(CONVERT(varchar, @dt , 108),2)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ SubString(CONVERT(varchar, @dt , 108),4,2)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ SubString(CONVERT(varchar, @dt , 108),7,2)” _
+ vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “–Backup Transaction Log ” _
+ strDBName + ” w/Verify” + vbCrLf
                        strCmd = strCmd + “set @database = ‘” + strDBName _
+ “‘” + vbCrLf
                        strCmd = strCmd + “set @strbackup = ‘” + strBackupDir _
                            + “‘ + ‘\LOG_’+@@ServerName+’_’ + @database _
+ ‘_’ + @strDate + ‘.sqb'” + vbCrLf
                        strCmd = strCmd + “–Backup Transaction Log ” _
+ strDBName + vbCrLf
                        strCmd = strCmd + “Select @command= ‘-SQL “”BACKUP LOG _
[” + strDBName + “] TO DISK = ”’ _
+ @strBackup + ”’ WITH ” _
                            + IIf(strBackupPassword <> “”, “PASSWORD = ”” _
+ strBackupPassword + “”,”, “”) _
                             + ” ERASEFILES=” + Trim(CStr(intRetDays)) _
+ “, ” + vbCrLf
                        strCmd = strCmd + “INIT, VERIFY, NAME = _
”Database (” + strDBName + “), ‘ _
+ CONVERT(varchar, @dt , 113) + ”’, _
DESCRIPTION = ”Backup on ‘ _
+ CONVERT (varchar, @dt , 113) _
+ ‘  Database: ” + strDBName + _
”  Instance: (local)”” _
+ ” “” -E ‘ ” + vbCrLf
                        strCmd = strCmd + “Execute master..sqlbackup @command”
                    ElseIf intVersion = 8 Then
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “–Backup Transaction Log ” + strDBName _
+ ” w/Verify” + vbCrLf
                        strCmd = strCmd + “EXECUTE master.dbo.xp_sqlmaint N’-D ” _
+ strDBName + ”  -VrfyBackup”
                        strCmd = strCmd + ” -BkUpMedia DISK -BkUpLog _
-UseDefDir  -DelBkUps “
                        strCmd = strCmd + Trim(CStr(intRetDays)) _
+ “DAYS -BkExt “”TRN””‘” + vbCrLf
                    Else
                        strCmd = strCmd + “declare @strbackup varchar(500), _
@strDate varchar(30),  _
@database varchar(50)” _
+ vbCrLf
                        strCmd = strCmd + “declare @dt datetime, @dtfiles datetime, _
@dthist datetime, @sysfiles datetime” + vbCrLf
                        strCmd = strCmd + “declare @backupSetId as int” + vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “select @dt = getdate()” + vbCrLf
                        strCmd = strCmd + “set @strDate = _

CONVERT(varchar, @dt , 112)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate + _
Left(CONVERT(varchar, @dt , 108),2)” + vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate + _
SubString(CONVERT(varchar, @dt , 108),4,2)” + vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate + _
SubString(CONVERT(varchar, @dt , 108),7,2)” + vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “–Backup Transaction Log ” + _
strDBName + ” w/Verify” + vbCrLf
                        strCmd = strCmd + “set @database = ‘” + strDBName + “‘” _
+ vbCrLf
                        strCmd = strCmd + “set @strbackup = ‘” + strBackupDir _
+ “‘ + ‘\’ + @database + ‘_tlog_’ _
+ @strDate + ‘.trn'” + vbCrLf
                        strCmd = strCmd + “BACKUP LOG [” + strDBName + “]
TO  DISK = @strbackup WITH NOFORMAT, _
NOINIT,” + vbCrLf
                        strCmd = strCmd + “NAME = N'” + strDBName _
+ “-Transaction Log Backup’, SKIP, REWIND, _
NOUNLOAD,  STATS = 10″ + vbCrLf
                        strCmd = strCmd + “select @backupSetId = position _
from msdb..backupset” _
+ vbCrLf
                        strCmd = strCmd + “where database_name=N'” + strDBName _
+ “‘” + vbCrLf
                        strCmd = strCmd + “and backup_set_id=(select max(backup_set_id)
from msdb..backupset where
database_name=N'” + strDBName + “‘ )” _
+ vbCrLf
                        strCmd = strCmd + “if @backupSetId is null
begin raiserror(N’Verify failed. Backup
information for database ”” + strDBName _
+ “” not found.’, 16, 1) end” + vbCrLf
                        strCmd = strCmd + “RESTORE VERIFYONLY FROM  DISK = _
@strbackup WITH  FILE = @backupSetId, _
NOUNLOAD,  NOREWIND” + vbCrLf
                    End If
                End If

Now we can create the job step using the command we just built, set the job’s first step if necessary, and build the error notification step to report any problems that occur during the backup.

                jbsUserTlogBkupJobStep = New JobStep(jobUserTlogBkupJob, “Step ” _
+ CStr(i))
                jbsUserTlogBkupJobStep.DatabaseName = dbDatabase.Name
                jbsUserTlogBkupJobStep.Command = strCmd
                jbsUserTlogBkupJobStep.OnSuccessAction = StepCompletionAction.GoToStep
                jbsUserTlogBkupJobStep.OnSuccessStep = i + 2
                jbsUserTlogBkupJobStep.OnFailAction = StepCompletionAction.GoToNextStep
                jbsUserTlogBkupJobStep.Create()
                intStepID = jbsUserTlogBkupJobStep.ID

                If i = 1 Then
                    jobUserTlogBkupJob.ApplyToTargetServer(srvMgmtServer.Name)
                    jobUserTlogBkupJob.StartStepID = intStepID
                    jobUserTlogBkupJob.Alter()
                End If

                strUserTlogBkupFailCmd = BuildNotifyStep(strJobName, _
intVersion, dbDatabase.Name, _
strNotificationEmail, _
                    strNotificationSource, _
strNotificationSMTP)
                i += 1
                jbsUserTlogBkupFailStep = New JobStep(jobUserTlogBkupJob, _
“Step ” + CStr(i))
                jbsUserTlogBkupFailStep.DatabaseName = “master”
                jbsUserTlogBkupFailStep.Command = strUserTlogBkupFailCmd
                jbsUserTlogBkupFailStep.OnSuccessAction = _
StepCompletionAction.GoToNextStep
                jbsUserTlogBkupFailStep.OnFailAction = _
StepCompletionAction.GoToNextStep
                jbsUserTlogBkupFailStep.Create()
            End If
        Next

Just like for the full backup job, we need a dummy step for the last backup to jump to if successful.

        i += 1
        jbsUserTlogBkupJobStep = New JobStep(jobUserTlogBkupJob, “Step ” + CStr(i))
        jbsUserTlogBkupJobStep.DatabaseName = “master”
        jbsUserTlogBkupJobStep.Command = “select 1”
        jbsUserTlogBkupJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
        jbsUserTlogBkupJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess
        jbsUserTlogBkupJobStep.Create()

The transaction log backups are done pretty regularly during the day to provide more consistent recovery from failure. I normally run them once an hour, but one server is set to run them every 15 minutes. The interval in minutes is passed in from the command line to accommodate the appropriate backup strategy. Once again, the start of the job is set to one hour after the start of the full backup job.

        ‘Define a JobSchedule object variable by supplying the parent job

‘and name arguments in the constructor.
        jbschUserTlogBkupJobSched = New JobSchedule(jobUserTlogBkupJob, “Sched 01”)
        ‘Set properties to define the schedule frequency, and duration.
        jbschUserTlogBkupJobSched.FrequencyTypes = FrequencyTypes.Daily
        Dim tsUserTlogBkupStart As TimeSpan
        tsUserTlogBkupStart = New TimeSpan(intFullBkupStart, 0, 0)
        jbschUserTlogBkupJobSched.ActiveStartTimeOfDay = tsUserTlogBkupStart
        Dim tsUserTlogBkupEnd As TimeSpan
        tsUserTlogBkupEnd = New TimeSpan(23, 59, 59)
        jbschUserTlogBkupJobSched.ActiveEndTimeOfDay = tsUserTlogBkupEnd
        jbschUserTlogBkupJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Minute
        jbschUserTlogBkupJobSched.FrequencySubDayInterval = intTLogInterval
        jbschUserTlogBkupJobSched.FrequencyTypes = FrequencyTypes.Daily
        jbschUserTlogBkupJobSched.FrequencyInterval = 1
        Dim dtUserTlogBkupStartDate As Date = Date.Today
        jbschUserTlogBkupJobSched.ActiveStartDate = dtUserTlogBkupStartDate
        ‘Create the job schedule on the instance of SQL Agent.
        jbschUserTlogBkupJobSched.Create()

    End Sub

The System database backup job

The last backup job we need to create is for the system databases. I set all my servers to run this backup job once a week at 2am on Sunday morning. I keep these backup files on disk for two weeks. If I make any changes on my servers I can easily run this job manually to prevent any loss, but things generally don’t change frequently enough to justify running the backups more regularly. Your mileage may vary.

    Sub BuildSystemFullBackup(ByRef srvMgmtServer As Server, _
    ByVal intRedgate As Integer, _
    ByVal strBackupPassword As String, _
    ByVal strNotificationEmail As String, _
    ByVal strNotificationSource As String, _
    ByVal strNotificationSMTP As String)

        Dim intStepID As Integer            ‘Identify the current StepID
        Dim dbcDatabases As DatabaseCollection  ‘The Server’s Database Collection
        Dim dbDatabase As Database          ‘The Current Database Object
        Dim intVersion As Integer           ‘The Server’s Version Number
        Dim strJobName As String            ‘The Job Name
        Dim strBackupDir As String          ‘The Server’s Backup Directory
        Dim strCmd As String                ‘The Transact-SQL command
        Dim i As Integer                ‘An iterator

        strJobName = “SystemFullBackup”

        Dim jobSystemFullBkupJob As Job           ‘The Job object
        Dim jbsSystemFullBkupJobStep As JobStep   ‘The Job Step object

        Dim jbsSystemFullBkupFailStep As JobStep  ‘The Job Step object for
‘the failure step
        Dim jbschSystemFullBkupJobSched As JobSchedule ‘The Job Schedule object
        Dim strSystemFullBkupFailCmd As String    ‘The command string for
‘the failure step

We grab the databases collection, set the default init fields, get the version and backup directories, like before, and create the job.

        dbcDatabases = srvMgmtServer.Databases
        srvMgmtServer.SetDefaultInitFields(GetType(Database), “IsSystemObject”)

        ‘Determine the SQL Server Version
        intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))
        strBackupDir = srvMgmtServer.Settings.BackupDirectory

        ‘Create the Trans Log Backup Job
        jobSystemFullBkupJob = New Job(srvMgmtServer.JobServer, strJobName)
        jobSystemFullBkupJob.Description = “System Full Backup”
        jobSystemFullBkupJob.Category = “[Uncategorized (Local)]”
        jobSystemFullBkupJob.OwnerLoginName = “sa”
        jobSystemFullBkupJob.Create()
        i = 0

We need to clean up the old backup files if we’re running SQL 2005. Also, note that for SQL 2005 servers I created a subfolder called ‘System’ in the server’s backup directory, so I can have a different cleanup frequency for the system backup files. This is because the xp_delete_file will delete everything with the supplied extension value, and both user and system databases are backed up with the .bak extension.

        If intVersion = 9 Then
            ‘Cleanup backup files
            i += 1

            strCmd = “” + vbCrLf
            strCmd = strCmd + “declare @sysfiles datetime” + vbCrLf
            strCmd = strCmd + “select @sysfiles = dateadd(d, -14, getdate())” _
+ vbCrLf
            strCmd = strCmd + “” + vbCrLf
            strCmd = strCmd + “–Cleanup Maintenance” + vbCrLf
            strCmd = strCmd + “exec master.dbo.xp_delete_file 0,N'” _
+ strBackupDir + “\System\’,N’bak’,@sysfiles” _
+ vbCrLf

            jbsSystemFullBkupJobStep = New JobStep(jobSystemFullBkupJob, “Step ” _
+ CStr(i))
            jbsSystemFullBkupJobStep.DatabaseName = “master”
            jbsSystemFullBkupJobStep.Command = strCmd
            jbsSystemFullBkupJobStep.OnSuccessAction = _
StepCompletionAction.GoToStep
            jbsSystemFullBkupJobStep.OnSuccessStep = i + 2
            jbsSystemFullBkupJobStep.OnFailAction = _
StepCompletionAction.GoToNextStep
            jbsSystemFullBkupJobStep.Create()
            intStepID = jbsSystemFullBkupJobStep.ID

            If i = 1 Then
                jobSystemFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)
                jobSystemFullBkupJob.StartStepID = intStepID
                jobSystemFullBkupJob.Alter()
            End If

            strSystemFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _
“Cleanup”, strNotificationEmail, _
                strNotificationSource, strNotificationSMTP)
            i += 1
            jbsSystemFullBkupFailStep = New JobStep(jobSystemFullBkupJob, “Step ” _
+ CStr(i))
            jbsSystemFullBkupFailStep.DatabaseName = “master”
            jbsSystemFullBkupFailStep.Command = strSystemFullBkupFailCmd
            jbsSystemFullBkupFailStep.OnSuccessAction = _
StepCompletionAction.GoToNextStep
            jbsSystemFullBkupFailStep.OnFailAction = _
StepCompletionAction.GoToNextStep
            jbsSystemFullBkupFailStep.Create()
        End If

We need to loop through the databases, but notice that the loop is a little different than the other two subroutines. We only want system databases here, but we don’t want to back up tempdb.

        For Each dbDatabase In dbcDatabases
            If dbDatabase.IsSystemObject = True Then
                Dim strDBName As String
                strDBName = dbDatabase.Name

                If strDBName <> “tempdb” Then
…

Now, within the loop we build the command to back up the system databases, using either xp_sqlmaint or building the date string and file name, and building the backup and verify Transact-SQL statements.

                    i += 1

                    strCmd = “” + vbCrLf
                    strCmd = strCmd + “–Backup Database ” + strDBName _
+ ” w/Verify” + vbCrLf
                    If intRedgate <> 0 Then ” 
                        strCmd = strCmd + “declare @strbackup varchar(500), _
@strDate varchar(30),  @database varchar(50)” _
+ vbCrLf
                        strCmd = strCmd + “declare @dt datetime, _

@command varchar (2000)” + vbCrLf
                        strCmd = strCmd + “declare @backupSetId as int” + vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “select @dt = getdate()” + vbCrLf
                        strCmd = strCmd + “set @strDate = _
CONVERT(varchar, @dt , 112)” + vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ Left(CONVERT(varchar, @dt , 108),2)” + vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ SubString(CONVERT(varchar, @dt , 108),4,2)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ SubString(CONVERT(varchar, @dt , 108),7,2)” _
+ vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “–Backup Transaction Log ” _
+ strDBName _
+ ” w/Verify” + vbCrLf
                        strCmd = strCmd + “set @database = ‘” _
+ strDBName + “‘” + vbCrLf
                        strCmd = strCmd + “set @strbackup = ‘” _
+ strBackupDir + “‘ _
+ ‘\FULL_’+@@ServerName+’_’ _
+ @database + ‘_’ + @strDate _
+ ‘.sqb'” + vbCrLf
                        strCmd = strCmd + “–Backup System Database ” _
+ strDBName + vbCrLf
                        strCmd = strCmd + “Select @command= _
‘-SQL “”BACKUP DATABASE [” _
                            + strDBName + “] TO DISK = ”’ _
+ @strBackup + ”’ WITH ” _
                            + IIf(strBackupPassword <> “”, _
“PASSWORD = ”” _
+ strBackupPassword _
+ “”,”, “”) _
                            + ” ERASEFILES=14, ” _
+ vbCrLf
                        strCmd = strCmd + “INIT, VERIFY, NAME = _
”Database (” + strDBName + “), ‘ _
+ CONVERT(varchar, @dt , 113) _
+ ”’, DESCRIPTION = ”Backup on ‘ _
+ CONVERT(varchar, @dt , 113) _
+ ‘  Database: ” + strDBName _
+ ”  Instance: (local)”” _
+ ” “” -E ‘ ” + vbCrLf
                        strCmd = strCmd + “Execute master..sqlbackup @command”
‘ In SQL Server 2000 use the xp_sqlmaint procedure to
‘ backup the databases and delete old backup files
                   ElseIf intVersion = 8 Then
                        ‘Full Backups
                        strCmd = strCmd + “EXECUTE master.dbo.xp_sqlmaint N’-D ” _
+ strDBName + ”  -VrfyBackup”
                        strCmd = strCmd + ” -BkUpMedia DISK -BkUpDB _
-UseDefDir  -DelBkUps “
                        strCmd = strCmd + “14DAYS -BkExt “”BAK””‘” + vbCrLf
                    Else
                        ‘Full Backups
                        strCmd = strCmd + “declare @strbackup varchar(500), _
@strDate varchar(30),  _
@database varchar(50)” _
+ vbCrLf
                        strCmd = strCmd + “declare @dt datetime” + vbCrLf
                        strCmd = strCmd + “declare @backupSetId as int” + vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “select @dt = getdate()” + vbCrLf
                        strCmd = strCmd + “set @strDate = _
CONVERT(varchar, @dt , 112)” + vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ Left(CONVERT(varchar, @dt , 108),2)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ SubString(CONVERT(varchar, @dt , 108),4,2)” _
+ vbCrLf
                        strCmd = strCmd + “set @strDate = @strDate _
+ SubString(CONVERT(varchar, @dt , 108),7,2)” _
+ vbCrLf
                        strCmd = strCmd + “” + vbCrLf
                        strCmd = strCmd + “set @database = ‘” + strDBName _
+ “‘” + vbCrLf
                        strCmd = strCmd + “set @strbackup = ‘” + strBackupDir _
+ “‘ + ‘\System\’ + @database + ‘_backup_’ _
+ @strDate + ‘.bak'” + vbCrLf
                        strCmd = strCmd + “BACKUP DATABASE [” + strDBName + “] _
TO  DISK = _
@strbackup WITH NOFORMAT, INIT,” + vbCrLf
                        strCmd = strCmd + “NAME = N'” + strDBName _
+ “-Full Database Backup’, SKIP, NOREWIND, _
NOUNLOAD, STATS = 10″ _
+ vbCrLf
                        strCmd = strCmd + _
“select @backupSetId = position _

from msdb..backupset” _
+ vbCrLf
                        strCmd = strCmd + “where database_name=N'” + strDBName _
+ “‘” + vbCrLf
                        strCmd = strCmd + _
“and backup_set_id=(select max(backup_set_id) _
from msdb..backupset where _
database_name=N'” + strDBName + “‘ )” _
+ vbCrLf
                        strCmd = strCmd + _
“if @backupSetId is null begin raiserror _
(N’Verify failed. Backup information _
for database ”” _
+ strDBName + “” not found.’, 16, 1) end” _
+ vbCrLf
                        strCmd = strCmd + _
“RESTORE VERIFYONLY FROM  DISK = _
@strbackup WITH  FILE = _
@backupSetId, NOUNLOAD,  NOREWIND” _
+ vbCrLf
                    End If

Once the command is built we build the step to use the command, and build the error notification step for the database.

                    jbsSystemFullBkupJobStep = New JobStep(jobSystemFullBkupJob, _
“Step ” + CStr(i))
                    jbsSystemFullBkupJobStep.DatabaseName = dbDatabase.Name
                    jbsSystemFullBkupJobStep.Command = strCmd
                    jbsSystemFullBkupJobStep.OnSuccessAction = _
StepCompletionAction.GoToStep
                    jbsSystemFullBkupJobStep.OnSuccessStep = i + 2
                    jbsSystemFullBkupJobStep.OnFailAction = _
StepCompletionAction.GoToNextStep
                    jbsSystemFullBkupJobStep.Create()
                    intStepID = jbsSystemFullBkupJobStep.ID

                    If i = 1 Then
                        jobSystemFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)
                        jobSystemFullBkupJob.StartStepID = intStepID
                        jobSystemFullBkupJob.Alter()
                    End If

                    strSystemFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _
dbDatabase.Name, strNotificationEmail, strNotificationSource, _
strNotificationSMTP)
                    i += 1
                    jbsSystemFullBkupFailStep = New JobStep(jobSystemFullBkupJob, _
“Step ” + CStr(i))
                    jbsSystemFullBkupFailStep.DatabaseName = “master”
                    jbsSystemFullBkupFailStep.Command = strSystemFullBkupFailCmd
                    jbsSystemFullBkupFailStep.OnSuccessAction = _
StepCompletionAction.GoToNextStep
                    jbsSystemFullBkupFailStep.OnFailAction = _
StepCompletionAction.GoToNextStep
                    jbsSystemFullBkupFailStep.Create()
                End If
            End If
        Next

Again, once we’re done with all the databases in the loop, we build the dummy step, as in the other subroutines.

        i += 1
        jbsSystemFullBkupJobStep = New JobStep(jobSystemFullBkupJob, _
“Step ” + CStr(i))
        jbsSystemFullBkupJobStep.DatabaseName = “master”
        jbsSystemFullBkupJobStep.Command = “select 1”
        jbsSystemFullBkupJobStep.OnSuccessAction = _
StepCompletionAction.QuitWithSuccess
        jbsSystemFullBkupJobStep.OnFailAction =
StepCompletionAction.QuitWithSuccess
        jbsSystemFullBkupJobStep.Create()

Last, we define the job schedule, specifying that the job is run weekly, starting at 2am on Sunday.

This program, once implemented, will create the jobs that run our full backups for our user and system databases, and will run the transaction log backups for our user databases, all at the intervals we find most appropriate for our business needs.