Redgate Snapper, RedGate Scream, 7z... OH MY!!!

I literally jumped up and yelled, "FK YEAH" when i discovered these 2 marvelous nuggets of joy. What's so great?

  1. Clean and simple
  2. Helps reduce the signs of ageing
  3. Sanity check
  4. Better quality of sleep
  5. FREE!!!!
If you have a solid environment under lock and key with change control abound, this is probably not for you, crazy nut. But if you are that gun slinger riding in to save the day, everyday, with out so much as time to gawk at the lady's, then this is right up your alley.

How many times have you found yourself knee deep in it, asking your self what then hell just changed to cause mass panic, timeouts, missing reference, asp yellow screen of death? Well this will give you a leg up in "those" inevitable times.


@ECHO OFF
SETLOCAL
::---------------------------------------------------------------
::HOT SAKE
::---------------------------------------------------------------
:: Pre-Reqs
:: SNAPPER
:: http://labs.red-gate.com/Tools/Details/RedGateSnapper
:: SCREAM
:: http://labs.red-gate.com/Tools/Details/RedGateScream
:: 7-zip [can create encrypted zip, aes-256, oh my]
:: http://www.7-zip.org/
::---------------------------------------------------------------

ECHO Creating DB Schema Snapshots Date: %DATE% %TIME%

SET SNAPPER=C:\SQLTools\RedGate.SQLSnapper\RedGate.SQLSnapper.exe

SET mTMPSTORE=C:\SQLSNAPPER\
SET mSAVETOFOLDER=\\NASSHARE\SQLSchema\BOBSQL\
SET mSQLSERVER=BOBSQL
SET mTEMPDBLIST=%mTMPSTORE%tmpDBs.txt

SET ZIPAPP="C:\Program Files\7-Zip\7z.exe"
SET EXT=snp

::Get the datetime into single variable
FOR /F "tokens=1-4 delims=/ " %%i in ('date /t') do (SET mDATE=%%l%%j%%k)

FOR /f "Tokens=1-4 delims=:." %%i IN ("%TIME%") DO (
SET hh=%%i
SET mm=%%j
SET ss=%%k
SET ms=%%l
)
IF "%hh:~0,1%" == " " (set hh=0%hh:~1,1%)
SET mTIME=%hh%%mm%%ss%%ms%

::Clear any existing list
DEL /q %mTEMPDBLIST% >nul

:: Write db name(s) to tmp file.
SQLCMD -S %mSQLSERVER% -W -u -h-1 -Q"SET NOCOUNT ON; select name from sys.databases" >>%mTEMPDBLIST%

::Loop on Db name(s) and create Schema snapshots with 7z
FOR /f "tokens=* delims= " %%a IN (%mTEMPDBLIST%) do (
IF "%%a" NEQ "" CALL:SNAPIT "%mSQLSERVER%" "%%a" "%mTMPSTORE%%%a_%mDATE%_%mTIME%.%EXT%"
)

::GOTO :EOF
::Archive all Schema Snapshots
%ZIPAPP% u -tzip %mSAVETOFOLDER%%mDATE%_%mTIME%_%EXT%.7z %mTMPSTORE%*%EXT% > nul
IF %ERRORLEVEL%==0 DEL %mTMPSTORE%*%EXT%

DEL /q %mTEMPDBLIST% >nul

ECHO THANK YOU COME AGAIN
GOTO :EOF

:SNAPIT
SET xSERVER=%~1
SET xDB=%~2
SET xFileName=%~3
:: This assumes you running with a trusted account
%SNAPPER% /verbose /server:%xSERVER% /database:%xDB% /makesnapshot:%xFileName% >nul
GOTO :EOF


KUDOS TO RED GATE for making this free. And 7z, best compression utility, imho ;-P

~ENJOY!

Comments :

0 comments to “Redgate Snapper, RedGate Scream, 7z... OH MY!!!”