README.MD 3.33 KB
Newer Older
1 2
SQLScripts
============
dwentzel's avatar
dwentzel committed
3 4
This is a repository for publicly available SQL Scripts I've created and moved from my website into a proper VCS.  
This repo contains a myriad of scripts.  
dwentzel's avatar
dwentzel committed
5

6 7 8
DBA-in-a-Box
-------------
This is a process I've been using since SQL 6.5 that will quickly set up general database hygiene processes when I don't have a dedicated DBA available to me.  Specifically, full and log backups, index reorgs, and DBCC consistency checks.  
dwentzel's avatar
dwentzel committed
9

10 11 12
MyOldCITProcess
----------------
This is an example continuous integration process that I used years ago to automate database build testing.  http://www.davewentzel.com/content/doing-database-cit-tfs.  Although the code is outdated (vbscript) the CIT testing concepts have not changed a bit.  We always want to build a net-new database from our scripts and compare it with upgraded copies of our dbs from every previous GA'd version of our software.  
dwentzel's avatar
dwentzel committed
13

14 15 16
Compare			
-------------
Various scripts that aid in comparing db schemas and seed/master/system/lookup table data between two databases.  This is part of any good database build/deploy/CIT system.  Certainly GUI tools can do this much better, but the focus here is on automation in a CIT system.  
dwentzel's avatar
dwentzel committed
17

18 19 20
DatabaseBuildProcess	
---------------------
This is my old database build process.  I have since migrated my new customers to the MD3 process which also resides in my git repo.  MD3 uses PoSH so it's a little more modern.  It also has a ton of usability improvements.  There is still some code in here that I occasionally borrow however. MD3 can be found here:  http://git.davewentzel.com/dwentzel/md3
dwentzel's avatar
dwentzel committed
21

22 23
sysjobhistoryPurge	
-------------------
dwentzel's avatar
dwentzel committed
24 25 26 27 28 29 30 31
http://www.davewentzel.com/content/blocking-and-contention-sysjobhistory.  A utility that customizes what gets 
purged via sysjobhistory.  Allows far more flexibility than what Microsoft provides natively.  Also, the purge 
is "smart" and will not cause blocking and concurrency problems on instances with many, frequent SQL Agent 
jobs.  Includes tsqlt unit tests.  

`CreateSnapshot.sql` : http://www.davewentzel.com/content/automatic-database-snapshot-creation-script.  
Quickly creates a database snapshot and provides you with the commands to restore the db back to the snapshot.  
This is great when testing some code (not in prod) where transaction control may not be possible (such as testing 
32 33 34 35 36
service broker features or replication).  This will allow you to quickly "roll back" any changes. 

`LinkedServerGenerator.sql` :  Dyanmically creates or destroys a linked server.  This is useful to "build your own
connection string". Specifically in dev envs where you may need to quickly copy some data from another server using
"INSERT INTO...SELECT".  
dwentzel's avatar
dwentzel committed
37 38 39 40 41 42 43 44 45 46 47 48

`PostRestoreSteps.sql` : http://www.davewentzel.com/content/post-restore-process.  Train your staff to always 
run this proc as part of their environment build and db restore process and you'll ensure your environment 
(database at least) is always configured properly. 

`SampleDataContract.sql` shows how to use data contracts in your stored procs to eliminate errors from SSIS, 
SSRS, etc.   

`UntrustedForeignKeys.sql` : http://www.davewentzel.com/content/find-and-fix-untrusted-foreign-keys.  Finds and 
optionally fixes (if it can) untrusted foreign keys in your database.

Please see my other public repos at http://git.davewentzel.com
dwentzel's avatar
dwentzel committed
49

dwentzel's avatar
dwentzel committed
50

51