SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

To Script or Not To Script

When approaching an automation problem the first questions you must answer is whether you should even invest the time to write a script. There are scenarios where it just isn’t worth your time. Depending on the tools available to you even the task of scripting out every database object could be accomplished easier and faster by not scripting.

Buy vs. Build vs. Borrow

When I look into my collection of SQL Server tools, I use combination of Microsoft shipped, free software, various scripts I’ve built or borrowed and products I’ve bought from various software vendors. One purchased product I use is from Red Gate called SQL Compare. Using SQL Compare I can quickly create database build scripts faster and better than I could if I created my own collection scripts. Given enough time I could have written my own tool, but why should I invest the time when there’s a commercial tool that does it better? Scripting is about solving problems not adequately addressed by tools you own or can buy.

Using SQL Compare for solely scripting out databases isn’t worth the purchase price. The main reason I use Red Gate is for the comparison and build scripts it generates. The ability to script out database objects is just an added bonus.

SQL Compare doesn’t include native PowerShell support, however the Pro edition includes a command-line utility called sqlcompare which can be used within PowerShell. The following example scripts out an entire database including automatically creating the folder structures as shown below. No scripting required!

PS C:\Program Files\Red Gate\SQL Compare >  ./sqlcompare.exe /s1 "Z003\sql1" /db1 "pubs" /mkscr:"C:\pubs"
SQL Compare Command Line V8.1.0.360
Copyright c Red Gate Software Ltd 1999-2009
Creating folder of scripts 'C:\pubs' from database 'pubs' on 'Z003\sql1'...
PS C:\Program Files (x86)\Red Gate\SQL Compare >


Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.


Posted by Jason Brimhall on 15 March 2011

Cool Beans

Leave a Comment

Please register or log in to leave a comment.