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!

Trigger + sp_start_job = Execute permissions Denied...

Well I had a novel idea of creating a trigger on a table to kick off a SQL job, asynchronously. Seemed simple enough. And it was until I needed to have the update on the table be executed under the applications security context. Bah. I could run cause I part of sysadmin. Google, google, google and then came across this post at StackOverflow.com. Led me to add applications user to

* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole
in msdb... BUT when selecting these roles I inadvertently added TargetServersRole :-(. All four appeared at end of possible role to select, sniff sniff.

After chasing my tail and triple checking the apps user, profiling the DB, and various poor mans debugging, I went back to google and found this GEM @ serverfault.com:

select dp.NAME AS principal_name,
dp
.type_desc AS principal_type_desc,
o
.NAME AS object_name,
p
.permission_name,
p
.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
where o.name = 'sp_start_job'
ARggggghhh, shoot me!

principal_nameprincipal_type_descobject_namepermission_namepermission_state_desc
TargetServersRoleDATABASE_ROLEsp_start_jobEXECUTEDENY

Removed that role and YAY!!!.

All that was really needed was SQLAgentOperatorRole.

On another note, I discovered TRY CATCH are useless in a trigger, or at least they were for me. Any errors in a trigger result in a rollback. I wanted at least part of the trigger to execute and the second part in question to fail gracefully.

Live and learn.

~Enjoy