Compare a database with its backup: what changed since last night?

“What changed since last night?” is a common question after an incident: rows are gone, a value is wrong, a deployment did more than planned. A backup is a snapshot of the answer, but you cannot compare a .bak file with a database directly. You compare the live database with a copy of the backup. This article shows how to get that copy and how to compare.

Step 1: get the backup into a scratch database

Two ways, depending on how much you need.

The whole database. Restore the backup under another name, on any instance that has room for it:

RESTORE DATABASE Sales_Backup
FROM DISK = N'D:\Backups\Sales_FULL.bak'
WITH MOVE N'Sales' TO N'E:\Temp\Sales_Backup.mdf',
     MOVE N'Sales_log' TO N'E:\Temp\Sales_Backup_log.ldf',
     STATS = 10;

RESTORE FILELISTONLY gives the logical file names for MOVE. How to open a .bak file without SQL Server lists what to check first, such as room and version.

Only a few tables. When the problem is in a handful of tables, SQLTreeo Restore reads them straight from the backup file and copies them into a scratch database with Copy to SQL Server…: no restore of the whole database. Copying more than the first 1,000 rows of a table needs a SQLTreeo Restore license key. Restore a single table from a backup has the steps. The tables keep their names, columns, primary keys and clustered indexes, but not their other indexes or foreign keys.

Step 2: compare

With T-SQL, for one table. When the scratch database is on the same instance, EXCEPT lists the rows that exist in one table and not in the other. Run it in both directions:

-- in the backup, not in the live table: rows deleted or changed since
SELECT * FROM Sales_Backup.dbo.Orders
EXCEPT
SELECT * FROM Sales.dbo.Orders;

-- in the live table, not in the backup: rows added or changed since
SELECT * FROM Sales.dbo.Orders
EXCEPT
SELECT * FROM Sales_Backup.dbo.Orders;

A changed row appears in both results, once as it was and once as it is, and you have to match the two by key yourself. EXCEPT cannot compare xml, text, ntext, image, geography or geometry columns, so list the columns you need instead of *.

With SQLTreeo Compare, for many tables. SQLTreeo Compare compares the two databases table by table, row by row, on the primary or unique keys, and shows the changed values side by side. The databases can be on different servers, so the scratch copy does not have to be on the production instance. Choose Compare table data, set the live database and the scratch database as source and target, limit the tables if you want to, and press Compare. Compare table data between two SQL Server databases has the steps.

Run a schema compare first if you suspect that objects changed too, not only rows. It shows tables, columns, procedures and other objects that exist on one side only or differ. How to compare two SQL Server databases explains it.

Putting rows back

The comparison shows what differs; it does not change your data. To put rows back, decide per table:

• A few rows: insert them from the scratch database with INSERT ... SELECT, with SET IDENTITY_INSERT when the table has an identity column.
• A whole table: copy it from the backup with SQLTreeo Restore, choosing to replace the rows of the existing table. Try it on a copy first when other tables refer to it.

The backup is the state at one moment. Rows that were legitimately added or changed after the backup show up as differences too, so read the result as “different since the backup”, not as “damage”.