Managing a large SQL Server inventory requires an efficient management processes.

Following on from DBA  productivity and less is more  , this post outlines the system I use to manage the DBA scripts across the database server environment

 1)       Develop a script library covering the essentials of DBA database management . Tasks include : Reindexing , statistics, defragementation, checkdb , kill user processes, kill all db connections  and much more..

My current version of SQL_TOOLS has 345 scripts covering a wide range of  DBA tasks

2)       Create an installation T-SQL script. The purpose is to deploy the scripts onto the SQL Server Instance via command line .

An example of the script could be as follows . The script I actually use doesn’t allow the DROP DATABASE as the logon account doesn’t have DROP DATABASE privileges , to avoid any mistakes

 This example script assumes you have the five scripts mentioned in the same directory :

/* SCRIPT: SQL_TOOLS_INSTALL.sql */
/* BUILD A THE SQL-TOOLS DATABASE */

-- This is the main caller for each script
SET NOCOUNT ON
GO

PRINT 'CREATING DATABASE'
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'SQL_TOOLS')
DROP DATABASE SQL_TOOLS
GO
CREATE DATABASE SQL_TOOLS
GO
USE SQL_TOOLS
GO

:On Error exit

:r isp_ALTER_INDEX_main.sql
:r isp_UPDATE_STATISTICS_main.sql
:r isp_DBCC_CHECKDB.sql
:r isp_KILL_ALL_DBCONNECTIONS.sql
:r isp_KILL_USER_PROCESSES.sql

PRINT 'SQL_TOOLS DATABASE CREATE IS COMPLETE'
GO

3) Create a batch file installation script.  This is the script you’ll execute from the command line – which will trigger the script in step 2.

       
An example is : 
       /* SCRIPT: SQL_TOOLS_INSTALL.bat */
       /* Excecute from the command line */
       /*input parameter1 = ”

SQLCMD -E -d master -S %1 -i "SQL_TOOLS_INSTALL.sql"
PAUSE

4)       Once the scripts are deployed onto the SQL Server Instance – you have a script library available to a)  create scheduled or ad-hoc tasks b) deploy secure scripts for troubleshooting , analysis and reporting

The key to this system is  a) maintaining a centralised script library , test and proven on lower environments.  b) rapid deployment onto a sql server environment c) Securing the scripts and limiting execution rights to relevant accounts

This approach is only one part of a strategy . Further considerations are scheduling of scripts , managing installation, disaster recovery and inventory management

See Also

Should libraries be installed on database servers ?

Powershell - run script on all sql servers