Home > Scripting/Automation, SQL Server > Scripted ftp download of zipped SQL DB, restore into SQL Server

Scripted ftp download of zipped SQL DB, restore into SQL Server

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.
  1. Common sense
    February 18, 2011 at 7:31 pm

    All of that trouble, while you could have set up SQL replication instead…

    • February 18, 2011 at 7:44 pm

      Appreciate your input ‘Common Sense’. I see you so little I began thinking common sense must be a super power.

      SQL replication is something I’d have liked myself, had this been an option available with the 3rd party who controls the source database. It was investigated, we were told no.

      Another thing ‘common sense’ told me to do was to not use Windows ftp client storing passwords in clear text and not having any resume support. That is now changing with a 3rd party utility doing the downloads and executing restore scripts on download completion.

      P.S. It wasn’t much trouble. Took less than half hour. The blog post took longer actually.

  2. October 4, 2011 at 12:04 am

    Hey there, You’ve done a great job. I will certainly digg it and personally recommend to my friends. I am confident they will be benefited from this web site.

  3. March 1, 2013 at 7:02 pm

    Nice post. I was checking continuously this weblog and I am impressed!
    Extremely useful info specifically the closing section
    🙂 I take care of such information much. I used to be seeking this particular info for a very lengthy time.
    Thank you and best of luck.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: