SQL Scripts Manager is a free application from Red Gate for managing and executing SQL scripts. It is designed in such a way that the best way of understanding what it does to try it out. It comes with a library of scripts that should be useful to you right away. You probably won’t realize that those scripts that are included are there to serve a dual purpose. they are useful in themselves, but also to demonstrate what can be done with the tool.
You’ll notice that SQL Scripts Manager features the Ola Hallengren scripts that were described at length in the Simple-Talk article by Brad McGehee called Automate and Improve Your Database Maintenance Using Ola Hallengren’s Free Script and Phil Factor’s scripts were described in SQL Server CRUD-Generation from System Views. Louis Davidson and Tim Ford contributed three scripts from their brilliant Simple-Talk book Performance Tuning with SQL Server Dynamic Management Views.
So why should one need SQL Scripts Manager
Why bother with SQL Scripts Manager when you already have SSMS, PowerShell, and SQLCMD? This is the first question I’ll tackle, but first, a bit of background.
The developers at Red Gate work single-mindedly on projects that can take many months to complete. Although they make Software Tools that are easy and interesting for the users, anything that requires months of toil, and extreme attention to detail is inevitably going to be rather less exciting to develop in the later stages. To get the fine detail right is not a creative task. One way that the company relieves the worst of the pressure on the development teams is to allow them occasional ‘down-tools’ weeks where they are allowed to work on their own projects. SQL Scripts Manager is the result of a couple of ‘Down-tools’ weeks. The people who work on projects such as SQL Compare or SQL Backup occasionally close their eyes and dream of creating something else, and the Down-tools weeks make that dream a reality. Already, several useful tools have emerged from this activity, such as SQL Search and MySQL Compare
SQL Scripts Manager started out in various coffee-time discussions about various dissatisfactions with SQLCMD and PowerShell for the routine execution of scripts. SQLCMD can do clever things, and is impressively fast, but if you need complex input forms or anything beyond the simplest of parameters, then you are suddenly feeling the design-restrictions of what is essentially a command line tool without a reasonable macro language. You can, of course, do a great deal with PowerShell, but this is never going to provide you with the means to find an execute scripts in an easily-maintained library of scripts. It is also idiosyncratic in it’s language. Once you’ve mastered it, you wonder what all the fuss is about, but the creation of PowerShell scripts is is not for the occasional user.
When a number of ideas coalesced into thinking about a tool, a number of design criteria coalesced. These were that …
- The tool ought to provide a versatile form-generation, so that a user could execute a script without special training. SSMS provides a simple form for filling in a template, that could then subsequently be executed. Too simple, far too simple. You cannot even generate a list or a combo to choose alternatives. With SSMS, it was a germ of a good die that never got finished. As well as providing a form that allowed the user to select the database and server, with may me a text field, and the ability to select one of a list of parameters,
- There had to be a better way of processing the results of a SQL query. You can, with SSMS, save the file as CSV or paste it into Excel, but what about the myriad of other things that one might want to do with data. Here PowerShell shines, but only for the departmental geek. For SQL Scripts Manager, The answer was to allow the loading of dynamic Python Libraries to process the results. There is an example provided that was used to save templates in the local SSMS template directory from Phil Factor’s scripts.
- It has to be dead easy to use. Any DBA will have scripts that need to be executed by whoever is on duty when unpleasant events happen. The obvious example is disaster recovery, but there are a number of eventualities that can happen to a production system that the typical DBA will have scripts for. These have to be executed by production staff for whom PowerShell may be a closed book, and SSMS a mystery. For this, SQL Scripts manager is ideal; but even when one has the special keystrokes of SSMS imprinted on one’s medullary cortex, it is great to be able to locate the right script, confirm that it is correct, point it at the correct recalcitrant database, and fire it.
- It must be possible to certify scripts so that the user is aware if any unauthorised, or unofficial, alteration has been made to any of them.
- The scripts, with all their extra parameters and user interface, must be stored in XML and editable in any XML Editor, and simple scripts should be easily created from example XML documents.
SQL Scripts Manager started in the first of Red-Gate’s Down-Tools weeks; the team continued the work in the second Down Tools week, by the end of which it was ‘nearly’ ready for release. Then, over the next few months, the product was refined and polished, and more scripts were added from more authors from the Friends of Red Gate (FORG). There are now 28 scripts from 9 contributing authors.
Running a script
Before you run a script, it is worth checking the source and the description. Some scripts require you to see the window that gives the opportunity to view the description….
…or the source.
When you then click to continue, you will then will see the input form, which will vary according to the script. Here is a pretty simple one. The ‘Save preset’ button allows you to save a commonly used server for this script as a default.
When you click ‘Run’, you will, with luck be met by a result displayed in grid form like this one seen below (Tracy’s script).
Creating a script file
When creating a new script, it is probably best to to start with a copy of one of the .rgtool XML files provided with SQL Scripts Manager. (select File > Show Scripts Folder from the main SQL Scripts Manager window to find where they are stored). There are also two simple template scripts that can be downloaded at the bottom of the article. If you want to do it from scratch, You’d make a copy of your TSQL Script and convert it into an .rgtool XML script file by wrapping it with appropriate XML elements and attributes, such that the resulting file is a valid SQL Scripts Manager XML file. You should also rename your script file to have an .rgtool extension. This XML file has a schema that is documented in The SQL Scripts Manager XML schema. When experimenting, you can alter the GUI definition which is part of the XML script file, by adding controls for selecting a SQL Server instance or database, and to map other controls to parameters required by your script (such as text boxes and option buttons). (See Types of control for detailed information)
Once you’ve finished editing your new .rgtool file, save it to the SQL Scripts Manager scripts folder, located at \Documents and Settings\All Users\Application Data\Red Gate\SQL Scripts Manager\ by default. and restart SQL Scripts Manager
If your .rgtool file is valid, the script will be shown in the list on the main SQL Scripts Manager window, ready to run. If there are problems with the file, it is associated with the ‘<Load Error>’ tag, and the error is shown beneath the script title in the scripts list:
Of course, SQL Scripts Manager will only report load errors that relate to problems with the XML structure of your .rgtool file, whereas any syntax or logic errors in the script code (the code contained within the <script> tags in your .rgtool file), will not be evident until you run the script using SQL Scripts Manager.
Here is just about the simplest template file you could use, with an explanation of what goes where. It is used to run a script against whatever database you select. It can be downloaded at the bottom of the article. I’d guess that this will cover most of your requirements if you have a number of relatively simple scripts.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<?xml version="1.0" encoding="utf-8"?> <tool version="1"> <description name="Template script (run against selected database)" icon="data" url="http://www.script_homepage.com/" version="1" ><![CDATA[ <p> Template for script that runs against a selected database on a selected server. </p> <p> <i>(the first line of this section appears under the script name, in the list of scripts)</i> </p> <p> Put your documentation about the script here. This section supports some HTML tags, such as <b>bold</b>, <i>italics</i>, lists, and <a href="http://www.red-gate.com">hyperlinks</a>. </p> <p> <b>IMPORTANT NOTE: If your script could have a significant impact on the server, please clearly document this here. The first time your script is run by a user, this documentation is shown to them.</b> </p> <p> In addition, you can add optional URL attributes for some of the XML elements: </p> <ul> <li>the description XML element: web page about the script (eg, if its hosted on http://www.sqlservercentral.com/scripts/rgtool/</li> <li>the author XML element: your homepage, twitter handle, and a picture of yourself (if you make it available for download)</li> </ul> You should leave the signature XML element empty, as currently only Red Gate can sign scripts. ]]></description> <author name="Your Name Here" url="http://www.your_homepage.com/" twitter="your_twitter_name" img="your_pic_if_you_make_it_available_to_download.png" /> <tags> <tag>Favorite</tag> <tag>WhateverTagYouWant</tag> </tags> <ui> <output displaytype="Grid | OutputLog" width="500" height="400" /> <block> <control type="header" label="Select server and database:"> <font name="Arial" size="12" /> </control> <control type="database" id="connection" /> </block> </ui> <script type="sql"><![CDATA[ -- This template is for a script that runs against a server, but has no other parameters. -- >>> PUT YOUR SCRIPT HERE <<< CREATE TABLE #msg (Output NVARCHAR(100)) INSERT INTO #msg VALUES ('Server:') INSERT INTO #msg VALUES (@@SERVERNAME) INSERT INTO #msg VALUES ('Database:') INSERT INTO #msg VALUES (@connection_database) SELECT * FROM #msg PRINT 'Messages, errors, etc, go to the process log' ]]></script> <signature></signature> </tool> |
There are a large number of icons compiled into the program that you can use just by specifying them in the XML file without having to supply your own. These are in the downloads at the bottom of the article. You can provide your own icon, but it has to be copied to the same folder as the script. The icon must be referenced by its full file name, (e.g. foo.ico), in the script
You will notice the <signature></signature> tags at the end of the XML document. Once you have created the scripts and had them signed off, they can be given a digital certificate by Red Gate. This will inform whoever runs the script that it has not been tampered with. There are plans afoot to provide a tool to do this, but exact details are yet to be announced.
The user interface
Here is one of the more complex forms that are presented in one of the sample scripts.
This is entirely determined from the following xml fragment from the corresponding XML file. It isn’t much harder than XHTML to figure out.. All the controls, and many more besides are described here.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
<ui> <output displaytype="ForceLog" /> <block id="b1" column="left"> <control type="header" label="Helper database location"> <font name="Arial" size="12" /> </control> <control type="database" id="connection"> <help> <title>Helper database location</title> <text><![CDATA[ <p>Select a SQL Server where the Ola Hallengren Maintenance Pack has been installed, then select the appropriate helper database (named 'RedGateSqlScriptsManager' by default).</p> <p>You can install the Ola Hallengren Maintenance Pack by running <b>Install Ola Hallengren Maintenance Pack</b>.</p> ]]></text> </help> </control> </block> <block id="b2" column="right"> <control type="header" label="Databases to back up"> <font name="Arial" size="12" /> </control> <control type="combo" id="DatabaseName"> <editable>true</editable> <list> <item>SYSTEM_DATABASES</item> <item>USER_DATABASES</item> <item>ALL_DATABASES</item> </list> <help> <title>Databases to back up</title> <text><![CDATA[ <p>Select a group of databases (<b>ALL_DATABASES</b>, <b>USER_DATABASES</b>, <b>SYSTEM_DATABASES</b>), or type your own selection of databases to back up.</p> <p>Examples:</p> <ul> <li>Use a comma (',') to separate multiple database names: '<b>Database1, Database2</b>'</li> <li>Use a minus sign ('-') to exclude a particular database from a group database backup: '<b>USER_DATABASES, -DatabaseToExclude</b>'</li> <li>Use paired percentage signs ('%') as wild cards, to back up all databases that have 'sample' in their name: '<b>%sample%</b>'</li> <li>Back up all databases that have 'sample' in their name, excluding 'Database1': '<b>%sample%, -Database1</b>'</li> <li>Back up all databases that do not have 'sample' in their name: '<b>ALL_DATABASES, -%sample%</b>'</li> </ul> ]]></text> </help> </control> <control type="header" label="Backup options"> <font name="Arial" size="12" /> </control> <control type="header" label="Path to backup directory:" /> <control type="text" id="Folder"> <width>200</width> </control> <control type="header" label="Backup type:" /> <control type="combo" id="BackupType"> <list> <item>FULL</item> <item>DIFF</item> <item>LOG</item> </list> </control> <control type="check" id="CheckVerify" label="Verify backup upon completion"> <help> <title>Verify backup upon completion</title> <text><![CDATA[ <p>Select this option, and SQL Server will automatically verify that the backup file is complete and readable (using the RESTORE VERIFYONLY command).</p> <p>It is good practice to use this option to verify your backups as soon as they have completed.</p> ]]></text> </help> </control> <control type="check" id="CheckCopyOnly" label="Copy-only backup"> <help> <title>Copy-only backup</title> <text><![CDATA[ <p>Select this option, and SQL Server will create a backup that is independent of the sequence of normal SQL Server Backups (for example, the usual 'Full' > 'Differential' > 'Log', backup sequence).</p> <p>SQL Server uses the WITH COPY_ONLY statement when you select this option.</p> <p>You should not use this option for backups that you may later want to use in combination with backups from your normal backup schedule.</p> ]]></text> </help> </control> </block> </ui> |
Community-supplied Scripts
Currently, the plan is for the SQL Server Central script repository to be used for community-contributed scripts. There will be support the addition of scripts wrapped up to run inside SQL Scripts Manager.