Quick and Dirty Scripting

I've been often asked to restore a database to another server and have all transactions logs applied to it up to a certain point. Using SQL Server 2005, you could setup Transaction Log shipping and it works very well, or you could copy the files over manually then apply them manually using SQL Server Enterprise Studio. Well I'm lazy so I came up with this simple script to do the work for me . . .

This script will ask for the database name to be restored as well as the directory where the transaction logs , drop any existing database of that name, restore the database and its transaction logs. It will not setup your database backups (full or transaction) nor will it replicate them over. I do have scripts for this but a tool like SyncBackSE works just as well. Oh this is a Windows batch script that utilizes osql which comes with SQL Server 2005.

@echo off

set BAKFILE=%1
set DBSNAME=%2
set LOGDIR=%3

IF NOT DEFINED BAKFILE (GOTO HELP)
IF NOT DEFINED DBSNAME (GOTO HELP)
IF NOT DEFINED LOGDIR (GOTO HELP)

echo Using the following variables to restore the database
echo Database Backup file - %BAKFILE%
echo Database Name - %DBSNAME%
echo Trans Log Directory - %LOGDIR%
choice /M "Do you want to continue"

IF ERRORLEVEL 2 ( GOTO END)

set DATADIR=d:\DATA
set SQL=%TMP%\restore.sql
set OUT=.\restore_results.txt

IF EXIST %SQL% ( del %SQL% )

IF NOT EXIST %BAKFILE% (
echo %BAKFILE does not exist. Must exit.
GOTO END
)

echo USE [MASTER] > %SQL%
echo GO >> %SQL%
echo ALTER DATABASE %DBS% SET SINGLE_USER WITH ROLLBACK_IMMEDIATE >> %SQL%
echo GO >> %SQL%
echo DROP DATABASE %DBS% >> %SQL%
echo GO >> %SQL%

echo RESTORE DATABASE [%DBSNAME%] > %SQL%
echo FROM DISK = '%BAKFILE%' >> %SQL%
echo WITH NORECOVERY >> %SQL%
echo GO >> %SQL%

for /f "tokens=* delims= " %%a in ('dir /b /a-d /od %LOGDIR%\*.trn') do (
echo RESTORE LOG [%DBSNAME%] >> %SQL%
echo FROM DISK = '%LOGDIR%\%%a' >> %SQL%
echo WITH NORECOVERY >> %SQL%
echo GO >> %SQL%
set LAST=%%a
)

echo RESTORE LOG [%DBSNAME%] >> %SQL%
echo FROM DISK = '%LOGDIR%\%LAST%' >> %SQL%
echo WITH RECOVERY >> %SQL%
echo GO >> %SQL%

osql -E -w 5000 -i %SQL% -o %OUT%
GOTO END

:HELP
echo.
echo Usage: restore_dbs.bat [BAKFILE] [DBSNAME] [LOG DIR]
echo.
echo.
echo BAKFILE - The full path to a recent backup file of the database
echo DBSNAME - Name of the new database
echo LOG DIR - The full path to a directory that contains all the recent tanslogs
echo *The LOG DIR should not contain any transaction logs older than BAKFILE
echo.
echo Eg: restore.bat d:\Backups\SampleDBS_backup_200705222201.bak SampleDBS d:\Temp
echo.

:END
IF EXIST %SQL% ( del %SQL% )

Labels: batch sql

posted by Brian Denicola  # 10:44 AM