A scripts folder is a set of object creation scripts representing a database's schema and (optionally) data.
In SQL Data Compare you can create a scripts folder from an existing data source when you set up the comparison. An object creation script file is created for each object, and different object types are stored within subfolders you can specify.
When you deploy to a scripts folder, static data files are created.
You can use scripts folders:
- for version control of databases
For example, you may want to store the script files in a source control system, so that you can track the modified or new objects.
- to compare databases on unconnected SQL Servers
You can create and compare scripts folders only if you are using SQL Data Compare Professional Edition.
For an example using scripts folders, see Deploying a database from source control
What's stored in a scripts folder?
- Object creation scripts
A .sql file containing creation scripts is saved for each object in the schema when the scripts folder is created.
- Static data scripts (optional)
A .sql file containing INSERT scripts is saved for each table when you deploy to the scripts folder.
- Database and schema metadata
A .sdcs file is saved for each table when you deploy to the scripts folder. These files contain index information that enables SQL Data Compare to compare static data.
The file RedGateDatabaseInfo.xml contains information about the objects in the schema, the SQL Server version, and the collation of the database from which the scripts folder was created.
You are recommended not to delete or modify these files.
Creating a scripts folder
To create a scripts folder:
- On the Data Sources tab of the Project Configuration dialog box, select a database, backup, or existing valid scripts folder as the source.
- Under Target, select Scripts folder.
- In the Scripts folder box, browse to or select the folder you want to use.
If the folder does not contain valid scripts folder metadata, the option to create schema scripts is enabled.
- Click Create Schema Scripts.
The Create New Scripts Folder dialog box is displayed:
- Under Data source details, specify the details of the source for the scripts folder.
You can create a scripts folder from a database, backup, snapshot, or another scripts folder.
- Under Scripts folder properties, specify a name and location for the scripts folder.
SQL Data Compare automatically detects the case sensitivity of the data source. If you want to override this, clear the Auto detect case sensitivity check box, and select the Treat items as case sensitive check box if required.
By default, SQL Data Compare decrypts text objects in SQL Server 2008 and SQL Server 2005 databases that were created using the WITH ENCRYPTION option. When comparing large databases, selecting this option can result in slower performance.
You can customize the folder structure and character encoding of the scripts folder. To do this, click Scripts creation options. The Edit Scripts Creation Options dialog box is displayed, allowing you to specify the directories in which your database objects and static data are saved.
- When the source is a snapshot, the case sensitivity and decryption options are not available.
- When the source is a scripts folder, the decryption option is not available.
Comparing and deploying scripts folders
When a scripts folder is the target, you can either:
- Create a deployment script to update the database from which the scripts folder was created, or
- Update files in the scripts folder directly
Note that when an object is dropped during deployment, its script file is not deleted.
If a scripts folder is the target, and any of the script files that will be modified are designated as read-only, a warning is displayed. If you click Yes to continue, these files will be made writable so that they can be modified. This may occur, for example, when you are working with a source control system that sets files to read-only status in some situations.
For more information, see Using the Deployment Wizard
More information about scripts folders
This section provides further information about using scripts folders as a data source in SQL Data Compare.
- White space
SQL Server does not always process white space and comments correctly at the beginning and end of the object definition for some objects such as views, stored procedures, and rules. You are therefore recommended to select both the Ignore spaces in object names and Trim trailing spaces options when you use a scripts folder as a data source.
For more information, see Setting project options
When you select a scripts folder as the target data source, SQL Data Compare preserves comments in object types such as views and stored procedures. However, this is not possible for non-textual object types such as tables. Comments that are part of a table definition will be lost when the table is modified and the object creation script updated.