Automate SQL Server database Restore

Today we will see a small but very useful script.Many times we need to restore databases from REAL environment to TEST or development environment .

This complete activity can be automated with the below script.


   ====>  


Steps :

1. Create an empty batch file.

2. Put the blow script in it and supply real variable values to it .


::@echo off
 
:: Description
:: Automate SQL Server database restore process
:: Author : Dilip Singh
 
:: Set variables
SET SERVER=yoursevername\sqlserverinstancename
SET DB=databsename
SET SRC_SERVER=yoursourcedatabsename
SET SRC_DB=yoursourcedatabse_log
 
::databse backup file path
SET SRC_DAT=E:\backup\MyDB.bak
::Destination dtabase detials  SET DST_DAT=E:\Data\MyDestDB.mdf
SET DST_LOG=E:\Data\MyDestDB_log.ldf
 
REM === Restore
pause
REM === Please wait.. resotre in progress...
SQLCMD.EXE -S %SERVER% -Q "RESTORE DATABASE [%DB%] FROM  DISK = N'%SRC_DAT%' 
WITH  FILE = 1,  MOVE N'%SRC_SERVER%' TO N'%DST_DAT%', MOVE N'%SRC_DB%' TO N'%DST_LOG%', 
 NOUNLOAD,  REPLACE,  STATS = 10" || goto Error
 
REM === additional scripts can be called here i. creating user automatically etc.
pause  SQLCMD.EXE -S %SERVER% -v DB=%DB% -i yourscriptname.sql || goto Error
exit /b 0
:Error
exit /b 1
pause

3. save file and Execute it …

 

You may also find my previous post helpful .

Automate SQL Server operations by batch file

Advertisements

3 thoughts on “Automate SQL Server database Restore

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s