MS SQL Dev Database refresh from snapshot script

Post Reply
User avatar
Richard Siemers
Site Admin
Posts: 1333
Joined: Tue Aug 18, 2009 10:35 pm
Location: Dallas, Texas

MS SQL Dev Database refresh from snapshot script

Post by Richard Siemers »

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.


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 4616 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 4444 times


Feel free to post/add your own or suggest changes/fixes and enhancements!
Richard Siemers
The views and opinions expressed are my own and do not necessarily reflect those of my employer.
andyhu
Posts: 1
Joined: Thu Oct 20, 2011 3:10 am

Re: MS SQL Dev Database refresh from snapshot script

Post by andyhu »

Hi, Richard
I have a question:
What is the relationship between PROD_SQL_OTHER_DB_E.clone and PROD_SQL_OTHER_DB_E.rw ?
User avatar
Richard Siemers
Site Admin
Posts: 1333
Joined: Tue Aug 18, 2009 10:35 pm
Location: Dallas, Texas

Re: MS SQL Dev Database refresh from snapshot script

Post by Richard Siemers »

In this example the clone is a full copy production... of which snapshots are taken and used by dev. Since dev uses a snapshot of the clone, the clone can be resynced (can take some time) while DEV is in use. Also dev can revert back to the "clone" without having to resync with production.
Richard Siemers
The views and opinions expressed are my own and do not necessarily reflect those of my employer.
Nohbody
Posts: 1
Joined: Wed May 29, 2013 3:30 pm

Re: MS SQL Dev Database refresh from snapshot script

Post by Nohbody »

Richard,
Do you have the t-sql code for usp_Attach_DB...
I'm going do be doing this exact exercise in my environment with 15 DBs on 6 servers, it would be nice to have an SP time-tested and proven.
Thanks for all the great information!
User avatar
Richard Siemers
Site Admin
Posts: 1333
Joined: Tue Aug 18, 2009 10:35 pm
Location: Dallas, Texas

Re: MS SQL Dev Database refresh from snapshot script

Post by Richard Siemers »

Sorry, I do not have the USP detach script, nor do I know who the original author is unfortunately.
Richard Siemers
The views and opinions expressed are my own and do not necessarily reflect those of my employer.
Post Reply