Tech Talk by Mohsin Abbas

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

1. Connect to 3rd party’s server using ftp and download the .zip file containing the backup of the Microsoft SQL Server database.
2. Unzip the file to extract the .bak file containing Microsoft SQL Server database backup.
3. Detach existing Microsoft SQL Server database and delete it’s .mdf and .ldf files.
4. Restore .bak file as new database.

SOLUTION

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.

@echo off
echo Deleting previously downloaded .zip and .bak files
del M:\Restore\*.zip
del M:\Restore\*.bak

echo Connecting to ftp using commands from ftp.txt and downloading .zip file, then renaming it to DB.zip
ftp -s:M:\Restore\ftp.txt ftp.3rdparty.net
rename M:\Restore\*.zip DB.zip

echo Unzipping compressed backup
unzip M:\Restore\DB.zip
rename M:\Restore\*.bak DB.bak

echo Detaching previous database
sqlcmd -S SERVERNAME\INSTANCENAME -i detachDB.sql

echo Deleting previous database files
del “C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\DB_Live.mdf”
del “C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\DB_Live_log.ldf”

echo Restoring database from new backup
sqlcmd -S SERVERNAME\INSTANCENAME -i restoreDB.sql

3. Schedule the script to run at the time of your choosing.

DEPENDENCIES

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.

ftpuser
ftp password
cd database
mget *.ZIP
y
bye

2. unzip.exe – download from here

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.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’DB_Live’, @keepfulltextindexfile=N’true’
GO

4. restoreDB.sql – again you’ll need to modify this or create your own.

RESTORE DATABASE [DB_Live] FROM  DISK = N’M:\Restore\DB.BAK’ WITH  FILE = 1,  MOVE N’SQLMASTER_Data’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\DB_Live.mdf’,  MOVE N’SQLMASTER_Log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\DB_Live_log.ldf’,  NOUNLOAD,  STATS = 10
GO

CONSTRAINTS

1. The ftp commands assume the presence of only one .zip file to download in the ‘database’ folder. In my client’s case, the 3rd party overwrites the file every day.
2. This was written for and tested with Microsoft SQL Server 2005 Express on Windows Server 2003. No warranties are made to work with this version of Windows and SQL Server or any other.