Poor Man’s Log Shipping

We wanted to have off-site backups for our database. SQL Server 2000 provides Log Shipping. With log shipping, a server can automatically copy and restore its logs to a stand-by server. However, it can only be done on a local network.

We do have log shipping enabled at our data center. However, we wanted to keep log backups – as backups, without restoring them – to a remote site. So we implemented a poor man’s log shipping using Windows scheduling, zip application from Info-Zip, and Xlight FTP server.

We chose one full backup as our starting point. Subsequent backups of transaction logs were to be shipped offsite. We used 7-Zip to chop up and compress the approximately 40 GB of database backup into approximately 8 GB of a regular zip file. The settings were kept at their default values, except the part where the resulting zip file was to be split up into 650 MB sizes.

Using batch files (code at the end) scheduled to run once a day, we copy all files created from midnight the previous day up to the point the batch file is run to a temporary location on the local machine. This collection of files is compressed and then sent off-site via FTP. Since we do not have an instance of SQL Server at the remote site, we do not restore these logs or apply them to the full backup taken.

Main Batch File

:: Resources that provided the code or helped write it:
:: http://malektips.com/xp_dos_0013.html
:: http://malektips.com/xp_dos_0002.html
:: http://www.computerhope.com/batch.htm#4
:: http://www.computing.net/programming/wwwboard/forum/14356.html
echo %time%
mkdir “C:\BKPS-TEMP”
@FOR /F “tokens=*” %%i IN (‘yesterday.bat’) DO set ystrday=%%i
set todaydate=-%date:~10,4%-%date:~4,2%-%date:~7,2%
xcopy “C:\BKPS\*.*” “C:\BKPS-TEMP” /D:%ystrday% /s /q
zip -r “C:\BKPS-TEMP\db1tlogbkp.zip” “C:\BKPS-TEMP”
rename bkp.zip bkp%todaydate%.zip
FTP -s:ftp.txt ftp.foobar.com
rmdir “C:\BKPS-TEMP” /s /q
echo %time%

FTP File

lcd “C:\BKPS-TEMP”
mput bkp*.zip


:: This file gives yesterday’s date, that is, today minus one
:: The code was taken as is from the following web page
:: Get Yesterday date in MS DOS Batch file posted by srini_vc
:: If you are the author of the code and would like the code taken down, please leave a comment and we will get back to you

@echo off

set yyyy=

set $tok=1-3
for /f “tokens=1 delims=.:/-, ” %%u in (‘date /t’) do set $d1=%%u
if “%$d1:~0,1%” GTR “9” set $tok=2-4
for /f “tokens=%$tok% delims=.:/-, ” %%u in (‘date /t’) do (
for /f “skip=1 tokens=2-4 delims=/-,().” %%x in (‘echo.^|date’) do (
set %%x=%%u
set %%y=%%v
set %%z=%%w
set $d1=
set $tok=))

if “%yyyy%”==”” set yyyy=%yy%
if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% – 100

set CurDate=%mm%/%dd%/%yyyy%

set dayCnt=%1

if “%dayCnt%”==”” set dayCnt=1

REM Substract your days here
set /A dd=1%dd% – 100 – %dayCnt%
set /A mm=1%mm% – 100


if /I %dd% GTR 0 goto DONE

set /A mm=%mm% – 1

if /I %mm% GTR 0 goto ADJUSTDAY

set /A mm=12
set /A yyyy=%yyyy% – 1


if %mm%==1 goto SET31
if %mm%==2 goto LEAPCHK
if %mm%==3 goto SET31
if %mm%==4 goto SET30
if %mm%==5 goto SET31
if %mm%==6 goto SET30
if %mm%==7 goto SET31
if %mm%==8 goto SET31
if %mm%==9 goto SET30
if %mm%==10 goto SET31
if %mm%==11 goto SET30
REM ** Month 12 falls through


set /A dd=31 + %dd%



set /A dd=30 + %dd%



set /A tt=%yyyy% %% 4

if not %tt%==0 goto SET28

set /A tt=%yyyy% %% 100

if not %tt%==0 goto SET29

set /A tt=%yyyy% %% 400

if %tt%==0 goto SET29


set /A dd=28 + %dd%



set /A dd=29 + %dd%



if /I %mm% LSS 10 set mm=0%mm%
if /I %dd% LSS 10 set dd=0%dd%

echo %mm%-%dd%-%yyyy%


Comments are closed.

%d bloggers like this: