SQLScripter is a utility that scripts SQL Server databases, jobs, logins and any other sql server object. The utility is designed to automate the process of schema generation.

SQLScripter is a powerful command line utility that scripts SQL Server objects. The utility is designed to automate the process of schema generation.

SQLScripter scripts your entire server. Schedule an automatic job that scripts all database level objects and server level objects on a daily or weekly basis.

Script Jobs, Logins, Linked Servers, Tables, Views, Stored Procedures, Users, UDFs, Indexes….

Oooops… a problem found in a stored procedure loaded in the recent version – no need to look for the appropriate backup and restore. Save that time and immediately get the required object available on your file system.

Your developers access the production servers to see if the schema matches the schema at their environment – not any more, automate a job that brings the production schema to your organization and save your developers and your organization a lot of precious time.

Besides the ability to script all objects there are many command line switches that are helpful in the DBA”s routine tasks, for example while tuning your indexes the I switch will generate 3 files: one file contains the CREATE statements for all indexes, one file contains the CREATE statements for all indexes excluding PKs and one file contains the DROP statements. Similarly the switch FK will generate 2 files: one file contains the CREATE statements and one file containing the DROP statements for all Foreign Key Constraints. This can be done for a single database, a few databases or all databases on the server. In the same way the switch TR will script triggers. DBAs working with Replication may find the NFR switch useful. For the complete list of available switches see the ReadMe file.

SQLScripter can be useful in some High Availability scenarios where you need to assure that objects belonging to a specific server are always available and exists on another server when a disaster event occurs.

SQLScripter is a professional tool at a low cost price.

Installation and configuration is quick. The product is very light and when you install it you do not effect anything on your machine.

Obtain a license

If you would like to work with the fully featured Standard Edition then all that is required is to purchase a license. There is no need to uninstall SQLScripter but only to locate the license file at the working folder.
To obtain a license file follow these steps:

  1. Purchase a license by clicking the PayPal button
  2. Email the ComputerName.tek file (located at your working folder) to support@sqlserverutilities.com
  3. Receive a reply mail containing the license file
  4. Locate the received license file at your working folderThe working folder is the folder where SQLScripter is installed. If the default installation path was not modified during the installation then the path should be:
    C:\Program Files\SQLScripter\ (32 bits machines)
    C:\Program Files (x86)\SQLScripter\ (64 bits machines)

Prerequisites

SQLScripter requires Microsoft .NET Framework version 2.0 installed on the machine where the program is installed.

SQL Server’s port (typically port 1433) open two directions between the machine installed with SQLScripter and the SQL Server(s) being scripted.

Supported operating systems

Windows Server 2000
Windows Server 2003
Windows Server 2008
Windows XP
Windows Vista

Comments

SQLScripter terminates with a return value of 0 on success and 1 on failure.
SQLScripter does not script encrypted routines.

Installation

Run SQLScripter.msi file to install the program.
Following the installation edit SQLScripter.exe.config file and Servers.config file.
Once the two files have been configured you can launch the program by running SQLScripter.exe file.

Usage

There is one configuration file to edit before running SQLScripter.exe:
SQLScripter.exe.config (see the bellow table for details)
SQLScripter.exe.config is where you define all the configuration settings required.

Log File

There are various settings that if required can be modified by editing the file log4net.config.
The key “file” represents the logs file location and defaults to the root of drive C:\ (C:\SQLScripter.log).
The key “maxSizeRollBackups” defaults to 5 which represent the maximum number of log files that are available at a given time. The files are managed in a FIFO (First In First Out) algorithm.
The key “maximumFileSize” defaults to 10 which represent a size of 10MB per log file.

Support

When applying for support please send a description of the problem encountered to support@sqlserverutilities.com with SQLScripter in the subject of the mail and attach the following files:
SQLScripter.exe.config
SQLScripter.log

Key Value Description
Server ServerNameServerName\Instance For a default instanceFor a named instance
Database db1db1; db2; db3;

All

Script specific databaseA semi column separated list of databases

All databases

ObjectsToScript UU; V; P; FK; FN;

All

Script specific objectA semi column separated list of objects

All objects

U Tables
V Views
P Stored Procedures
FN User Defined Functions
FK Generates two files: One file creates Foreign Key Constraints One file drops all Foreign Key Constraints
TR DML Triggers – Generates two files: One file creates all Triggers One file drops all Triggers
I Generates three files: One file creates all indexes One file creates all indexes excluding PK One file drops all indexes excluding PK
DDL DDL Database Triggers
DDLSRV DDL Server Triggers
SC Schemas
RL Database Roles
SRL Server Roles
L Logins
J Generates two files: One file containing all Jobs One file containing all Jobs apart of Replication jobs and Maintenance Plan jobs
DDLSRV DDL Server Triggers
UG Generates three files, this is the combination of J + L: One file containing all Jobs One file containing all Jobs apart of Replication jobs and Maintenance Plan jobs and one file contains Logins
DDLSRV DDL Server Triggers
LS Linked Servers
US Database Users (Principals)
PP GDR (GRANT, DENY, REVOKE) permissions of objects, Users and Logins
NFR Generates 9 files: A DROP, CREATE AND CREATE NOT FOR REPLICATION per each object type: Foreign Key Constraints, Check Constraints and DML Triggers
Output Path C:\sqlscripter_out The path where SQLScripter will create the folder and generate the script files. This can be a local path, a mapped drive, a network drive or a UNC path (Universal Naming Convention; \\computername\sharedfolder\resource). If the OutputPath is not local a UNC path is the preferred option. The account executing SQLScripter requires the appropriate permissions to connect and create folder and files at the OutputPath. If OutputPath is not supplied or OutputPath is not a valid path then the current location is assumed (the location where SQLScripter.exe is executed from)
ZipDir 10 Zip the output folderDo not Zip the output folder
ZipPW 1 If the ZipDir key is configured with a value of 1 then optionally you may protect the zip file with a password
DeleteOutputPathAfterZip 10 If the ZipDir key is configured with a value of 1 then optionally you may delete the Output folder thus leaving just the zip file
AuthenticationMode 10 Windows Authentication
DaysToKeepOutputFolder 300 Any Output Folder older than the defined value (30 days in this example) will be deletedNo clean up will be done
Version Description Released
1.0.0.0 First release 10/07/2007
1.0.0.1 Fixed minor issues of compatibility with objects not supported by SQL Server 2000 07/10/2007
1.0.0.2 Fixed minor issues of compatibility with objects not supported by SQL Server 2000 03/11/2007
1.0.0.3 Added connectivity using SQL Login 07/01/2008
1.0.0.4 Added the I (Indexes) switch 05/02/2008
1.0.0.5 Added the NFR switch 11/03/2008
1.0.0.6 Fixed a problem that the zip file was corrupted 17/11/2008
1.0.0.7 Fixed a problem that the zip file was corruptedAdded the passwords hashed to the Logins script (SQL 2005 only)

Add the option to supply a semi column separated list of database names in addition to a specific database name or the “ALL” options available in previous versions

Add the UG switch (Upgrade)

SQL Server 2008 compatible

19/02/2009
2.0.0.0 Add a license requirement to run the utility in the fully featured Standard Edition while the Freeware Edition becomes limited in featuresReplaced the logger with the log4net logger

Add a Cleanup task that deletes old Output Folders

Add the option to supply a semi column separated list of objects in addition to a specific object name or the “ALL” options available in previous versions

Add the LS switch (Linked Servers)

24/08/2009
2.0.0.1 Add the PP switch (GDR Permissions)Fixed an issue that indexes of Indexed Views were not scripted 20/10/2009