Setting data sources
Published 21 August 2019
When you create a new comparison project, SQL Data Compare requires information about which two data sources you want to compare, and how to connect to them. You specify this information on the Project Configuration dialog box:
Selecting data sources
Specify the two data sources you want to compare in the Data Sources tab. You specify a source and a target.
The source is the data source that will not change. The target is the data source that will change.
To switch the source and target, click.
You can compare the following types of data source:
Any database you can connect to on a SQL Server.
Native SQL Server backups or Redgate SQL Backup backups. You can specify a backup as a data source only if you are using SQL Data Compare Professional edition.
- Source Control
There are two options for the Source Control data source:
- Direct from source control
A revision of a database from a source control repository.
For more information about getting your database into source control, see the SQL Source Control documentation.
- Scripts folder
A folder containing the SQL creation script files that collectively define the database structure. This is usually organized into a number of subfolders.
For more information, see Working with scripts folders.
- Direct from source control
You can compare any combination of data source types in your project.
Selecting a database
- Under Source or Target, select Database.
- Type or select the name of the SQL Server in the Server box.If you experience problems selecting a SQL Server that is not running on the LAN, for example if you are accessing the SQL Server via an Internet connection, you may need to create an alias to the SQL Server using TCP/IP (refer to your SQL Server documentation for details). You can then type the alias name in the Server box to connect to the remote SQL Server.To refresh the Server list, right-click the box and click Refresh, or scroll to the top of the list and click Refresh.
- Select the authentication method, and for SQL Server authentication enter the User name and Password.If you want SQL Data Compare to remember your password, select the Save password check box.
- In the Database box, type or select the name of the database.To refresh the Database list scroll to the top of the list and click Refresh.
Selecting a backup
- Under Source or Target, select Backup:
- Click Add Backup Set Files to select all the files making up the backup set you want to compare.To specify a network path, type the full path, including the server name, for example:
If any of the files you add are encrypted, the Decrypt Backup Files dialog box is displayed. Enter the password to decrypt these files.
To use a differential backup as a data source, you must also add the associated full backup.
When you have added a backup set, one of the following icons is displayed:
The backup set is valid and complete.
Select the check box to use this backup as a data source.
The backup set you have selected cannot be used as a data source.
This error is shown if the backup is corrupted, or if you have selected a partial, filegroup, or transaction log backup.
One or more files in the backup set is encrypted.
Click the padlock icon to display the Decrypt Backup Files dialog box.
Either the backup set is incomplete, or a differential backup has been added without the corresponding full backup.
For more information, see Working with backups
Selecting a scripts folder
- Under Source or Target, select Scripts Folder.
In the Scripts Folder box, select the folder, or clickto browse to the folder.
You can use a scripts folder as a data source only if it contains a database schema. You can create scripts folders using SQL Compare, and use SQL Data Compare to update them with static data. Alternatively, you can create a scripts folder using SQL Data Compare.
To create schema scripts in a folder using SQL Data Compare:
- 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:
To update the target scripts folder with data from the source, perform the comparison, select the rows you want to include, and then deploy.
For more information, see Working with scripts folders