SqlHarvester

About The Library

SqlHarvester is a free command-line based scripting engine written in .NET. It enables the import/export of database content across Sql Server 2000/2005/2008 database servers.

During export mode database content is scripted into flat Sql files. Unlike scripts generated by other commercial utilities these scripts validate database content at runtime so that data is inserted or updated based on the existence of primary keys.

In this way scripts may be run on any database with the same schema in order to synchronize content across disconnected servers. The scripts also disable database constraints at runtime to avoid conflicts. The import of content via the tool is transactional.

Features

• Compatible with Sql Server 2000/2005/2008
• Update/insert of data based on primary keys
• Runtime enable/disable of constraints
• Handles unicode & binary data types
• Script subset of data from multiple tables
• Transaction based data import
• Command-line based interface, integrates with build scripts

Usage

SqlHarvester [-export|-import] [<option>]
 -export
 Â Exports content from the target database.
 -import
 Â Imports content into the target database.

Options:
 -connectionString:<connectionString>
 Â The connection-string used to connect to the target database.

 -tables[:tableExpression1,[:tableExpression2], ...]
 Â Defines one or more tables from which to script content.

 -defaultScriptMode:[NotSet|Delete|NoDelete]
 Â Specifies if content is scripted with deletes by default.

 -outputDirectory:<filePath>
 Â The location of script files for scripting or seeding.

 -verbose:<level>
 Â Specifies the output verbose level (0-4).

Where not specified on the command-line the default SqlHarvester settings can be configured using the SqlHarvester.exe.config file.

Examples

The following scripts content from the table TableA where it meets the condition TableId>10. Any existing content not matching this criteria in the target database will be deleted when the script is imported.

SqlHarvester -export -tables:"TableA Where TableId>10 With Delete"

The following scripts content from table TableA where it meets the condition TableId>10. When imported the scripted content will be merged with any existing content. Existing rows will be updated, missing rows will be inserted.

SqlHarvester -export -tables:"TableA Where TableId>10 With NoDelete"

The following scripts content from the table TableA, TableB and TableC. Each table has a different delete mode and filter specified. The verbose mode is set to 3.

SqlHarvester -export -tables:"TableA With NoDelete":"TableB":"TableC Where ColumnB='A' With NoDelete" -verbose:3

The following scripts content from all user-database-tables. The * wildcard may also be used when defining tables in the configuration file.

SqlHarvester -export -tables:"*"

The following imports pre-scripted content from the output directory into the Demo database. Data is imported within a transaction and rolls back on error. Database constraints are disabled during the import process.

SqlHarvester -import -connectionString:"data source=(local);Integrated Security=SSPI;Initial Catalog=Demo"

The following imports pre-scripted content from the specified directory SqlScripts, into the default target database defined in the configuration file. Any files with the Sql extension within this directory will be imported.

SqlHarvester -import -outputDirectory:"C:\SqlScripts"