Scheduled MSSQL Express Backup

Microsoft SQL Server Express is a free slimmed down version of Microsoft SQL Server. One of the features missing from the Express edition is the backup facility, however you can still use the SQL commands. Peter Shaw of Website Development Ltd (and a member of Devon CFUG) knocked up this script to backup the databases and scheduled itself to run daily.

<cfset request.dsn = "msde" />
<cfset request.dbUsername = "backupOperator" />
<cfset request.dbPassword = "mypassword" />
<cfset request.backupDirectory = "e:\backups\mssql\" />
<cfset request.backupScriptUrl = "http://myservername/sql2005scheduledBackup.cfm" />

<!--- Schedule recuring task if not defined --->
<cfschedule action="update"
  task="sql2005scheduledBackup"
  operation="HTTPRequest"
  url="#request.backupScriptUrl#"
  startDate="01/01/08"
  startTime="01:30"
  interval="daily"
  requestTimeOut="300" />

<!--- get list of all databases --->
<cfquery name="listOfDatabasesToBackup" 
  datasource="#request.dsn#" 
  username="#request.dbUsername#" 
  password="#request.dbPassword#">
  select name as dbName
  from master.dbo.sysdatabases
  where name <> 'tempdb'
</cfquery>

<!--- do the back up --->
<cfloop query="listOfDatabasesToBackup">
  <!--- Create individual directories for each database if they don't exist --->
  <cfif not directoryExists("#request.backupDirectory##dbName#")>
    <cfdirectory action="create" directory="#request.backupDirectory##dbName#" />
  </cfif>
    
  <cfquery name="requestBackup" 
    datasource="#request.dsn#" 
    username="#request.dbUsername#" 
    password="#request.dbPassword#">
    backup database #dbName#
    to disk='#request.backupDirectory##dbName#\#dbName#_db_#dateFormat(now(), "YYYYMMDD")##timeFormat(now(), "HHMMSS")#'
    with format
  </cfquery>
</cfloop>

<!--- delete old backups to prevent disk getting very full --->
<cfdirectory action="list" 
  name="MSSqlBackupFileList" 
  directory="#request.backupDirectory#" 
  recurse="yes" />

<cfloop query="MSSqlBackupFileList">
  <!--- parse the cfdirectory string into a proper date --->
  <cfset correctLastModifiedDate = createDate(listGetAt(listFirst(dateLastModified, " "), 3, "/"), listGetAt(dateLastModified, 2, "/"), listGetAt(dateLastModified, 1, "/")) />

  <!--- delete backups older than 7 days, of type file (not directory) and retain the backup from the first of each month --->
  <cfif correctLastModifiedDate lt dateadd("d", -7, now()) and
    type eq "File" and
    day(correctLastModifiedDate) neq 1>
    <cffile action="delete" file="#directory#\#name#" />
  </cfif>
</cfloop>

This code has been tested against Microsoft SQL Server 2005 Express Edition, but should work with 2008 as well (although I'd personally wouldn't recommend the upgrade). Thanks to Peter for allowing me to share this.

• Posted in:
• SQL
• ColdFusion
• DevonCFUG

3 comments

1. Why do you not recommend the upgrade to 2008? I don't plan upgrade any time soon but curious about your negative experience.
   
   Thank you,
   Gabriel
   
   Comment by Gabriel – May 07, 2009
2. Hi Gabriel, 2008 doesn't really differ that much from 2005 and it's a complete pain to install!
   www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/installing-sql-server-2008-express-162
   
   Comment by John Whish – May 07, 2009
3. Rhis is a cool little tip, I REALLY miss the agent on the express edition, I use it for so many maintenance tasks aside from the backup, it's a real shame MS don't bundle it with express but I suppose we can't have everything for nothing ;-)
   
   Rob
   
   Comment by Robert Rawlins – August 22, 2009