Mike’s Dump

November 11, 2005

Automated SQL Server Backup for WebHost4Life

Filed under: Code — mikesdump @ 10:30 am

With the problems I’ve had with hard drives and more recently my laptop
I’ve wanted to get a better backup process going. My previous process
was whenever I remember which translated into every month if I was
lucky.

Webhost4Life offers a web interface to backup your database and then
you log in through FTP to download the file. My goal was to have an
automated way of doing this.

What I did was create three different files. The first file is the SQL
command to backup the database. I have used this before locally to

backup my MSDE databases. This file is passed as a parameter when
calling OSQL.EXE.

The second file is an FTP script file which is passed as a parameter to the command line FTP client. This script will:
1) Log in the user
2) Turn off prompting
3) Ensure we are downloading in binary mode
4) Downloads the backup file
5) Quits which closes the connection

The last file is a batch file, which ties everything together. First,
the batch file ensures the proper directory (for me) is set for when
the file is downloaded. Next, OSQL.EXE is called with host, user name,
password and script file passed as parameters. This backs up the
database. The FTP client is called passing the ftp script and IP as
parameters which starts the download of the backup file automatically.
The last task run in the batch file is to rename the backup file to
include the date.

To have this run every night I set up a Windows scheduled task to run the batch file.

The files I created are below. The following has been modified to protect the innocent.

wh4l_sql_backup.sql

BACKUP DATABASE [database
name
] TO DISK = N’C:\db\mikefriesen\whfl-cs-backup.bak’ WITH INIT ,

NOUNLOAD ,

NAME = N’database name BackUp’,

NOSKIP ,

STATS = 100,

NOFORMAT
go
quit

wh4l_ftp_script.txt

ftp_user_name
password

prompt
binary
get whfl-cs-backup.bak
quit

wh4l_backup.bat

REM Set the proper directory. This is where the backup file will be
stored.

g:
cd g:\
cd g:\sql_server_backups

REM Start the database backup using OSQL.exe. Parameters used are the
IP to the database
REM database user name, password and SQL script file:
wh4l_sql_backup.sql

“C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\OSQL.EXE” -S 255.255.255.255
-U database_user -P password
-i g:\sql_server_backups\wh4l_sql_backup.sql

REM Connect to the FTP server and download the backup file using the
ftp script file (whfl_ftp_script.txt).
REM IP to ftp server required.

ftp -s:wh4l_ftp_script.txt 255.255.255.255

REM Rename the downloaded backup file by appending the current date
(yyyy-mm-dd)
REM I did this so I’m not constantly overwriting the last file.

ren whfl-cs-backup.bak
whfl-cs-backup-%date:~10,4%-%date:~4,2%-%date:~7,2%.bak


A couple sources I used
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp
http://www.computing.net/dos/wwwboard/forum/14569.html

I feel the need to add a disclaimer.
This information is provide “AS-IS”. Please use at your own risk.

Advertisements

1 Comment »

  1. Nice Post!
    Thanks

    ————-
    http://www.webhost4life.org

    Comment by Evas — March 19, 2009 @ 10:28 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: