SQL Data Compare 14

Working with backups

You can use SQL Data Compare to compare a backup with other data sources. This is useful, for example, when you want to retrieve the data from a backup and compare it with your database without running a restore operation or copying the backup from a remote network.

If you're comparing two backups, you don't need SQL Server to be installed on your computer.

  • Comparing backups is available only in SQL Data Compare Professional edition.
  • SQL Data Compare can retrieve the data from full or differential backups. However, it doesn't support partial, filegroup, or transaction log backups.
  • When you run a comparison using a backup, SQL Data Compare locks the backup files when it reads them, and you can't overwrite, move, or delete them.
  • SQL Data 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.
  • You can specify a backup as the target; however, note that backups can't be modified.

Comparing and deploying backups

You can:

  • compare a backup with another data source
    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 a backup is the source, and a database is the target, the database can be deployed with the backup.

Compatibility with backups

To use a differential backup as a data source, you must also add the associated full backup.

SQL Data Compare supports:

  • native SQL Server backups
  • SQL Backup backups
    You can use backups created with SQL Backup version 3 or later; you can use compressed or encrypted backups.

SQL Data Compare does not support:

  • Azure SQL Database backups 

When you set up a comparison that uses backup files, SQL Data Compare doesn't support:

  • tables that don't have a primary key, unique index, or unique constraint
  • views
  • computed columns
    However, you can compare persisted computed columns.
  • string representations of CLR types
    Only the binary representation is supported.
  • custom comparison keys
    However, you can select an alternative unique index, or unique constraint.
  • row filtering using the WHERE Clause Editor


Known issues with SQL Data Compare and backup files

  • 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 from Azure SQL Databases.
  • You can't compare backups of databases with Transparent Data Encryption (TDE) enabled.
  • When you set up a comparison that uses a backup, SQL Data Compare doesn't support tables that don't have a primary key, unique index, or unique constraint.
  • SQL Data Compare doesn't support using partial, filegroup, or transaction log backups as a data source.
  • SQL Data 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.

Didn't find what you were looking for?