Contents

Compare and synchronize scripts folders in SQL Server

The guide describes the following topics:

• Create a scripts folder
• Compare and synchronize scripts folders
  • Compare and synchronize a scripts folder as the source
  • Compare and synchronize a scripts folder as the target
• Scripts folders synchronization issues

A scripts folder is a set of scripts that represent a database schema, and optionally, it can include data. An object creation script file is generated for each object. These script files can be stored in user-specified subfolders based on the types of objects. Scripts folders can be utilized for:

• Version-controlling databases by storing the script files in a version-control system to track objects modifications
• Comparing database objects on unconnected SQL Servers
• Database reverse engineering

Note

Scripts folders can only be created and compared using dbForge Schema Compare Professional Edition.

A scripts folder stores the following information:

• Database objects that are grouped into folders based on their types in the database, such as Programmability, Security, Storage, Tables, or Views. Each folder contains individual .sql files with creation scripts for each object within that group.
• A folder with static data that contains individual .sql files with INSERT scripts for each table.
• A config.xml file that contains configuration information, including a SQL Server version, the collation of the database from which the scripts folder was created, and the entity, which refers to the database these scripts are associated with.
• A single .sql file with ALTER DATABASE scripts for the whole database.

Note

dbForge Schema Compare for SQL Server can compare scripts folders with different structures or when objects are generated in a single .sql file. It should be noted that scripts folders can be created not only using the dbForge tool but also any third-party tool. However, when synchronizing data, dbForge Schema Compare will write objects according to the folder structure specified in the dbForge tool.

Create a scripts folder

There are two ways to create a new scripts folder via Schema Compare:

• Using the Create Scripts Folder or Snapshot dialog
• Comparing and synchronizing a scripts folder as a target source

Create a scripts folder using the Create Scripts Folder or Snapshot dialog

1. Navigate to the Database menu and select Tasks > Create Scripts Folder or Snapshot to open the Create Scripts Folder or Snapshot dialog.

2. In the dialog, specify a database as a source type of the object, a connection, a database name from which a scripts folder will be created, a scripts folder as a destination type, and a path to the output file name to store the scripts folder.

3. Optional: Select the Include data checkbox to insert static data.

4. Optional: Click Scripts Folder Structure to customize the scripts folder structure and file name templates.

5. Click Create to create a scripts folder containing all database objects.

Compare and synchronize scripts folders

When comparing and synchronizing scripts folders as a Target or a Source, a synchronization script is created. It can be saved to a file or opened in an internal editor.

When a scripts folder is selected as the Source, you can execute the synchronization script directly against the target database or generate a migration script.

When a scripts folder is selected as a target, you can update the scripts folder after synchronization or generate a migration script.

Note

When you need to compare data in a scripts folder, refer to Compare and synchronize scripts folders and static data.

To compare and synchronize a scripts folder as the source

1. On the standard toolbar, click New Schema Comparison to open the New Schema Comparison wizard.

2. On the Source and Target page of the wizard, choose a scripts folder as a source type and a database as a target type.

3. Under Source:

• In the Database scripts folder field, you can do either of the following:
  • From the dropdown list, choose the previously connected scripts folder.
  • Click New to create a scripts folder.
  • Click Browse to select the required scripts folder stored on your machine.
• The server version and default collation are automatically inherited from the current server connection.

4. Under Target:

• From the dropdown list, choose the previously established server connection. Alternatively, select Manage from the dropdown list to open the Connection Manager and create a new connection.

• Choose a target database to compare.

5. Optional: On the Options page, you can select additional options to customize the default comparison process.

6. Click Compare to run the comparison process. The Schema Comparison progress window opens, showing the stages of the comparison process.

7. In the Schema Comparison document that opens, select the objects to compare and click Synchronize data to the target database to open the Schema Synchronization Wizard.

8. In the wizard, select the way you want to manage a synchronization script and click Synchronize.

To compare and synchronize a scripts folder as the target

1. On the standard toolbar, click New Schema Comparison to open the New Schema Comparison wizard.

2. On the Source and Target page of the wizard, choose a database as a source type and a scripts folder as a target type.

3. Under Source:

• From the dropdown list, choose the previously established server connection. Alternatively, select Manage from the dropdown list to open the Connection Manager and create a new connection.

• Choose a source database to be compared.

4. Under Target:

• In the Database scripts folder field, you can do either of the following:
  • From the dropdown list, choose the previously connected scripts folder.
  • Click New to create a scripts folder.
  • Click Browse to select the required scripts folder stored on your machine.
• The server version and default collation are automatically inherited from the current server connection.

5. Optional: On the Options page, you can select additional options to customize the default comparison process.

6. Click Compare to run the comparison process. The Schema Comparison progress window opens, showing the stages of the comparison process.

8. In the Schema Comparison document that opens, select the objects you want to compare and click Synchronize data to the target database to open the Schema Synchronization Wizard.

9. In the wizard, select the way you want to manage a synchronization script and click Synchronize.

Scripts folder synchronization issues

• Certificates, symmetric keys, and asymmetric keys are not supported when you use a scripts folder as a data source.

• When you select a scripts folder as a target data source, comments that are part of a table definition will be lost when the table is modified and the object creation script updated.

• You can use the scripts folder comparison feature to copy or transfer database data. To move data, it’s required to utilize the scripts folder feature provided by dbForge Data Compare. To learn how to do it, see Compare and synchronize scripts folders and static data.

• We recommend you enable the Ignore spaces in object names and Ignore white spaces options on the Options tab when you use a scripts folder as a data source. The fact is that SQL Server doesn’t always process white spaces correctly at the beginning and end of the object definition. There can be issues for objects such as views, stored procedures and functions, triggers, user types, defaults, and rules.

Note

To ensure scalability and reliable backups, you can use Microsoft Azure Files and/or Microsoft OneDrive as a storage for your scripts folders.

Want to Find out More?

• Previous
• Next