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
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
Feel free to post/add your own or suggest changes/fixes and enhancements!