In two earlier posts I presented a couple of SQL stored procedures that I promised to tie together. The first procedure generates a random time of day and the second can be used to FTP a file from within SQL Server.

Well in this post I put these two together to create a stored procedure for creating a database job that will take a nightly backup of a database and FTP it to another location. I hope this isn’t terribly anti-climactic for you.

This script is perfect for a quick and dirty backup plan for a database. If you are dealing with enterprise level (and sized) databases, your DBA will probably scoff at this script, and rightly so. Such a situation really needs a more robust database backup plan probably with differential backups etc…

But for smaller operations, this will save your butt. I use this to backup my blog database and some of our development databases every night.

The parameters are pretty much a combination of the two previous parameters.

Parameter Datatype Description
@databaseName nvarchar(128) Name of the database to backup
@jobName nvarchar(255) The name of this Job as it would be listed in Enterprise Manager under Management | Sql Server Agent | Jobs
@scheduleName nvarchar(255) A descriptive name for the schedule.
@timeOfDay int Time of day as an int (HHMMSS). Default is -1 which indicates a random time of day between midnight and @maxHour
@maxHour int If generating a random time of day, the upper bound for the hour in which the backup job can run. By default 4 am.
@owner nvarchar(128) The database user account that this job runs under. By default the sa account.
@backupDirectory nvarchar(256) The directory on the database server to backup the file.
@ftpServer nvarchar(128) FTP host name.
@ftpUser nvarchar(128) FTP user.
@ftpPassword nvarchar(128) FTP user password.
@ftpPath nvarchar(128) FTP path.

Why the randomness?

Notice that if you set @timeOfDay to -1, this script will create a schedule for the job at a random hour of the day which is constrained by @maxHour. This is really there to help stagger the times at which the database backups run. All too often administrators make the mistake of scheduling the backups of all the backups at the same time. Of course, you can override this randomness by simply specifying a @timeOfDay.

Typical usage of the query looks like

exec CreateBackupJob @databaseName='MCTDb'
    , @ftpServer='example.com'
    , @ftpUser='UserName'
    , @ftpPassword='Supersecret'

When you obtain this script, I recommend modifying the script when you create it on a server to have give the ftp parameters default values, if that makes sense in your environment. That can save you time when creating new backups.

You can download the full script here. This script will drop (if they exist) and recreate all three stored procedures I mentioned. Let me know if you found this useful.

Download it from http://tools.veloc-it.com/