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.
|@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.|
|@ftpPassword||nvarchar(128)||FTP user password.|
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
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.