SQLScripter is a command line utility that scripts SQL Server databases. The utility is designed to automate the process of schema generation.
In case you come across a need that SQLScripter does not cover in the current version please mail your requirement to [email protected] with SQLScripter in the subject of the mail and I will consider implementing it in an upcoming release.
Freeware edition - A limited edition that can be used at no cost
Standard edition - A fully featured edition that requires a license
| Feature | Comment | Freeware | Standard |
|---|---|---|---|
| Script unlimited number of databases | The number of databases that can be scripted on a single instance | OK | OK |
| Script database level objects | Objects type that can be scripted | ALL except Stored Procedures, Functions, Views | OK |
| Script server level objects | Jobs, Linked Servers, Logins | x | OK |
| Zip the Output folder | Compress the Output folder generated at the end of each run | x | OK |
| Password protection on the zipped Output folder | Secure the output by a password | x | OK |
| Clean up the Output folder | A clean up task that deletes folders older than the defined date | x | OK |
| Support all available command line switches | The option to script only a single object type | x | OK |
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. Go to www.sqlserverutilities.com/SQL-Server-Utilities-SQLScripter-download.htm
2. Purchase a license by clicking the PayPal button
3. Email the ComputerName.tek file (located at your working folder) to [email protected]
4. Receive a reply mail containing the license file
5. Locate the received license file at your working folder
The 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)
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.
Windows Server 2000
Windows Server 2003
Windows Server 2008
Windows XP
Windows Vista
SQLScripter terminates with a return value of 0 on success and 1 on failure.
SQLScripter does not script encrypted routines.
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.
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.
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.
When applying for support please send a description of the problem encountered to [email protected] with SQLScripter in the subject of the mail and attach the following files:
SQLScripter.exe.config
SQLScripter.log
| Key | Value | Description |
|---|---|---|
| Server | ServerName ServerName\Instance |
For a default instance For a named instance |
| Database | db1 db1; db2; db3; All |
Script specific database A semi column separated list of databases All databases |
| ObjectsToScript | U U; V; P; FK; FN; All |
Script specific object A 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 | 1 0 |
Zip the output folder Do 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 | 1 0 |
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 | 1 0 |
Windows Authentication |
| DaysToKeepOutputFolder | 30 0 |
Any Output Folder older than the defined value (30 days in this example) will be deleted No 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 corrupted Added 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 features Replaced 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 |