Thursday, August 21, 2008

Backup data with bcp

What I wanted was not to have more of two months of data online in my table. In order to do accomplish this task I decided to use Ms SQL Server's Bulk Copy Program (bcp).

Here's the MSDN page on bcp usage :
http://msdn.microsoft.com/en-us/library/ms162802.aspx

Then, using osql (MSDN) I'll clean up the table of the rows backupped.

In my table there's a field named dtRow which contains the timestamp as UTC.

I wrote this little batch :

@echo off

:CHECK_ARGUMENTS
if "%1"=="" goto PRINT_USAGE
if "%1"=="-?" goto PRINT_USAGE

:CHECK_ARG_DIRECTION
if "%1"=="in" goto RESTORE_AREA
if "%1"=="out" goto BACKUP_AREA

goto PRINT_USAGE

:BACKUP_AREA

:CHECK_ARG_DAYS
if %2==-d goto CHECK_ARG_DAYS_2
if not %1==-d goto PRINT_USAGE
:CHECK_ARG_DAYS_2
if not "%3"=="" goto CHECK_ARG_PATH
goto PRINT_USAGE

:CHECK_ARG_PATH
if not "%4"=="" goto CHECK_BCP
goto PRINT_USAGE


:CHECK_BCP
echo Looking for BCP....
IF EXIST %PROGRAMFILES%\Microsoft SQL Server\80\Tools\Binn\bcp.exe GOTO CHECK_BCP_OK ELSE GOTO CHECK_BCP_KO

:CHECK_BCP_OK
echo BCP exists.
goto CHECK_OSQL

:CHECK_BCP_KO
echo BCP does not exist!!.
goto EXITPROGRAM


:CHECK_OSQL
echo Looking for OSQL....
IF EXIST %PROGRAMFILES%\Microsoft SQL Server\80\Tools\Binn\osql.exe goto CHECK_OSQL_OK ELSE GOTO CHECK_OSQL_KO

:CHECK_OSQL_OK
echo OSQL exists.
goto BACKUP_DATA


:CHECK_OSQL_KO
echo OSQL does not exist!!.
goto EXITPROGRAM


:BACKUP_DATA
ECHO BACKUP DATA....
SET query="SELECT * FROM MyDB.dbo.MyTable WHERE dtRow > ((SELECT TOP 1 dtRow FROM MyDB.dbo.MyTable ORDER BY dtRow DESC) - 86400*%3)"

"%PROGRAMFILES%\Microsoft SQL Server\80\Tools\Binn\bcp.exe" %query% queryout %4 -N -U sa -P sa -S.\SQL2000

IF not %ERRORLEVEL%==0 goto ERROR_BACKUP

:DELETE_DATA
ECHO.
ECHO DELETING SAVED ROWS
SET query="DELETE FROM MyDB.dbo.MyTable WHERE dtRow > ((SELECT TOP 1 dtRow FROM MyDB.dbo.MyTable ORDER BY dtRow DESC) - 86400*%3)"

"%PROGRAMFILES%\Microsoft SQL Server\80\Tools\Binn\osql.exe" -S.\SQL2000 -U sa -P sa -d MyDB -Q %query%

ECHO Done.

goto EXITPROGRAM


:RESTORE_AREA
echo RESTORING DATA....
"%PROGRAMFILES%\Microsoft SQL Server\80\Tools\Binn\bcp.exe" MyDB.dbo.MyTable in %4 -N -U sa -P sa -S.\SQL2000
ECHO Done.

goto EXITPROGRAM

:PRINT_USAGE
ECHO Simple Backup Utility
ECHO Usage : obu {in | out} -d days path
ECHO days : Number of days to backup
ECHO starting from the last record.
ECHO path : Path of the backup file.

:ERROR_BACKUP
echo Error during backup phase. Aborting...
goto EXITPROGRAM

:EXITPROGRAM

No comments: