HPE Storage Users Group https://3parug.net/ |
|
MS SQL Dev Database refresh from snapshot script https://3parug.net/viewtopic.php?f=17&t=45 |
Page 1 of 1 |
Author: | Richard Siemers [ Fri Aug 20, 2010 9:00 pm ] |
Post subject: | MS SQL Dev Database refresh from snapshot script |
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: 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 4011 times Feel free to post/add your own or suggest changes/fixes and enhancements! |
Author: | andyhu [ Fri Oct 21, 2011 1:14 am ] |
Post subject: | Re: MS SQL Dev Database refresh from snapshot script |
Hi, Richard I have a question: What is the relationship between PROD_SQL_OTHER_DB_E.clone and PROD_SQL_OTHER_DB_E.rw ? |
Author: | Richard Siemers [ Mon Nov 07, 2011 5:32 pm ] |
Post subject: | Re: MS SQL Dev Database refresh from snapshot script |
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. |
Author: | Nohbody [ Tue Jun 18, 2013 2:06 am ] |
Post subject: | Re: MS SQL Dev Database refresh from snapshot script |
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! |
Author: | Richard Siemers [ Fri Jul 19, 2013 12:51 pm ] |
Post subject: | Re: MS SQL Dev Database refresh from snapshot script |
Sorry, I do not have the USP detach script, nor do I know who the original author is unfortunately. |
Page 1 of 1 | All times are UTC - 5 hours |
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |