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

• Dhruva_51

  SSChampion

  Points: 10521

  More actions

  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.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  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

• passm.ru

  SSC Rookie

  Points: 29

  More actions

  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.

• leon.booij

  Ten Centuries

  Points: 1062

  More actions

  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.