MS SQL Dev Database refresh from snapshot script
Posted: Fri Aug 20, 2010 9:00 pm
I don't know who the exact author is but all credit goes to them, not me! 
This script has been passed around various SE's and customers of 3PAR, and there is no real official central repository for hosting or maintaining such things, so here is as good a home as any!
These script uses:
plink : executes a single command via SSH, freely downloadable on the internet.
osql : cli client for MS SQL server
diskpart : native windows command
unmount.txt : diskpart script to unmount drives, needs to be tailored for your host
mount.txt : diskpart script to mount drives, needs to be tailored for your host
The basic flow of the script is to refresh a set of cloned disks of a production SQL database, kills the users on the DEV system, detaches the datafiles, and unmounts the volumes from windows, unexports them from 3PAR, re-snaps them from the clone, and re-exports, mounts and attaches the data files to the dev SQL instance.
Here is a sample of the mount.txt file, you will need to edit it of course, and create the unmount.txt from scratch using the mount.txt as a template.
Feel free to post/add your own or suggest changes/fixes and enhancements!
This script has been passed around various SE's and customers of 3PAR, and there is no real official central repository for hosting or maintaining such things, so here is as good a home as any!These script uses:
plink : executes a single command via SSH, freely downloadable on the internet.
osql : cli client for MS SQL server
diskpart : native windows command
unmount.txt : diskpart script to unmount drives, needs to be tailored for your host
mount.txt : diskpart script to mount drives, needs to be tailored for your host
The basic flow of the script is to refresh a set of cloned disks of a production SQL database, kills the users on the DEV system, detaches the datafiles, and unmounts the volumes from windows, unexports them from 3PAR, re-snaps them from the clone, and re-exports, mounts and attaches the data files to the dev SQL instance.
Code: Select all
REM CLONE PROD_SQL VOLUMES TO MOUNT ON DEV_SQL
REM FULLCOPY/CLONE THE PROD DB VOLUMES (ALREADY ESTABLISHED, THIS ONE WILL USE SNAPSHOTS TO RESYNC THE TARGET VOLS)
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk creategroupvvcopy -r -b PROD_SQL_OTHER_DB_E.clone PROD_SQL_OTHER_DB_F.clone PROD_SQL_OTHER_DB_G.clone PROD_SQL_OTHER_DB_L.clone PROD_SQL_OTHER_DB_M.clone
REM KILL USERS Connected to Dev Database
osql -S DEV_SQL -d dbautility -U cloneuser -P cloneuser_password -q "EXIT(EXECUTE usp_KillUsers 'CPP')" > snapshot.log
IF ERRORLEVEL 0 GOTO KILLERROR
osql -S DEV_SQL -d dbautility -U cloneuser -P cloneuser_password -q "EXIT(EXECUTE usp_KillUsers 'CPPWORK')" >> snapshot.log
IF ERRORLEVEL 0 GOTO KILLERROR
REM DETACH DEV DB FROM DEV_SQL
osql -S DEV_SQL -d dbautility -U cloneuser -P cloneuser_password -q "EXIT(EXECUTE usp_Detach_DB)" >> snapshot.log
IF ERRORLEVEL 0 GOTO DETACHERROR
REM UNMOUNT THE VOLUMES ON DEV_SQL
diskpart /s unmount.txt >> snapshot.log
IF NOT ERRORLEVEL 0 GOTO UNMOUNTERROR
REM UNEXPORT ALL SNAPSHOT VOLUMES
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk removevlun -f PROD_SQL_OTHER_DB_E.rw 101 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk removevlun -f PROD_SQL_CPP_F.rw 102 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk removevlun -f PROD_SQL_CPP_G.rw 103 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk removevlun -f PROD_SQL_CPP_Log_L.rw 104 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk removevlun -f PROD_SQL_OTHER_DB_Log_M.rw 105 DEV_SQL
REM UPDATE THE SNAPSHOTS
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk updatevv -ro -f PROD_SQL_OTHER_DB_E.rw PROD_SQL_CPP_F.rw PROD_SQL_CPP_G.rw PROD_SQL_CPP_Log_L.rw PROD_SQL_OTHER_DB_Log_M.rw
REM EXPORT ALL SNAPSHOT VOLUMES AGAIN
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk createvlun -f PROD_SQL_OTHER_DB_E.rw 101 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk createvlun -f PROD_SQL_CPP_F.rw 102 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk createvlun -f PROD_SQL_CPP_G.rw 103 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk createvlun -f PROD_SQL_CPP_Log_L.rw 104 DEV_SQL
plink cloneuser@3PAR01 -i DEV_SQL_PRIVATE.ppk createvlun -f PROD_SQL_OTHER_DB_Log_M.rw 105 DEV_SQL
REM WAIT 240 SECONDS FOR DISKPART TO COMPLETE PREVIOUS OPERATION (testing on 30 sec or below values unsuccessful)
CHOICE /c YN /n /d Y /t 240
REM MOUNT ALL SNAPSHOT VOLUMES AGAIN
diskpart /s mount.txt >> snapshot.log
IF NOT ERRORLEVEL 0 GOTO MOUNTERROR
REM ATTACH SQL DB
osql -S DEV_SQL -d dbautility -U cloneuser -P cloneuser_password -q "EXIT(EXECUTE usp_Attach_DB)" >> snapshot.log
IF ERRORLEVEL 0 GOTO ATTACHERROR
ECHO SNAPSHOT SUCCESSFULL >> snapshot.log
Date /t >> snapshot.log
EXIT /B 0
:KILLERROR
ECHO ERROR KILLING USERS IN DATABASE, CALL 3PAR Admin >> snapshot.log
EXIT
:DETACHERROR
ECHO ERROR DETACHING DATABASE, CALL 3PAR Admin >> snapshot.log
EXIT
:UNMOUNTERROR
ECHO ERROR UNMOUNTING WINDOWS VOLUMES, CALL 3PAR Admin >> snapshot.log
EXIT
:MOUNTERROR
ECHO ERROR MOUNTING WINDOWS VOLUMES, CALL 3PAR Admin >> snapshot.log
EXIT
:ATTACHERROR
ECHO ERROR DETACHING DATABASE, CALL 3PAR Admin >> snapshot.log
EXIT
snapshot.txt- Sample diskpart script to mount drives
- (3.15 KiB) Downloaded 4623 times
Here is a sample of the mount.txt file, you will need to edit it of course, and create the unmount.txt from scratch using the mount.txt as a template.
Code: Select all
select volume=6
assign letter=H noerr
select volume=7
assign letter=I noerr
select volume=8
assign letter=J noerr
select volume=9
assign letter=K noerr
select volume=10
assign letter=M noerr
- mount.txt
- Sample diskpart script for mounting luns in Windows
- (222 Bytes) Downloaded 4453 times
Feel free to post/add your own or suggest changes/fixes and enhancements!