SQL Server Backups on Amazon EC2 Instances

If you have an EC2 Windows instance with SQL Server, you've probably noticed the SQL Server Agent missing. So how do we perform backups?

Option 1: Select each database in Management Studio, right click and run a backup, one database at a time. (Really? Does anyone have time for this)

Option 2: Use Transact-SQL. You can run the statement below, one command per database. This is quicker and easier than option 1 but it's still initiated manually.

backup database DATABASENAME to disk='c:\backup\FILENAME.BAK'

Option 3: Run a script to generate the Transact-SQL and run the commands with a scheduler. You can do this with any server side language with a SQL Server connection. The example below is written in Coldfusion.

<cfsetting requesttimeout="180">

<!--- Current Datetime --->
<cfset t = dateformat(now(),"yyyymmdd") & timeformat(now(),"hhmm")>

<!--- Delete backups older than this date --->
<cfset deleteDate = dateadd("d",-4,now())>

<cfset request.datasource = "CFDATASOURCE">

<cfquery datasource="#request.datasource#" name="backup">
   backup database DATABASENAME to disk='c:\backup\FILENAME_#t#.BAK'
</cfquery>

<!--- Delete old backups --->
<cfdirectory action="list" directory="c:/backup" filter="*.BAK" name="dirQuery">

<cfloop query="dirQuery">
   <cfif dateCompare(deleteDate,dirQuery.dateLastModified) eq 1>
      <cffile action="delete" file="c:/backup/#dirQuery.name#">

   </cfif>
</cfloop>