USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TruncateLogs]
@databaseName nvarchar(1024)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(1000)
DECLARE @logfilename NVARCHAR(1000)

SELECT @logfilename = b.name
FROM sys.sysdatabases a
INNER JOIN sys.sysaltfiles b
ON a.dbid = b.dbid
WHERE fileid = 2
AND a.name = @databaseName

IF @@rowcount = 1 BEGIN

SET @sql = '
USE ' + @databaseName + '
BACKUP LOG ' + @databaseName + '
with truncate_only
dbcc shrinkfile (' + @logfilename + ', 1)'

EXECUTE sp_executesql @sql
END

END

Next, just add it to the loop in the original ColdFusion scheduled task script:

<cfstoredproc datasource="master" procedure="dbo.sp_TruncateLogs">
<cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
</cfstoredproc>

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.