Can I use SQL Server to backup, zip and ftp - as a one button process

Posted on 2006-05-04
Last Modified: 2011-09-20
I have a requirement to backup a database, shrink it, zip it and ftp to a server all as one process (me thinks here this is going to be command line stuff) - does any body have any ideas on how to accomplish this.


Question by:Paul_Blackler
    LVL 30

    Assisted Solution

    You can just create a SQL Server job with three steps.

    The first step is of type 'Transact-SQL' and will contain a backup command in it, something like this:

    BACKUP DATABASE <dbname> TO DISK='PathToBakFile'

    The next step is of type 'CmdExec' and will contain the command line to ZIP your backup file. You'll need to download a command line zipper (I think PKZip or Winzip are the usual)

    The next step is of type 'CmdExec' and will contain the command line to ftp the file. You can use the standard windows FTP or download another one.

    There's lots of logging and notification stuff that you can set up here.
    LVL 11

    Accepted Solution

    backup and shrink in reverse order i hope.

    furthermore, you probably want to create a job..

    maintenance\sql server agent\jobs

    create a step to shrink
    a step to backup
    then you can use the type dropdown to select a cmdExec and call the zip commandline
    same goes for FTP, although you might want to create a FTP script for that

    doable, but it assumes you have full controll over the DB and
    you think using CMDExec is a good Idea.

    personally, i'd vote no on that last one. ;)


    Author Comment

    I actually want to use a bespoke program called FTP Voyager from the CMDExec would that cause any problems
    LVL 11

    Expert Comment

    in theory, if there is commandline access to the FTP prog, there should be no problem.

    i personally just have objections to the use of cmdExec from a security perspective.

    Author Comment

    Thanks both of you

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now