I've been lax in my blogging and my ColdFusion duties lately. I just today installed CF 9 for the first time, and I'm usually way ahead of the curve on that. Work has been super busy, and we are stuck on CF 8 at the office for the foreseeable future. So far, the upgrade has been quick and painless. Now I need to start diving into the new features. I think the main feature for me is the general performance enhancements, but I'm sure I'll get into the Office integration as well. One nice thing about upgrading is being able to try out the features as Enterpise version for a month before putting in the license key for Pro version and losing the functionality.

I was also thinking about a SQL Server upgrade. I'm running the Express version, which is perfect for most small-medium web sites. One thing it doesn't have is a way to do maintenance. I've been doing manual backups. Well, after looking at the price of a SQL Server license, I decided to look for a good automated backup. I found a backup script online at http://www.mssqltips.com/tip.asp?tip=1174 and modified it to meet my needs (made directory name a parameter)

CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName nvarchar(1024),
@type CHAR(1) = 'F',
@directory nvarchar(1024) = 'C:\SQLBackup'
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(1000)
DECLARE @currentDate NVARCHAR(20)

SELECT @currentDate = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/',') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':',')

IF @type = 'F'
SET @sql = 'BACKUP DATABASE ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Full_' +
@currentDate + '.BAK'

IF @type = 'D'
SET @sql = 'BACKUP DATABASE ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Diff_' +
@currentDate + '.BAK' WITH DIFFERENTIAL'

IF @type = 'L'
SET @sql = 'BACKUP LOG ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Log_' +
@currentDate + '.TRN'

EXECUTE sp_executesql @sql
END

Next, I created my own stored procedure for deleting old database backups. Basically, in a "real" SQL Server, the backup task will delete backups back to a date you specify. We'll pass in the database name, type of backup, timeframe (in weeks), and the directory name.

CREATE PROCEDURE [dbo].[sp_RemoveOldBackups]
@databaseName sysname,
@backupType CHAR(1) = 'F',
@howlong integer = 4,
@directory nvarchar(1024) = 'C:\SQLBackup'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @type nchar(3)
SET @type = 'BAK'
if @backupType = 'L' BEGIN SET @type = 'TRN' END
DECLARE @backupPath nvarchar(2048)
SET @backupPath = @directory + '\' + @databaseName
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(ww, -1 * @howlong, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @backupPath, @type, @DeleteDate,1

END

Now, rather than use Windows scheduled tasks for this, I wanted a simple ColdFusion scheduled task. The stored procedures should be stored in the master database, so you'll need a datasource that will allow execute permissions on these two stored procedures. Finally, save the following into a ColdFusion file and store it in one of your web sites. Set the scheduled task to execute once a week.

<cftry>
<!--- List of databases to backup --->
<cfset variables.databases = "mydatabase,Northwind,myOtherDatabase" />
<!--- Path to the backup folder --->
<cfset variables.backupPath = "Z:\SQLBackup" />
<!--- How many weeks to keep backups --->
<cfset variables.howLong = "4" />

<!--- Loop through the databases --->
<cfloop from="1" to="#ListLen(variables.databases)#" index="variables.i">
  <cftry>
    <!--- First, backup the database --->
    <cfstoredproc datasource="master" procedure="sp_BackupDatabase">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
      <cfprocparam cfsqltype="cf_sql_char" value="F" />
      <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.backupPath#" />
    </cfstoredproc>
    <!--- Next, delete the old databases --->
    <cfstoredproc datasource="master" procedure="sp_RemoveOldBackups">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
      <cfprocparam cfsqltype="cf_sql_char" value="F" />
      <cfprocparam cfsqltype="cf_sql_integer" value="4" />
      <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.backupPath#" />
    </cfstoredproc>
    <cfcatch>
      <cflog application="yes" text="Database backup failed #now()# for #ListGetAt(variables.databases, i)#" />
    </cfcatch>
  </cftry>
</cfloop>
<cfcatch>
   <cflog application="yes" text="Database backup failed #now()# outside loop" />
</cfcatch>
</cftry>

Obviously, this is the bare bones of a backup system. You can modify the scripts to perform differential backups to meet your needs, log file backups, and add some error handling to the stored procedures, but this is a good start.