SQLScripter

I’m going to introduce you to a free tool called SQLScripter, a command-line utility that you can use to quickly and easily automate database schema scripting tasks, such as script generation for disaster recovery or as part of the development process for checking in the database schema. The manual process of using the GUI to generate a schema is slow, tedious, and prone to mistakes. SQLScripter addresses all these problems by transforming database schema generation from a slow, manual process into a fast, automated one. SQL Scripter improves on SQL Server Management Studio’s (SSMS’s) Generate SQL Server Scripts Wizard by eliminating the requirement that you have to use the GUI to manually build the exact composition of the objects you want scripted.

Using SQLScripter

SQLScripter was written by Yaniv Etrogi, a SQL Server developer and DBA living in Tel Aviv, Israel. Yaniv wrote the tool while developing a high-availability solution for a replicated environment. The subscriber needed to be up-to-date with the publisher’s work so that in the event of a disaster the subscriber could take on the role of the publisher.

You can run SQLScripter as a SQL Server Agent job step of type “Operating system (CmdExec)” via the Windows Task Scheduler, from a batch file, or by manually executing the utility from the Windows command prompt. You can control the tool via its configuration file, SQLScripter.exe.config, which uses parameters to manage all aspects of the schema script generation. You can configure the utility in a variety of ways. For example, you could configure SQLScripter to be executed on Server1 to script out a set of objects on Server2 and direct the output to a path and file on Server3. SQLScripter’s biggest benefit is that it provides an easy way for you to automate schema script generation from within SSIS jobs.

SQLScripter uses trusted connections when connecting to local and remote servers. Note that you can’t use it to script encrypted database objects. Upon completion, SQLScripter terminates with a return value of 0 on success and 1 on failure.

You can scope the schema script generation by instance, database, and object type. You can also script out server-level objects such as jobs and roles. You must declare on Output Path, where the script file is stored as a local path, mapped drive, network drive, or by its Universal Naming Convention name. Of course, the account executing SQLScripter must have permission to create folders and files at Output Path. To script objects, the user must have SELECT permissions or otherwise be able to “see” the object to be scripted. You can use a .zip file to store the schema generation script that’s created and specify a password for the .zip file to secure it.

You can use SQLScripter to perform many different tasks, including

• migrating a database schema between production, quality assurance, and development environments

• keeping the latest schema in source control • tracking schema history regularly using an automated job

• creating a script quickly and easily to drop and create all indexes for fill factor maintenance and defragmentation

• scripting out important server-level objects before the installation of an upgrade, service pack, or hotfix to ensure the recoverability of jobs and extended stored procedures

System Configurations

SQLScripter was developed using SQL Server 2005 and uses the Microsoft .NET Framework 2.0, including SQL Server Management Objects. It runs on Windows Vista, Windows XP, and Windows Server 2000 and later. It was tested primarily on SQL Server 2005 in 32-bit environments, but it might work (unsupported) in 64-bit environments.