[SQL] Create a Job to Backup and FTP Your Database

0 comments suggest edit

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/

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.



13 responses

  1. Avatar for czm
    czm April 20th, 2008

    Thanks alot, this is really useful.

  2. Avatar for Soe Thiha
    Soe Thiha May 5th, 2008

    Where can I get this CreateBackupJob store procedure? I am using the sql server 2005.
    thanks in advance.

  3. Avatar for Dan
    Dan July 28th, 2008

    Can't access the stored procedure :(

  4. Avatar for RSsqure
    RSsqure August 3rd, 2008

    Could not locate or download CreateBackupJob stored procedure script. We are using SQL 2000 and 20005.
    It would be really helpful if you could share.
    Thanks in advance.

  5. Avatar for SqlBackupAndFtp
    SqlBackupAndFtp October 8th, 2008

    If you need to backup SQL Server database you can use SQLBackupAndFtp utility (which is free for 1-2 databases).
    It zips the backups, stores them on a network or on a FTP server, removes old backups and sends an e-mail confirmation on job's success or failure.

  6. Avatar for Steven
    Steven October 14th, 2008

    I would like to try this script but I am directed to tool.veloc-it.com and password box. You cannot even get to the site to sign up or register.

  7. Avatar for anu
    anu January 15th, 2009

    I am also directed to tool.veloc-it.com and password box after which I am not able to traverse.

  8. Avatar for virender
    virender March 1st, 2009

    hi i am not able to find out CreateBackupJob proc, and other url not working .Anybody have "CreateBackupJob" store procedure? please send me

  9. Avatar for BenM
    BenM April 3rd, 2009

    The link is dead for me.

  10. Avatar for prashant
    prashant May 12th, 2010

    I am trying to download your utility, but the URL does not exist.
    Can you please help?

  11. Avatar for Harry
    Harry April 26th, 2011

    much more secure and robust solution here
    using CLR

  12. Avatar for vlad
    vlad May 25th, 2012

    This seems exactly waht I've been searching, but the script is no longer available at the address posted.
    Can you please update the address, if this script still exists?

  13. Avatar for Ahmed Ramadan
    Ahmed Ramadan May 17th, 2014

    links are broken