Archive

Recently I was given a requirement by a client to set up a scheduled ftp download of a zipped Microsoft SQL Server database backup file from a 3rd party’s ftp server and restore it into an SQL Server instance on one of the client’s servers. The process was to be run every morning.

I approached the problem using the below steps

Connect to 3rd party’s server using ftp and download the .zip file containing the backup of the Microsoft SQL Server database.

Here’s how you’ll achieve the same. This procedure uses some SQL scripts and one or more utilities not present in a default Windows installation. I’ll provide details for these as well in the dependencies section below.

1. Create a folder called ‘Restore’. This folder will store the relevant scripts and this is where the downloading/unzipping will happen.

2. In this folder, create a file ‘DailyRestore.bat’ with below commands which you’ll modify to match your server\instance name and file and folder locations. This is the main batch file that does all the process.

All dependencies need to be saved in the ‘Restore’ folder you created earlier.

1. ftp.txt – This file includes ftp commands to connect to an ftp server and get a file. Use text below – modify as you need. Each line is a command that the ftp utility will send to server. Yes, this includes username/password.

3. detachDB.sql – you’ll need to modify this to suit your own SQL installation. You can modify this or create your own detachDB script by manually detaching DB in SQL Server Management Studio and doing ‘Script Action to file’ instead of actually detaching the DB.