HPE Storage Users Group

A Storage Administrator Community




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: MS SQL Dev Database refresh from snapshot script
PostPosted: Fri Aug 20, 2010 9:00 pm 
Site Admin
User avatar

Joined: Tue Aug 18, 2009 10:35 pm
Posts: 1328
Location: Dallas, Texas
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:
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

Attachment:
File comment: Sample diskpart script to mount drives
snapshot.txt [3.15 KiB]
Downloaded 3966 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 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

Attachment:
File comment: Sample diskpart script for mounting luns in Windows
mount.txt [222 Bytes]
Downloaded 3803 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.


Top
 Profile  
Reply with quote  
 Post subject: Re: MS SQL Dev Database refresh from snapshot script
PostPosted: Fri Oct 21, 2011 1:14 am 

Joined: Thu Oct 20, 2011 3:10 am
Posts: 1
Hi, Richard
I have a question:
What is the relationship between PROD_SQL_OTHER_DB_E.clone and PROD_SQL_OTHER_DB_E.rw ?


Top
 Profile  
Reply with quote  
 Post subject: Re: MS SQL Dev Database refresh from snapshot script
PostPosted: Mon Nov 07, 2011 5:32 pm 
Site Admin
User avatar

Joined: Tue Aug 18, 2009 10:35 pm
Posts: 1328
Location: Dallas, Texas
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: MS SQL Dev Database refresh from snapshot script
PostPosted: Tue Jun 18, 2013 2:06 am 

Joined: Wed May 29, 2013 3:30 pm
Posts: 1
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!


Top
 Profile  
Reply with quote  
 Post subject: Re: MS SQL Dev Database refresh from snapshot script
PostPosted: Fri Jul 19, 2013 12:51 pm 
Site Admin
User avatar

Joined: Tue Aug 18, 2009 10:35 pm
Posts: 1328
Location: Dallas, Texas
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.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 


Who is online

Users browsing this forum: No registered users and 13 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group | DVGFX2 by: Matt