Post reply

Copy SQL .bak file to other server using FTP and restore the backup to a database

  • May 23, 2013 at 2:16 pm

    #275510

    Hi,

    I am looking for solution for below approach using TSQL or powershell or using SQL agent job.

    1. Perform backup on prod DB server of one database-- this can be done

    2. FTP/SFTP the file to the staging DB server - i am clearly looking for this line and do not want to use SSIS, this i havet to perform using SFTP client/server process.

    3. Restore the backup to a database

    Can any one please help how to start working on this process and to automate this process at the end.

  • May 25, 2013 at 8:01 am

    #1618525

    1. PowerShell Database Backup Script[/url]

    2. SFTP in PowerShell note: haven't used it but is based on a well known C# library wrapped as a native PowerShell snap-in so I would start here and fall back on using WinSCP

    3. can you connect to the remote instance in PowerShell? if so, then like step 1 you can invoke a restore the database using PowerShell. if not, then a SQL Agent Job that runs a PowerShell script periodically looking for new backups to restore would be a good place to start.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 2, 2013 at 1:42 pm

    #1620346

    You can upload SQL backups to FTP or SFTP with SQLBackupAndFTP[/url] tool. It also compresses them before uploading to destinations. There is ability to scheduled backup jobs and setup email notifications on success or failure.

  • June 3, 2013 at 4:28 am

    #1620457

    You can also use WinSCP for uploading the backup file.

    Then you can use serveral scripts or command line code and put it in an agent jobstep.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply