Working with backups
Published 13 December 2022
With SQL Compare, you can compare a backup with other data sources. This is useful, for example, when you want to retrieve the schema from a backup and compare it with your database without running a restore operation or copying the backup from a remote network.
You can only compare backups with SQL Compare Professional edition.
Compatibility
SQL Compare supports:
- native SQL Server backups
You can use backups created with SQL Server native compression (including row-level, page-level and file-level compression).
- SQL Backup backups
You can use backups created with SQL Backup 3 or later. You can use compressed or encrypted backups.
Known issues
- You can't compare compressed backups from SQL Server 2012 or later.
- You can't compare encrypted backups from SQL Server 2008 or later.
- You can't compare backups of databases with Transparent Data Encryption (TDE) enabled.
- When you set up a comparison that uses a backup, SQL Compare doesn't support tables that don't have a primary key, unique index, or unique constraint.
- SQL Compare can retrieve the schema from full or differential backups, but it doesn't support partial, filegroup, or transaction log backups.
- SQL Compare doesn't read the log records of backup files, so if the database schema was modified while the backup was being created, it may not be shown as modified in the comparison results.
Comparing and deploying backups
You can:
- compare a backup with another data source
For more information, see Setting data sources.
- create a deployment script from a backup
When you've selected a backup as the target, the deployment wizard creates a script to update the database from which the backup was created. Backups can't be modified directly.
When you run a comparison using a backup, SQL Compare locks the backup files when it reads them, and you can't overwrite, move, or delete them.
When a backup is the source, and a database is the target, the deployment script will deploy the database from the backup.
For more information, see Using the Deployment Wizard.
