Working with backups
Published 05 June 2013
SQL Data Compare enables you 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 are comparing two backups, you do not 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 does not 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 cannot overwrite, move, or delete them.
- SQL Data Compare does not 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 cannot 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 have selected a backup as the target, the deployment wizard creates a script to update the database from which the backup was created. Backups cannot 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
You can compare backups from SQL Server 2008, SQL Server 2005 or SQL Server 2000 databases.
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.
When you set up a comparison that uses backup files, SQL Data Compare does not support:
- tables that do not 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
