Working with snapshots
Published 21 August 2019
SQL Compare enables you to create a snapshot of a database and compare it with another data source. A SQL Compare snapshot is a binary file containing information about the structure of a database; it does not contain any table data.
You can use snapshots:
- for simple version control of databases
- to compare databases on unconnected SQL servers
Creating snapshots
To create a new SQL Compare snapshot, on the File menu, select Create snapshot. Alternatively, on the Project Configuration dialog box, select a snapshot as one of the data sources, and click Create.
The Create New Snapshot dialog box is displayed:
Under Data source details, specify the details of the source for the snapshot. You can create a snapshot from a database, backup, scripts folder, or another snapshot.
Under Snapshot creation properties, specify a name and location for the snapshot.
SQL 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 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.
- 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
You can also create a snapshot using SQL Snapper, a free tool that creates SQL Compare snapshots from SQL Server databases. SQL Snapper is distributed with SQL Compare and can be found in the main installation directory as RedGate.SQLSnapper.exe
Comparing and deploying snapshots
You can:
- compare a snapshot with another data source
See: Setting data sources.
- create a deployment script from a snapshot
When a SQL Compare snapshot is the target, deployment creates a script to update the database from which the snapshot was created. Snapshots cannot be modified directly.
When a snapshot is the source, and a database is the target, the deployment script will deploy the database with the snapshot.
This is useful, for example, if you want to roll back changes. If you have made changes to a database, and created a snapshot before deployment, you can then set the snapshot as the source, and the database as the target, to roll back the changes.
For more information, see: Using the Deployment Wizard.
Compatibility with previous versions of SQL Compare
Snapshots created using SQL Compare versions 3, 4, 5, 6, or 7 can be used in this version of SQL Compare.
However, if the Add WITH ENCRYPTION option was selected when you created a snapshot using SQL Compare version 3, the comparison or deployment may fail when you use the snapshot in this version of SQL Compare.
