Wednesday, April 25, 2007

Database deployment - How to ?

I was working on one of the projects where I was doing database development and another two guys were working on .Net development. We did't had common DEV server to which we connect and do the work. So in such situation obviously I will have to give a new version of database to each of these developers. So obvious next way is to backup and restore, but I will have to teach the developer how to do it. So I came-up with new option and i.e. batch script. Below is the content of the batch file.

/****** RestoreDB.bat *********************/

::CD "C:\Girish_Patil\Deployment\Restore"
::restoreDB Ultra_V2 C:\Girish_Patil\Deployment\Restore\DB_Backup.BAK C:\Girish_Patil\Deployment\Restore SLYSLE-PC\GP_SQL2K5
echo off

if "%1"=="" goto usage

if "%2"=="" goto usage
if "%3"=="" goto usage
echo.echo Database is being restored pls. wait..

echo =================================

osql -E -S%4 -Q"if exists(select name from master..sysdatabases where name = '[%1]') drop database [%1]" -w4000 >> RestoreLog.txt

osql -E -S%4 -Q"RESTORE DATABASE [%1] FROM DISK = N'%2' WITH FILE = 1, MOVE N'ULTRA' TO N'%3\%1_Data.mdf', MOVE N'ULTRA_log' TO N'%3\%1_Log.ldf', NOUNLOAD, REPLACE, STATS = 10" -w4000

echo =================================
echo [%1] Database is restored and is ready for use !!!
goto end

echo usage: RestoreDB DBName BkpFilePath DbPhysicalFilePath SrvName



I takes 4 parameter
1=Database name to restore as,
2=DB Backup file name with path,
3=Where do you want the new database files to be located,
4=SQL Server name, with instance name.

In my senario the SQL Server used to be running locally. So this script is ment to work smoothly as is on the local server. However if you want to restore it on remote server, in that case you need to place the DB Backup file on the remote server and provide the path local to it.

Pls. mail/contact me if you need any thing more specific.

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.