Search This Blog

Thursday, July 3, 2008

Zip & FTP database for backup purposes

My situation:

i need to backup, zip and FTP my database to another server for safety purposes.

My MSSQL database run a daily backup job at 12:00AM to backup folder (E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\) with name: MyDB_backup_200806010000.bak
where 2008 = YYYY, 06 = MM, 01 = DD and 0000 is fixed

my part is zip MyDB_backup_200806010000.bak to MyDB_backup_200806010000.zip and FTP to folder myapp/db in the remote site.

Software: 7zip, core ftp LE (both are free), both can be downloaded free only, google it online and you can get the file downloaded.

Steps:
1. Install Core Ftp LE in the server (my installation folder: c:"\program files\coreftp\coreftp.exe")
2. Open Core Ftp LE and create my FTP site and enter FTP username and password. I name my site mybacksite.
3. Install 7zip in the server. Copy 7z.exe from the installation folder (mine is in C:\Program Files\7-Zip\7z.exe) to database backup folder (mine is in E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\).
4. Write a windows batch script (batch) to perform the zip and ftp process. I name it appDBbackup.bat and put it in (mine is in E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\)

appDBbackup.bat:

@echo Off

for /f "tokens=1,2" %%u in ('date /t') do set d=%%v

for /f "tokens=1" %%u in ('time /t') do set t=%%u

if "%t:~1,1%"==":" set t=0%t%

rem set timestr=%d:~6,4%%d:~3,2%%d:~0,2%%t:~0,2%%t:~3,2%

set datestr=%d:~6,4%%d:~0,2%%d:~3,2%

set timestr=%t:~0,2%%t:~3,2%

7z a -tzip MyDB_backup_%datestr00.zip MyDB_backup_%datestr00.bak

setlocal

c:"\program files\coreftp\coreftp.exe" -s -O -u "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDB_backup_%datestr00.zip" -site mybacksite -p /myapp/db/


5. Create a windows scheduler to run the job. Point the task to appDBbackup.bat. In my case, i point to E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\appDBbackup.bat and i schedule it at 12:30AM, 30 minutes after the backup started.



to know more on how to create windows scheduler. read this

Done.

1 comment:

Anonymous said...

Thanks for brain!