{"id":245,"date":"2007-04-05T00:00:00","date_gmt":"2007-04-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/automate-your-database-maintenance-using-smo\/"},"modified":"2021-08-24T13:40:44","modified_gmt":"2021-08-24T13:40:44","slug":"automate-your-database-maintenance-using-smo","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/automate-your-database-maintenance-using-smo\/","title":{"rendered":"Automate your Database Maintenance using SMO"},"content":{"rendered":"<p>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 <b>xp_sqlmaint,<\/b> 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&#8217;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&#8217;ve defined.<\/p>\n<p>I don&#8217;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&#8217;ve just created at any time).<\/p>\n<p>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&#8217; <b>xp_smtp_sendmail<\/b> tool (http:\/\/www.sqldev.net\/xp\/xpsmtp.htm). Once the notification framework is in place (we <i>do<\/i> want to be notified when things fail, don&#8217;t we?) we can create the processes that generate our maintenance plans.<\/p>\n<p>To cover my needs, I created three SMO programs in my maintenance &#8220;suite&#8221;:<\/p>\n<ul>\n<li><b>CreateDailyBackupJobs<\/b> &#8211; 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<\/li>\n<li><b>CreateAlertLogBackups<\/b>,- creates transaction log backups triggered by Performance Condition Alerts, based on the transaction log filling up<\/li>\n<li><b>CreateWeeklyMaintJobs<\/b> &#8211; creates jobs to perform integrity checks and optimizations on the user databases, once a week<\/li>\n<\/ul>\n<p>All three programs are console applications which accept command-line parameters for required information. In this article I&#8217;ll describe the first program, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/09\/CreateDailyBackupJob.txt\"><b>CreateDailyBackupJobs<\/b><\/a>, 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.<\/p>\n<h2>CreateDailyBackupJobs: Automating Backups with SMO<\/h2>\n<p>The <b>CreateDailyBackupJobs<\/b> 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:<\/p>\n<ul>\n<li>The built-in backup tools provided by Microsoft, specifically the Transact-SQL Backup command for SQL Server 2005 and <b>xp_sqlmaint<\/b> for SQL Server 2000.<\/li>\n<li>Your third party backup tool of choice &#8211; in these examples, we use <a href=\"http:\/\/www.red-gate.com\/products\/SQL_Backup\/index.htm?utm_source=simpletalk&amp;utm_medium=publink&amp;utm_campaign=sqlbackup&amp;utm_content=article372\">SQL Backup<\/a>, from Red-Gate Software.<\/li>\n<\/ul>\n<p>The first thing you&#8217;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):<\/p>\n<pre><\/pre>\n<p>Microsoft.SqlServer.ConnectionInfo.dll<br \/>\nMicrosoft.SqlServer.Smo.dll<br \/>\nMicrosoft.SqlServer.SmoEnum.dll<br \/>\nMicrosoft.SqlServer.SqlEnum.dll<\/p>\n<p>Next, in the code window at the top of the code, before any declarations, insert the following lines:<\/p>\n<pre class=\"MsoNormal\">Microsoft.SqlServer.Management.SMOImports Imports Microsoft.SqlServer.Management.CommonImports Microsoft.SqlServer.Management.SMO.Agent<\/pre>\n<pre><\/pre>\n<p>Module Module1<\/p>\n<p>You&#8217;ll also need to change the Sub Main() statement to:<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">Sub Main(ByVal CmdArgs() As String)<\/p>\n<p>This change will allow you to process the command-line arguments, which is the first thing we need to do in each application.<\/p>\n<p>Most of my servers use named instances so I can&#8217;t just tell the program to connect to &#8220;.&#8221;. Therefore, I pass in the server name as the first parameter.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strServerName As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strProdServer As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intTLogInterval As Integer<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intRetDays As Integer<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intFullBkupStart As Integer<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strBackupPassword As String\u00a0 &#8216; the password for the Backup<br \/>\n &#8216;(Red Gate SQL Backup Only)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intRedgate As Integer<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strNotificationEmail As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strNotificationSource As String &#8216;who notified?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strNotificationSMTP As String &#8216;What was the SMTP (not SQL 2000)?<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim i As Integer<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strServerName = &#8220;.&#8221;\u00a0\u00a0\u00a0\u00a0 &#8216; The name of the server we&#8217;re connecting to<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strProdServer = &#8220;P&#8221;\u00a0\u00a0\u00a0\u00a0 &#8216; Production servers get Transaction Log Backups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intTLogInterval = 60\u00a0\u00a0\u00a0 &#8216; Most servers transaction log interval<br \/>\n &#8216; is once per hour<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intRetDays = 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; Most servers will retain files for 3 days<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intFullBkupStart = 2\u00a0\u00a0\u00a0 &#8216; Most backups start at 2am<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strBackupPassword = &#8220;&#8221;\u00a0\u00a0 &#8216; the password for the Backup<br \/>\n &#8216;(Red Gate SQL Backup Only)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationEmail = &#8220;Me@MyOrganisation.com&#8221; &#8216;who do we notify?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSource = &#8220;MyServer@MyOrganisation.com&#8221; &#8216;who notified?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSMTP = &#8220;smtp.MyOrganisation.com&#8221;<br \/>\n &#8216;What was the SMTP (not SQL 2000)?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intRedgate = 0<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For i = 0 To UBound(CmdArgs)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strCmdArg As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmdArg = CmdArgs(i)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If Left(strCmdArg, 1) = &#8220;-&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select Case Mid(strCmdArg, 2, 1)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;S&#8221; &#8216;the server name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strServerName = Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;P&#8221;\u00a0\u00a0\u00a0 &#8216;is it a production server<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strProdServer = Mid(strCmdArg, 3, 1)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;I&#8221;\u00a0\u00a0\u00a0 &#8216;the log interval<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intTLogInterval = CInt(Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;R&#8221;\u00a0\u00a0 &#8216;retention days<br \/>\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intRetDays = CInt(Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;B&#8221;\u00a0\u00a0\u00a0 &#8216;backup start hour<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intFullBkupStart = CInt(Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;W&#8221;\u00a0\u00a0\u00a0 &#8216;the backup password<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strBackupPassword = Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;G&#8221;\u00a0\u00a0\u00a0 &#8216;Are we doing a nice Redgate backup?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intRedgate = CInt(Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Case &#8220;T&#8221;\u00a0\u00a0\u00a0 &#8216;Email: who do we notify?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationEmail = Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;F&#8221;\u00a0\u00a0\u00a0 &#8216;From Whom: who was the source?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSource = Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case &#8220;E&#8221;\u00a0\u00a0\u00a0 &#8216;what SMTP server?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSMTP = Mid(strCmdArg, 3, Len(strCmdArg) &#8211; 2)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Select<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/p>\n<p>So, for example, to schedule a native backup, you could issue the following command line:<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0 &#8216;u:\\DBMaint\\BuildDailyBackupJob -S&#8221;MyServer\\Inst01&#8243; -PP -I15 -R3 -B2 _<br \/>\n -Tmyname@myorg.com<\/p>\n<p>The job will connect to server &#8220;MyServer\\Inst01&#8221;, 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.<\/p>\n<p>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:<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0 u:\\DBMaint\\BuildDailyBackupJob -S&#8221;MyServer&#8221; -PP -I30 -R5 -B5 -R3 -G1 -W&#8221;brian&#8221;<\/p>\n<p>-T&#8221;myname@myorg.com&#8221; -F&#8221;hisname@myorg.com&#8221; -E&#8221;SMTP.myorg.com&#8221; <\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;S&#8221;MyServer&#8221; use MyServer<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-PP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 treat it as a production server (p)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-I30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 number of minutes between log backups (30 here)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-R5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 number of days to retain the files before deleting them (5 here)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-B5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 backup start hour 5AM in this example<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-W&#8221;brian&#8221;\u00a0 the backup password (redgate only). Brian in this example<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-G1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Is this using Redgate&#8217;s SQL Backup (Yes in this example)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-T&#8221;who@Where&#8221;\u00a0\u00a0\u00a0\u00a0 To Email: who do we notify?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-F&#8221;From@Where&#8221;\u00a0\u00a0\u00a0 From Whom: who was the source?<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;-E&#8221;SMTP.WHO.COM&#8221;\u00a0 &#8216;what SMTP server? (SQL 2005 only)<\/p>\n<p>The job will connect to server &#8220;MyServer&#8221;, 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.<\/p>\n<p>The first thing we need to do is to connect to the server, which is handled in this code:<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0 Try<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Connect to the server<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dim srvMgmtServer As Server<br \/>\n\u00a0\u00a0\u00a0\u00a0 \u00a0srvMgmtServer = New Server(strServerName)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0 Dim srvConn As ServerConnection<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0 srvConn = srvMgmtServer.ConnectionContext<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0 srvConn.LoginSecure = True<\/p>\n<p>We&#8217;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.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tblServerJobs As DataTable<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim rowServerJobs As DataRow<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jobDumpJob As Job<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Drop the existing database_dump job<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tblServerJobs = srvMgmtServer.JobServer.EnumJobs<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each rowServerJobs In tblServerJobs.Rows<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If rowServerJobs(&#8220;Name&#8221;) = &#8220;DailyFullBackup&#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Or rowServerJobs(&#8220;Name&#8221;) = &#8220;TransLogBackup&#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Or rowServerJobs(&#8220;Name&#8221;) = &#8220;SystemFullBackup&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobDumpJob = srvMgmtServer.JobServer.Jobs(rowServerJobs(&#8220;Name&#8221;))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobDumpJob.Drop()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/p>\n<p>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&#8217;s left to do is execute the job creation subroutines, and build in some robust error handling:<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BuildDailyFullBackup(srvMgmtServer, intTLogInterval, intRetDays, _<\/p>\n<p class=\"MsoNormal\">intFullBkupStart, strProdServer, intRedgate, strBackupPassword, _<\/p>\n<p class=\"MsoNormal\">strNotificationEmail, strNotificationSource, strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; We only need Transaction Log backups on Production Servers<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If strProdServer = &#8220;P&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BuildDailyTlogBackup(srvMgmtServer, intTLogInterval, intRetDays,<\/p>\n<p class=\"MsoNormal\">intFullBkupStart, intRedgate, strBackupPassword, _<\/p>\n<p class=\"MsoNormal\">strNotificationEmail, strNotificationSource, strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BuildSystemFullBackup(srvMgmtServer, intRedgate, strBackupPassword, _<\/p>\n<p class=\"MsoNormal\">strNotificationEmail, strNotificationSource, strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch smoex As SmoException<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.WriteLine(&#8220;There has been an SMO error&#8221;)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Display the SMO exception message.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.WriteLine(smoex.Message)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Display the sequence of non-SMO exceptions that caused the SMO exception.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim ex As Exception<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0ex = smoex.InnerException<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Do While ex.InnerException IsNot (Nothing)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.WriteLine(ex.InnerException.Message)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ex = ex.InnerException<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Loop<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Catch other non-SMO exceptions.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch ex As Exception<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.WriteLine(&#8220;There has been a VB error. &#8221; + ex.Message)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Do While ex.InnerException IsNot (Nothing)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Console.WriteLine(ex.InnerException.Message)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ex = ex.InnerException<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Loop<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Try<br \/>\n\u00a0\u00a0\u00a0 End Sub<\/p>\n<h3>Failure Reporting<\/h3>\n<p>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&#8217;re running SQL Server 2000 or SQL Server 2005, so within our <b>BuildNotifyStep<\/b> function we create the appropriate commands based on the version to which we&#8217;re connected. This function will return the Transact-SQL command which will send the appropriate notification email, based on the step that failed.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0 Private Function BuildNotifyStep( _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strJobName As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intVersion As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strDBName As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationEmail As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationSource As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationSMTP As String) As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strCmd As String<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If intVersion = 9 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;declare @rc int, @subj varchar(255), _<br \/>\n @mesg varchar(255)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @subj = @@servername + _<br \/>\n &#8216; &#8211; &#8221; + strJobName + &#8221; Job Failure'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @mesg = &#8216;The &#8221; + strJobName + &#8221; job&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If strDBName &lt;&gt; &#8220;&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; for database &#8221; + strDBName<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; on &#8216; + @@servername + &#8216; failed at &#8216; + _<br \/>\n convert(varchar(25), getdate(), 100)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;exec @rc = master.dbo.xp_smtp_sendmail&#8221; + _<br \/>\n vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@FROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= N'&#8221; + strNotificationSource + _<br \/>\n &#8220;&#8216;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@FROM_NAME\u00a0= N&#8217;DB Admin&#8217;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@TO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= N'&#8221; + strNotificationEmail + _<br \/>\n &#8220;&#8216;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@priority\u00a0\u00a0= N&#8217;NORMAL&#8217;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@subject\u00a0\u00a0\u00a0= @subj,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@message\u00a0\u00a0\u00a0= @mesg,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= N&#8217;text\/plain&#8217;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0\u00a0@server \u00a0\u00a0\u00a0= N'&#8221; + strNotificationSMTP + _<br \/>\n &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;declare @rc int, @subj varchar(255), _<br \/>\n @mesg varchar(255)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @subj = @@servername + &#8216; &#8211; &#8221; + _<br \/>\n strJobName + &#8221; Job Failure'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @mesg = &#8216;The &#8221; + strJobName + &#8221; job&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If strDBName &lt;&gt; &#8220;&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; for database &#8221; + strDBName<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; on &#8216; + @@servername + &#8216; failed at &#8216;&#8221; + _<\/p>\n<p>&#8221; + convert(varchar(25), getdate(), 100)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;EXEC msdb.dbo.sp_send_dbmail&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0 @profile_name = &#8216;DBMail&#8217;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0 @recipients = &#8216;&#8221; + strNotificationEmail + _<br \/>\n &#8220;&#8216;,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221;\u00a0 @body = @mesg,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0strCmd = strCmd + &#8221;\u00a0 @subject = @subj&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BuildNotifyStep = strCmd<\/p>\n<p>\u00a0\u00a0\u00a0 End Function<\/p>\n<p>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.<\/p>\n<h3>The BuildDailyFullBackup Agent job<\/h3>\n<p>The first subroutine we&#8217;ll build is the <b>BuildDailyFullBackup<\/b> subroutine, which takes all the parameters we received from the command line.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0Sub BuildDailyFullBackup( _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByRef srvMgmtServer As Server, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intTLogInterval As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intRetDays As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intFullBkupStart As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strProdServer As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intRedgate As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strBackupPassword As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationEmail As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationSource As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationSMTP As String)<\/p>\n<p>We need to define variables for building the backup job and the various job steps used by the backup job:<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intStepID As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Identify the current StepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dbcDatabases As DatabaseCollection\u00a0 &#8216;The Server&#8217;s Database Collection<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Current Database Object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intVersion As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Server&#8217;s Version Number<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strJobName As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Job Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strBackupDir As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Server&#8217;s Backup Directory<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Transact-SQL command<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim i As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;An iterator<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strJobName = &#8220;DailyFullBackup&#8221;<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jobUserFullBkupJob As Job\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Job object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsUserFullBkupJobStep As JobStep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Job Step object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsUserFullBkupFailStep As JobStep\u00a0&#8216;The Job Step object for the failure step<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbschUserFullBkupJobSched As JobSchedule\u00a0\u00a0 &#8216;The Job Schedule object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strUserFullBkupFailCmd As String\u00a0\u00a0\u00a0\u00a0&#8216;The command string for the failure step<\/p>\n<p>Next, we get the collection of server databases, and set the default initial fields for the server to include the &#8220;<b>IsSystemObject<\/b>&#8221; property, which speeds up the processing of the program significantly. We&#8217;ll also get the version number of the server we&#8217;re connected to, and store the backup directory for the server.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbcDatabases = srvMgmtServer.Databases<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srvMgmtServer.SetDefaultInitFields(GetType(Database), &#8220;IsSystemObject&#8221;)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Determine the SQL Server Version<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strBackupDir = srvMgmtServer.Settings.BackupDirectory<\/p>\n<p>We can now start to create the first job. We&#8217;ll instantiate the Job object, using the server&#8217;s <b>JobServer<\/b> object and the job name as properties, then set the description, category, and owner, and create the job. We&#8217;ll also initialize the iterator for later use.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8216;Create the Daily Full Backup Job<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob = New Job(srvMgmtServer.JobServer, strJobName)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.Description = &#8220;Daily Full Backup&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.Category = &#8220;[Uncategorized (Local)]&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.OwnerLoginName = &#8220;sa&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i = 0<\/p>\n<p>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.<\/p>\n<p>The <b>sp_delete_backuphistory<\/b> command works for both SQL 2000 and SQL 2005. For purging the files, SQL 2000 uses the xp_sqlmaint function we&#8217;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 <b>@oldest_date<\/b> parameter to the sp_purge_jobhistory stored procedure so we can use that to delete job history older than one month.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Cleanup history and backup files<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dtfiles datetime, @dthist datetime&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dthist = dateadd(m, -1, getdate())&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dtfiles = dateadd(d, -&#8221; + Trim(CStr(intRetDays)) + _<\/p>\n<p class=\"MsoNormal\">&#8220;, getdate())&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Cleanup History&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;exec msdb.dbo.sp_delete_backuphistory @dthist&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If intRedgate = 0 And intVersion = 9 Then\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n<p class=\"MsoNormal\">&#8216; In SQL 2000 sqlmaint purges the files, in SQL 2005 we use xp_delete_file<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf &#8216;redGate backups do their own purging<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Cleanup Maintenance&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;exec msdb.dbo.sp_purge_jobhistory\u00a0 @oldest_date=@dthist&#8221; _<\/p>\n<p class=\"MsoNormal\">+ vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;exec master.dbo.xp_delete_file 0,N'&#8221; + strBackupDir + _<\/p>\n<p class=\"MsoNormal\">&#8220;&#8216;,N&#8217;bak&#8217;,@dtfiles&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>We&#8217;ve built the command for the step, now we need to add the step to the job.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep = New JobStep(jobUserFullBkupJob, &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.Command = strCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnSuccessAction = StepCompletionAction.GoToStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnSuccessStep = i + 2<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnFailAction = StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intStepID = jbsUserFullBkupJobStep.ID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.StartStepID = intStepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.Alter()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>Notice that on success we want to skip the next step, because that&#8217;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&#8217;s starting step ID to this step.<\/p>\n<p>Now we need to build the failure notification step. We load the database name with the word &#8220;Cleanup&#8221; 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.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strUserFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, &#8220;Cleanup&#8221;, _<\/p>\n<p class=\"MsoNormal\">strNotificationEmail, strNotificationSource,<br \/>\n _strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep = New JobStep(jobUserFullBkupJob, &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.Command = strUserFullBkupFailCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.OnFailAction = StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.Create()<\/p>\n<p>We now want to iterate through the collection of databases on the server. For this job we only want user databases, and we don&#8217;t want snapshot databases.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each dbDatabase In dbcDatabases<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0Dim bolProcessDB As Boolean<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strDBName As String<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bolProcessDB = True<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.IsSystemObject = True Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bolProcessDB = False<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.CompatibilityLevel = CompatibilityLevel.Version90 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.IsDatabaseSnapshot Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bolProcessDB = False<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strDBName = dbDatabase.Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If bolProcessDB = True Then<\/p>\n<p>Now we get to the logic for each database. First we&#8217;ll build the command for the step, based on which version of SQL Server we&#8217;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&#8217;ll first construct a string containing the date in <b>YYYYMMDDHHMMSS<\/b> format and use that as part of the backup file name. We then backup the database to that disk file, then do a <b>RESTORE VERIFYONLY<\/b> to make certain that the backup file is valid.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">i += 1<br \/>\n strCmd = &#8220;&#8221; + vbCrLf<br \/>\n strCmd = strCmd + &#8220;&#8211;Backup Database &#8221; + strDBName + _<br \/>\n &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n If intRedgate &lt;&gt; 0 Then<br \/>\n strCmd = strCmd + &#8220;declare @strbackup varchar(500),\u00a0 &#8221; _<br \/>\n + &#8220;@strDate varchar(30),@database varchar(50)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dt datetime, _<br \/>\n @command varchar (2000)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @backupSetId as int&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dt = getdate()&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = _<br \/>\n CONVERT(varchar, @dt , 112)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + &#8221; _<br \/>\n + &#8220;Left(CONVERT(varchar, @dt , 108),2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + &#8221; _<br \/>\n + &#8220;SubString(CONVERT(varchar, @dt , 108),4,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + &#8221; _<br \/>\n + &#8220;SubString(CONVERT(varchar, @dt , 108),7,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Transaction Log &#8221; + strDBName _<br \/>\n + &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @database = &#8216;&#8221; + strDBName + &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strbackup = &#8216;&#8221; + strBackupDir _<br \/>\n + &#8220;&#8216; + &#8216;\\FULL_&#8217;+@@ServerName+&#8217;_&#8217; + @database _<br \/>\n + &#8216;_&#8217; + @strDate + &#8216;.sqb'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup System Database &#8221; + strDBName _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;Select @command= _&#8217;-SQL &#8220;&#8221;BACKUP DATABASE [&#8221; _<br \/>\n + strDBName _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + &#8220;] TO DISK = &#8221;&#8217;+ @strBackup + &#8221;&#8217; WITH &#8221; _<\/p>\n<p class=\"MsoNormal\">+ IIf(strBackupPassword &lt;&gt; &#8220;&#8221;, &#8220;PASSWORD = &#8221;&#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+ strBackupPassword + &#8220;&#8221;,&#8221;, &#8220;&#8221;) _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + &#8221; ERASEFILES=14, &#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;INIT, VERIFY, NAME = &#8221;Database (&#8221; _<br \/>\n + strDBName _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + &#8220;), &#8216; + CONVERT(varchar, @dt , 113) + &#8221;&#8217; &#8221; _<br \/>\n + &#8221; , DESCRIPTION = &#8221;Backup on &#8216; _<br \/>\n + CONVERT(varchar, @dt , 113) &#8221; _<br \/>\n + &#8221; + &#8216;\u00a0 Database: &#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + strDBName + &#8221;\u00a0 Instance: (local)&#8221;&#8221; _<br \/>\n + &#8221; &#8220;&#8221; -E &#8216; &#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;Execute master..sqlbackup @command&#8221;<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0Else<\/p>\n<p class=\"MsoNormal\">&#8216; In SQL Server 2000 use the xp_sqlmaint procedure<\/p>\n<p class=\"MsoNormal\">If intVersion = 8 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Full Backups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;EXECUTE master.dbo.xp_sqlmaint N&#8217;-D &#8221; _<br \/>\n + strDBName + &#8221;\u00a0 -VrfyBackup&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; -BkUpMedia DISK -BkUpDB\u00a0 _<br \/>\n-UseDefDir\u00a0 -DelBkUps &#8220;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + Trim(CStr(intRetDays)) _<br \/>\n + &#8220;DAYS -BkExt &#8220;&#8221;BAK&#8221;&#8221;&#8216;&#8221; + vbCrLf<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Full Backups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @strbackup varchar(500),\u00a0 _<\/p>\n<p class=\"MsoNormal\">@strDate varchar(30), &#8221; _<br \/>\n + &#8221;\u00a0 @database varchar(50)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dt datetime&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @backupSetId as int&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dt = getdate()&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = CONVERT(varchar, @dt , 112)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + &#8221; _<br \/>\n + &#8220;Left(CONVERT(varchar, @dt , 108),2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + &#8221; _<br \/>\n + &#8221; SubString(CONVERT(varchar, @dt , 108),4,2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + &#8221; _<br \/>\n + &#8220;SubString(CONVERT(varchar, @dt , 108),7,2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @database = &#8216;&#8221; + strDBName + &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strbackup = &#8216;&#8221; + strBackupDir + &#8220;&#8216; + &#8216;\\&#8217; _<br \/>\n + @database + &#8216;_backup_&#8217; + @strDate + &#8216;.bak'&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;BACKUP DATABASE [&#8221; + strDBName + &#8220;] _<br \/>\n TO\u00a0 DISK = @strbackup WITH NOFORMAT, _<br \/>\n INIT,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;NAME = N'&#8221; + strDBName _<br \/>\n + &#8220;-Full Database Backup&#8217;, SKIP, NOREWIND, NOUNLOAD, _<br \/>\n STATS = 10&#8243; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @backupSetId = _<br \/>\n position from msdb..backupset&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;where database_name=N'&#8221; + strDBName + &#8220;&#8216;&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strCmd = strCmd + &#8220;and backup_set_id=(select max(backup_set_id) _<br \/>\n from msdb..backupset _<br \/>\n where database_name=N'&#8221; + strDBName _<br \/>\n + &#8220;&#8216; )&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;if @backupSetId is null begin raiserror _<br \/>\n (N&#8217;Verify failed. Backup information for _<br \/>\n database &#8221;&#8221; _<br \/>\n + strDBName + &#8220;&#8221; not found.&#8217;, 16, 1) end&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;RESTORE VERIFYONLY FROM\u00a0 DISK = _<br \/>\n @strbackup WITH\u00a0 FILE = @backupSetId, _<br \/>\n NOUNLOAD,\u00a0 NOREWIND&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>The command now built, we&#8217;ll construct the step and add it to the job.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep = New JobStep(jobUserFullBkupJob, &#8220;Step &#8221; _<br \/>\n + CStr(i) + &#8221; Full Backup of &#8221; + strDBName)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.DatabaseName = dbDatabase.Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.Command = strCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnSuccessAction = StepCompletionAction.GoToStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnSuccessStep = i + 2<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnFailAction = StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intStepID = jbsUserFullBkupJobStep.ID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)<br \/>\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserFullBkupJob.StartStepID = intStepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserFullBkupJob.Alter()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>We then add the failure notification step.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">strUserFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _<br \/>\n dbDatabase.Name, strNotificationEmail, _<br \/>\n strNotificationSource, strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep = New JobStep(jobUserFullBkupJob, _<br \/>\n &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.Command = strUserFullBkupFailCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupFailStep.Create()<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/p>\n<p>Now we&#8217;ve cycled through all the databases, we need a dummy step so the final database has a step to go to when it&#8217;s successful, so I added this step.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep = New JobStep(jobUserFullBkupJob, &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.Command = &#8220;select 1&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserFullBkupJobStep.Create()<\/p>\n<p>Now we can schedule the job. We want the job to run daily, so we set the job schedule&#8217;s <b>FrequencyTypes<\/b> property to Daily and the <b>FrequencySubDayTypes<\/b> to Once. We set the <b>ActiveStartTimeOfDay<\/b> to a <b>TimeSpan <\/b>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 <b>FrequencyInterval<\/b> to 1 and set the <b>ActiveStartDate<\/b> to Today. Then we can create the schedule.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Define a JobSchedule object variable by supplying the parent job<br \/>\n &#8216;and name arguments in the constructor. <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched = _<br \/>\n New JobSchedule(jobUserFullBkupJob, &#8220;Sched 01&#8221;)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Set properties to define the schedule frequency, and duration.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.FrequencyTypes = FrequencyTypes.Daily<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.FrequencySubDayTypes = _<br \/>\n FrequencySubDayTypes.Once<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsUserFullBkupStart As TimeSpan<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tsUserFullBkupStart = New TimeSpan(intFullBkupStart, 0, 0)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.ActiveStartTimeOfDay = _<br \/>\n tsUserFullBkupStart<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsUserFullBkupEnd As TimeSpan<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tsUserFullBkupEnd = New TimeSpan(23, 59, 59)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.ActiveEndTimeOfDay = _<br \/>\n tsUserFullBkupEnd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.FrequencyInterval = 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dtUserFullBkupStartDate As Date = Date.Today<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.ActiveStartDate = _<br \/>\n dtUserFullBkupStartDate<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Create the job schedule on the instance of SQL Agent.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserFullBkupJobSched.Create()<br \/>\n\u00a0\u00a0\u00a0 End Sub<\/p>\n<p>We&#8217;ve now created the job that will do a full backup of every user database once a day at the desired hour.<\/p>\n<h3>Transaction Log backup Job<\/h3>\n<p>The next subroutine we&#8217;ll build will create the transaction log backup job. We&#8217;ll run this conditionally, because we set the Dev and QA databases to Simple Recovery mode, so we don&#8217;t need log backups. In the Main() subroutine the call looks like this:<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0 &#8216; We only need Transaction Log backups on Production Servers<br \/>\n\u00a0\u00a0\u00a0 &#8216;\u00a0\u00a0 If strProdServer = &#8220;P&#8221; Then<br \/>\n\u00a0\u00a0\u00a0 &#8216;\u00a0\u00a0\u00a0\u00a0\u00a0 BuildDailyTlogBackup(srvMgmtServer, intTLogInterval, _<br \/>\n\u00a0\u00a0\u00a0 &#8216;\u00a0\u00a0 intRetDays, intFullBkupStart)<br \/>\n\u00a0\u00a0\u00a0 &#8216;\u00a0\u00a0 End If<\/p>\n<p>The structure of the subroutine is very similar to the full backup subroutine. First we define the necessary variables.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0 Sub BuildDailyTlogBackup( _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByRef srvMgmtServer As Server, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intTLogInterval As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intRetDays As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intFullBkupStart As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal intRedgate As Integer, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strBackupPassword As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationEmail As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationSource As String, _<br \/>\n\u00a0\u00a0\u00a0\u00a0 ByVal strNotificationSMTP As String)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intStepID As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Identify the current StepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dbcDatabases As DatabaseCollection\u00a0 &#8216;The Server&#8217;s Database Collection<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Current Database Object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intVersion As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Server&#8217;s Version Number<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strJobName As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Job Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strBackupDir As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Server&#8217;s Backup Directory<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Transact-SQL command<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim i As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;An iterator<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strJobName = &#8220;TransLogBackup<\/p>\n<p class=\"MsoNormal\">&#8216; Add 1 hour to the start of the full backups for the<br \/>\n &#8216; start of the tlog backups.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intFullBkupStart += 1\u00a0\u00a0<br \/>\n &#8216;The Job object<\/p>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jobUserTlogBkupJob As Job\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <br \/>\n &#8216;The Job Step object<\/p>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsUserTlogBkupJobStep As JobStep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <br \/>\n &#8216;The Job Step object for the failure step<\/p>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsUserTlogBkupFailStep As JobStep<br \/>\n &#8216;The Job Schedule object<\/p>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbschUserTlogBkupJobSched As JobSchedule\u00a0<br \/>\n &#8216;The command string for the failure step<\/p>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strUserTlogBkupFailCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n<p>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&#8217;s generally not a lot of transaction activity at this time any way.<\/p>\n<p>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&#8217;ll also create the new job.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbcDatabases = srvMgmtServer.Databases<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srvMgmtServer.SetDefaultInitFields(GetType(Database), &#8220;IsSystemObject&#8221;)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Determine the SQL Server Version<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strBackupDir = srvMgmtServer.Settings.BackupDirectory<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Create the Trans Log Backup Job<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob = New Job(srvMgmtServer.JobServer, strJobName)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.Description = &#8220;Trans Log Backup&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.Category = &#8220;[Uncategorized (Local)]&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.OwnerLoginName = &#8220;sa&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;strJobID = jobUserTlogBkupJob.JobID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i = 0<\/p>\n<p>In SQL 2000 the <b>sqlmaint<\/b> function purges the transaction log backup files, but in SQL 2005 we use the <b>xp_delete_file<\/b> 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.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If intVersion = 9 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Cleanup backup files<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dtfiles datetime&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dtfiles = dateadd(d, -&#8221; _<\/p>\n<p class=\"MsoNormal\">+ Trim(CStr(intRetDays)) _<br \/>\n + &#8220;, getdate())&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Cleanup Maintenance&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;exec master.dbo.xp_delete_file 0,N'&#8221; _<br \/>\n + strBackupDir + &#8220;&#8216;,N&#8217;trn&#8217;,@dtfiles&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep = New JobStep(jobUserTlogBkupJob, &#8220;Step &#8221; _<br \/>\n + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.Command = strCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnSuccessStep = i + 2<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intStepID = jbsUserTlogBkupJobStep.ID<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.ApplyToTargetServer(srvMgmtServer.Name)<br \/>\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserTlogBkupJob.StartStepID = intStepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.Alter()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strUserTlogBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _<\/p>\n<p class=\"MsoNormal\">&#8220;Cleanup&#8221;, strNotificationEmail, _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSource, strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep = New JobStep(jobUserTlogBkupJob, _<br \/>\n &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.Command = strUserTlogBkupFailCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>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.<\/p>\n<pre>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each dbDatabase In dbcDatabases\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim bolProcessDB As Boolean\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bolProcessDB = True\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.IsSystemObject = True Then\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bolProcessDB = False\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.CompatibilityLevel = CompatibilityLevel.Version90 Then\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.IsDatabaseSnapshot Then\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bolProcessDB = False\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If bolProcessDB = True Then<\/pre>\n<p>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.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strDBName As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strDBName = dbDatabase.Name<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Database &#8221; _<br \/>\n + strDBName + &#8221; w\/Verify&#8221; + vbCrLf<\/p>\n<p>We&#8217;ll only back up the transaction log if the recovery model is set to Full (I don&#8217;t have any databases set to Bulk Logged). If the server is SQL 2000 we&#8217;ll build a call to xp_sqlmaint, but if it&#8217;s SQL 2005 we&#8217;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.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Trans Log Backups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Full<br \/>\n Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If intRedgate &lt;&gt; 0 Then &#8221;\u00a0 <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @strbackup varchar(500), _<br \/>\n @strDate varchar(30),\u00a0 @database varchar(50)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dt datetime, _<br \/>\n @command varchar (2000)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @backupSetId as int&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dt = getdate()&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strCmd = strCmd + &#8220;set @strDate = _<br \/>\n CONVERT(varchar, @dt , 112)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + Left(CONVERT(varchar, @dt , 108),2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + SubString(CONVERT(varchar, @dt , 108),4,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + SubString(CONVERT(varchar, @dt , 108),7,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strCmd = strCmd + &#8220;&#8211;Backup Transaction Log &#8221; _<br \/>\n + strDBName + &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @database = &#8216;&#8221; + strDBName _<br \/>\n + &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strbackup = &#8216;&#8221; + strBackupDir _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + &#8220;&#8216; + &#8216;\\LOG_&#8217;+@@ServerName+&#8217;_&#8217; + @database _<br \/>\n + &#8216;_&#8217; + @strDate + &#8216;.sqb'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Transaction Log &#8221; _<br \/>\n + strDBName + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;Select @command= &#8216;-SQL &#8220;&#8221;BACKUP LOG _<br \/>\n [&#8221; + strDBName + &#8220;] TO DISK = &#8221;&#8217; _<br \/>\n + @strBackup + &#8221;&#8217; WITH &#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + IIf(strBackupPassword &lt;&gt; &#8220;&#8221;, &#8220;PASSWORD = &#8221;&#8221; _<br \/>\n + strBackupPassword + &#8220;&#8221;,&#8221;, &#8220;&#8221;) _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0+ &#8221; ERASEFILES=&#8221; + Trim(CStr(intRetDays)) _<br \/>\n + &#8220;, &#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;INIT, VERIFY, NAME = _<br \/>\n &#8221;Database (&#8221; + strDBName + &#8220;), &#8216; _<br \/>\n + CONVERT(varchar, @dt , 113) + &#8221;&#8217;, _<br \/>\n DESCRIPTION = &#8221;Backup on &#8216; _<br \/>\n + CONVERT (varchar, @dt , 113) _<br \/>\n + &#8216;\u00a0 Database: &#8221; + strDBName + _<br \/>\n &#8221;\u00a0 Instance: (local)&#8221;&#8221; _<br \/>\n + &#8221; &#8220;&#8221; -E &#8216; &#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;Execute master..sqlbackup @command&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ElseIf intVersion = 8 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Transaction Log &#8221; + strDBName _<br \/>\n + &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;EXECUTE master.dbo.xp_sqlmaint N&#8217;-D &#8221; _<br \/>\n + strDBName + &#8221;\u00a0 -VrfyBackup&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; -BkUpMedia DISK -BkUpLog _<br \/>\n -UseDefDir\u00a0 -DelBkUps &#8220;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + Trim(CStr(intRetDays)) _<br \/>\n + &#8220;DAYS -BkExt &#8220;&#8221;TRN&#8221;&#8221;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strCmd = strCmd + &#8220;declare @strbackup varchar(500),\u00a0_<br \/>\n@strDate varchar(30),\u00a0 _<br \/>\n@database varchar(50)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dt datetime, @dtfiles datetime, _<br \/>\n @dthist datetime, @sysfiles datetime&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @backupSetId as int&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dt = getdate()&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = _<\/p>\n<p class=\"MsoNormal\">CONVERT(varchar, @dt , 112)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + _<br \/>\n Left(CONVERT(varchar, @dt , 108),2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + _<br \/>\n SubString(CONVERT(varchar, @dt , 108),4,2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate + _<br \/>\n SubString(CONVERT(varchar, @dt , 108),7,2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Transaction Log &#8221; + _<br \/>\n strDBName + &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @database = &#8216;&#8221; + strDBName + &#8220;&#8216;&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strbackup = &#8216;&#8221; + strBackupDir _<br \/>\n + &#8220;&#8216; + &#8216;\\&#8217; + @database + &#8216;_tlog_&#8217; _<br \/>\n + @strDate + &#8216;.trn'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;BACKUP LOG [&#8221; + strDBName + &#8220;]<br \/>\n TO\u00a0 DISK = @strbackup WITH NOFORMAT, _<br \/>\n NOINIT,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;NAME = N'&#8221; + strDBName _<br \/>\n + &#8220;-Transaction Log Backup&#8217;, SKIP, REWIND, _<br \/>\n NOUNLOAD,\u00a0 STATS = 10&#8243; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @backupSetId = position _<br \/>\n from msdb..backupset&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;where database_name=N'&#8221; + strDBName _<br \/>\n+ &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;and backup_set_id=(select max(backup_set_id)<br \/>\n from msdb..backupset where<br \/>\n database_name=N'&#8221; + strDBName + &#8220;&#8216; )&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;if @backupSetId is null<br \/>\n begin raiserror(N&#8217;Verify failed. Backup<br \/>\n information for database &#8221;&#8221; + strDBName _<br \/>\n + &#8220;&#8221; not found.&#8217;, 16, 1) end&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;RESTORE VERIFYONLY FROM\u00a0 DISK = _<br \/>\n @strbackup WITH\u00a0 FILE = @backupSetId, _<br \/>\n NOUNLOAD,\u00a0 NOREWIND&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>Now we can create the job step using the command we just built, set the job&#8217;s first step if necessary, and build the error notification step to report any problems that occur during the backup.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep = New JobStep(jobUserTlogBkupJob, &#8220;Step &#8221; _<br \/>\n + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.DatabaseName = dbDatabase.Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.Command = strCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnSuccessAction = StepCompletionAction.GoToStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnSuccessStep = i + 2<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnFailAction = StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intStepID = jbsUserTlogBkupJobStep.ID<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.ApplyToTargetServer(srvMgmtServer.Name)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.StartStepID = intStepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobUserTlogBkupJob.Alter()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strUserTlogBkupFailCmd = BuildNotifyStep(strJobName, _<br \/>\n intVersion, dbDatabase.Name, _<br \/>\nstrNotificationEmail, _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSource, _<br \/>\n strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep = New JobStep(jobUserTlogBkupJob, _<br \/>\n &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.Command = strUserTlogBkupFailCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupFailStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/p>\n<p>Just like for the full backup job, we need a dummy step for the last backup to jump to if successful.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep = New JobStep(jobUserTlogBkupJob, &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.Command = &#8220;select 1&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsUserTlogBkupJobStep.Create()<\/p>\n<p>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.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Define a JobSchedule object variable by supplying the parent job<\/p>\n<p>&#8216;and name arguments in the constructor.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched = New JobSchedule(jobUserTlogBkupJob, &#8220;Sched 01&#8221;)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Set properties to define the schedule frequency, and duration.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.FrequencyTypes = FrequencyTypes.Daily<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsUserTlogBkupStart As TimeSpan<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tsUserTlogBkupStart = New TimeSpan(intFullBkupStart, 0, 0)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.ActiveStartTimeOfDay = tsUserTlogBkupStart<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsUserTlogBkupEnd As TimeSpan<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tsUserTlogBkupEnd = New TimeSpan(23, 59, 59)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.ActiveEndTimeOfDay = tsUserTlogBkupEnd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Minute<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.FrequencySubDayInterval = intTLogInterval<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.FrequencyTypes = FrequencyTypes.Daily<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.FrequencyInterval = 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dtUserTlogBkupStartDate As Date = Date.Today<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.ActiveStartDate = dtUserTlogBkupStartDate<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Create the job schedule on the instance of SQL Agent.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserTlogBkupJobSched.Create()<\/p>\n<p>\u00a0\u00a0\u00a0 End Sub<\/p>\n<h3>The System database backup job<\/h3>\n<p>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&#8217;t change frequently enough to justify running the backups more regularly. Your mileage may vary.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0 Sub BuildSystemFullBackup(ByRef srvMgmtServer As Server, _<br \/>\n\u00a0\u00a0\u00a0 ByVal intRedgate As Integer, _<br \/>\n\u00a0\u00a0\u00a0 ByVal strBackupPassword As String, _<br \/>\n\u00a0\u00a0\u00a0 ByVal strNotificationEmail As String, _<br \/>\n\u00a0\u00a0\u00a0 ByVal strNotificationSource As String, _<br \/>\n\u00a0\u00a0\u00a0 ByVal strNotificationSMTP As String)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intStepID As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Identify the current StepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dbcDatabases As DatabaseCollection\u00a0 &#8216;The Server&#8217;s Database Collection<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Current Database Object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim intVersion As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Server&#8217;s Version Number<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strJobName As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Job Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strBackupDir As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Server&#8217;s Backup Directory<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Transact-SQL command<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim i As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;An iterator<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strJobName = &#8220;SystemFullBackup&#8221;<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jobSystemFullBkupJob As Job\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The Job object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsSystemFullBkupJobStep As JobStep\u00a0\u00a0 &#8216;The Job Step object<\/p>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsSystemFullBkupFailStep As JobStep\u00a0 &#8216;The Job Step object for <br \/>\n &#8216;the failure step<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbschSystemFullBkupJobSched As JobSchedule\u00a0&#8216;The Job Schedule object<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strSystemFullBkupFailCmd As String\u00a0\u00a0\u00a0\u00a0&#8216;The command string for<br \/>\n &#8216;the failure step<\/p>\n<p>We grab the databases collection, set the default init fields, get the version and backup directories, like before, and create the job.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbcDatabases = srvMgmtServer.Databases<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srvMgmtServer.SetDefaultInitFields(GetType(Database), &#8220;IsSystemObject&#8221;)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Determine the SQL Server Version<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strBackupDir = srvMgmtServer.Settings.BackupDirectory<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Create the Trans Log Backup Job<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob = New Job(srvMgmtServer.JobServer, strJobName)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.Description = &#8220;System Full Backup&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.Category = &#8220;[Uncategorized (Local)]&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.OwnerLoginName = &#8220;sa&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i = 0<\/p>\n<p>We need to clean up the old backup files if we&#8217;re running SQL 2005. Also, note that for SQL 2005 servers I created a subfolder called &#8216;System&#8217; in the server&#8217;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.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If intVersion = 9 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Cleanup backup files<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @sysfiles datetime&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @sysfiles = dateadd(d, -14, getdate())&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Cleanup Maintenance&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;exec master.dbo.xp_delete_file 0,N'&#8221; _<br \/>\n + strBackupDir + &#8220;\\System\\&#8217;,N&#8217;bak&#8217;,@sysfiles&#8221; _<br \/>\n + vbCrLf<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep = New JobStep(jobSystemFullBkupJob, &#8220;Step &#8221; _<br \/>\n + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.Command = strCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnSuccessStep = i + 2<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intStepID = jbsSystemFullBkupJobStep.ID<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0jobSystemFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.StartStepID = intStepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.Alter()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strSystemFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _<br \/>\n &#8220;Cleanup&#8221;, strNotificationEmail, _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strNotificationSource, strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep = New JobStep(jobSystemFullBkupJob, &#8220;Step &#8221; _<br \/>\n + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.Command = strSystemFullBkupFailCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>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&#8217;t want to back up tempdb.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each dbDatabase In dbcDatabases<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If dbDatabase.IsSystemObject = True Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim strDBName As String<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strDBName = dbDatabase.Name<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If strDBName &lt;&gt; &#8220;tempdb&#8221; Then<br \/>\n&#8230;<\/p>\n<p>Now, within the loop we build the command to back up the system databases, using either <b>xp_sqlmaint<\/b> or building the date string and file name, and building the backup and verify Transact-SQL statements.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Database &#8221; + strDBName _<br \/>\n + &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If intRedgate &lt;&gt; 0 Then &#8221;\u00a0 <br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @strbackup varchar(500), _<br \/>\n @strDate varchar(30),\u00a0 @database varchar(50)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dt datetime, _<\/p>\n<p class=\"MsoNormal\">@command varchar (2000)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @backupSetId as int&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dt = getdate()&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = _<br \/>\n CONVERT(varchar, @dt , 112)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + Left(CONVERT(varchar, @dt , 108),2)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + SubString(CONVERT(varchar, @dt , 108),4,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + SubString(CONVERT(varchar, @dt , 108),7,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup Transaction Log &#8221; _<br \/>\n + strDBName _<br \/>\n + &#8221; w\/Verify&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @database = &#8216;&#8221; _<br \/>\n + strDBName + &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strbackup = &#8216;&#8221; _<br \/>\n + strBackupDir + &#8220;&#8216; _<br \/>\n + &#8216;\\FULL_&#8217;+@@ServerName+&#8217;_&#8217; _<br \/>\n + @database + &#8216;_&#8217; + @strDate _<br \/>\n + &#8216;.sqb'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8211;Backup System Database &#8221; _<br \/>\n + strDBName + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;Select @command= _<br \/>\n &#8216;-SQL &#8220;&#8221;BACKUP DATABASE [&#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + strDBName + &#8220;] TO DISK = &#8221;&#8217; _<br \/>\n + @strBackup + &#8221;&#8217; WITH &#8221; _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + IIf(strBackupPassword &lt;&gt; &#8220;&#8221;, _<br \/>\n &#8220;PASSWORD = &#8221;&#8221; _<br \/>\n + strBackupPassword _<br \/>\n + &#8220;&#8221;,&#8221;, &#8220;&#8221;) _<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + &#8221; ERASEFILES=14, &#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;INIT, VERIFY, NAME = _<br \/>\n &#8221;Database (&#8221; + strDBName + &#8220;), &#8216; _<br \/>\n + CONVERT(varchar, @dt , 113) _<br \/>\n + &#8221;&#8217;, DESCRIPTION = &#8221;Backup on &#8216; _<br \/>\n + CONVERT(varchar, @dt , 113) _<br \/>\n + &#8216;\u00a0 Database: &#8221; + strDBName _<br \/>\n + &#8221;\u00a0 Instance: (local)&#8221;&#8221; _<br \/>\n + &#8221; &#8220;&#8221; -E &#8216; &#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;Execute master..sqlbackup @command&#8221;<br \/>\n &#8216; In SQL Server 2000 use the xp_sqlmaint procedure to<br \/>\n &#8216; backup the databases and delete old backup files<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ElseIf intVersion = 8 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Full Backups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;EXECUTE master.dbo.xp_sqlmaint N&#8217;-D &#8221; _<br \/>\n + strDBName + &#8221;\u00a0 -VrfyBackup&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8221; -BkUpMedia DISK -BkUpDB _<br \/>\n -UseDefDir\u00a0 -DelBkUps &#8220;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;14DAYS -BkExt &#8220;&#8221;BAK&#8221;&#8221;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Full Backups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @strbackup varchar(500),\u00a0_<br \/>\n@strDate varchar(30),\u00a0 _<br \/>\n@database varchar(50)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @dt datetime&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;declare @backupSetId as int&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;select @dt = getdate()&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = _<br \/>\n CONVERT(varchar, @dt , 112)&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + Left(CONVERT(varchar, @dt , 108),2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + SubString(CONVERT(varchar, @dt , 108),4,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strDate = @strDate _<br \/>\n + SubString(CONVERT(varchar, @dt , 108),7,2)&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @database = &#8216;&#8221; + strDBName _<br \/>\n + &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;set @strbackup = &#8216;&#8221; + strBackupDir _<br \/>\n + &#8220;&#8216; + &#8216;\\System\\&#8217; + @database + &#8216;_backup_&#8217; _<br \/>\n + @strDate + &#8216;.bak'&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;BACKUP DATABASE [&#8221; + strDBName + &#8220;] _<br \/>\n TO\u00a0 DISK = _<br \/>\n @strbackup WITH NOFORMAT, INIT,&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;NAME = N'&#8221; + strDBName _<br \/>\n + &#8220;-Full Database Backup&#8217;, SKIP, NOREWIND, _<br \/>\n NOUNLOAD, STATS = 10&#8243; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + _<br \/>\n &#8220;select @backupSetId = position _<\/p>\n<p class=\"MsoNormal\">from msdb..backupset&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + &#8220;where database_name=N'&#8221; + strDBName _<br \/>\n + &#8220;&#8216;&#8221; + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + _<br \/>\n &#8220;and backup_set_id=(select max(backup_set_id) _<br \/>\n from msdb..backupset where _<br \/>\n database_name=N'&#8221; + strDBName + &#8220;&#8216; )&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + _<br \/>\n &#8220;if @backupSetId is null begin raiserror _<br \/>\n (N&#8217;Verify failed. Backup information _<br \/>\n for database &#8221;&#8221; _<br \/>\n + strDBName + &#8220;&#8221; not found.&#8217;, 16, 1) end&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strCmd = strCmd + _<br \/>\n &#8220;RESTORE VERIFYONLY FROM\u00a0 DISK = _<br \/>\n @strbackup WITH\u00a0 FILE = _<br \/>\n @backupSetId, NOUNLOAD,\u00a0 NOREWIND&#8221; _<br \/>\n + vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>Once the command is built we build the step to use the command, and build the error notification step for the database.<\/p>\n<pre><\/pre>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep = New JobStep(jobSystemFullBkupJob, _<br \/>\n &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.DatabaseName = dbDatabase.Name<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.Command = strCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnSuccessStep = i + 2<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 intStepID = jbsSystemFullBkupJobStep.ID<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.ApplyToTargetServer(srvMgmtServer.Name)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.StartStepID = intStepID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobSystemFullBkupJob.Alter()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strSystemFullBkupFailCmd = BuildNotifyStep(strJobName, intVersion, _<br \/>\n dbDatabase.Name, strNotificationEmail, strNotificationSource, _<br \/>\n strNotificationSMTP)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep = New JobStep(jobSystemFullBkupJob, _<br \/>\n &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.Command = strSystemFullBkupFailCmd<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.OnSuccessAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.OnFailAction = _<br \/>\n StepCompletionAction.GoToNextStep<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupFailStep.Create()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/p>\n<p>Again, once we&#8217;re done with all the databases in the loop, we build the dummy step, as in the other subroutines.<\/p>\n<pre><\/pre>\n<p class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i += 1<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep = New JobStep(jobSystemFullBkupJob, _<br \/>\n &#8220;Step &#8221; + CStr(i))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.DatabaseName = &#8220;master&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.Command = &#8220;select 1&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnSuccessAction = _<br \/>\n StepCompletionAction.QuitWithSuccess<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.OnFailAction = <br \/>\n StepCompletionAction.QuitWithSuccess<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSystemFullBkupJobStep.Create()<\/p>\n<p>Last, we define the job schedule, specifying that the job is run weekly, starting at 2am on Sunday.<\/p>\n<pre class=\"MsoNormal\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Define a JobSchedule object variable by supplying the parent job        ' and name arguments in the constructor. \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched = New JobSchedule(jobSystemFullBkupJob, \"Sched 01\")\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Set properties to define the schedule frequency, and duration.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.FrequencyTypes = FrequencyTypes.Weekly\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.FrequencyRecurrenceFactor = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsSystemFullBkupStart As TimeSpan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tsSystemFullBkupStart = New TimeSpan(2, 0, 0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.ActiveStartTimeOfDay = tsSystemFullBkupStart\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsSystemFullBkupEnd As TimeSpan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tsSystemFullBkupEnd = New TimeSpan(23, 59, 59)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.ActiveEndTimeOfDay = tsSystemFullBkupEnd\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.FrequencyInterval = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dtSystemFullBkupStartDate As Date = Date.Today\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.ActiveStartDate = dtSystemFullBkupStartDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Create the job schedule on the instance of SQL Agent.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschSystemFullBkupJobSched.Create()\u00a0\u00a0\u00a0 End SubEnd Module<\/pre>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 Part 1 of a three article series, Allen White shows how to automate the backup of all of your databases, using SQL Server Management Objects (SMO) and either native backup or Red Gate&#8217;s SQL Backup tool.&hellip;<\/p>\n","protected":false},"author":221822,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4519,4248,4249,4168,4738,4739,4336,4150,4151,4740],"coauthors":[48342],"class_list":["post-245","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-automation","tag-backup","tag-backup-and-recovery","tag-database","tag-database-backups","tag-red-gate-sql-backup","tag-smo","tag-sql","tag-sql-server","tag-xp_sqlmaint"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/245","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221822"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=245"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/245\/revisions"}],"predecessor-version":[{"id":72993,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/245\/revisions\/72993"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=245"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=245"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}