Validate Database Restore: Beyond Row Counts

Restoring a database from backup is only half the battle; the real work begins when you verify that the restored data is not only present but also consistent, complete, and usable. Row counts alone can mask subtle corruption, missing objects, or logical inconsistencies. This guide walks you through a practical, multi-layered validation process that goes far beyond comparing record counts, covering schema integrity, data consistency, referential integrity, and application-level checks.

What You’ll Need

• Access to the restored database (read and, for some checks, write permissions).
• Access to the original database or a known-good backup (for comparison).
• Database client tools (e.g., SQL Server Management Studio, pgAdmin, mysql CLI) or a scripting environment (Python, PowerShell) with database drivers.
• A list of critical tables, views, stored procedures, and other objects that must be present.
• Optional: a test environment where you can safely run queries that might lock tables or consume resources.

Step-by-Step

1. Check the restore log and database state. After restoring, query the database status. For SQL Server, use RESTORE VERIFYONLY before the restore and check sys.databases for state. For PostgreSQL, check pg_stat_activity and pg_is_in_recovery(). Ensure the database is online and not in a recovering or standby mode.
2. Verify database-level metadata. Compare database properties: collation, compatibility level, file sizes, and file paths. Use SELECT * FROM sys.databases (SQL Server) or SHOW DATABASES plus pg_database (PostgreSQL). Mismatches here can cause subtle application failures.
3. Compare object counts and definitions. Generate a list of all user objects (tables, views, procedures, functions, triggers, indexes, constraints) from the restored database and compare against the original. Use system catalogs: sys.objects (SQL Server), pg_class (PostgreSQL), information_schema.tables (both). Also compare object definitions using tools like sp_helptext or pg_get_functiondef for key objects.
4. Validate table structure. For each critical table, compare column names, data types, nullability, defaults, and identity/sequence properties. A single missing column can break an application. Use INFORMATION_SCHEMA.COLUMNS or sys.columns and script out a diff.
5. Run a checksum or hash comparison on sample data. Instead of row counts, compute a checksum or hash of the entire table or a sample. In SQL Server, use CHECKSUM_AGG(BINARY_CHECKSUM(*)) or HASHBYTES on a concatenated key. In PostgreSQL, use md5(string_agg(t::text, '')) or a similar aggregate. Compare these hashes between original and restored for critical tables.
6. Check foreign key integrity. Run queries to detect orphaned rows. For each foreign key, run a LEFT JOIN to find rows in the child table that have no matching parent. This is often missed by row counts.
7. Test constraints and triggers. Attempt to insert a deliberately invalid row (e.g., a NULL in a NOT NULL column, a duplicate in a unique index, or a value that violates a check constraint) in a transaction and roll it back. Confirm the constraint fires. Similarly, test that triggers fire by performing a benign update on a test row.
8. Validate stored procedures and functions. Execute critical stored procedures or functions with test parameters. Check for errors, missing objects, or permission issues. For SQL Server, use sp_refreshsqlmodule to catch schema drift.
9. Check permissions and users. Compare database users, roles, and permissions against the original. Missing logins or orphaned users can cause connection failures. Use sys.database_principals and sys.server_principals (SQL Server) or pg_roles (PostgreSQL).
10. Run application-level smoke tests. If possible, point a non-production application instance at the restored database and run a set of core workflows (login, create a record, read a report). This catches integration issues that T-SQL or PL/pgSQL checks might miss.
11. Compare data distributions and key statistics. For large tables, compare min/max/avg of key numeric columns, and check the number of distinct values in high-cardinality columns. This can reveal data truncation or partial restores.
12. Verify log and high-water marks. Check identity/sequence current values. If an identity column is at 1000 in the original but 100 in the restored, it may indicate missing data or a partial restore.

Common Mistakes

• Relying solely on row counts. Row counts can match even when entire columns are missing or data is silently corrupted.
• Ignoring schema differences. The restore may succeed but with a different schema version if the backup is from an older version.
• Forgetting to check permissions. Users and roles are often stored at the server level and may not be restored with the database.
• Skipping constraint checks. Foreign keys and check constraints may be disabled during restore; you must verify they are enabled and working.
• Not testing transactions. A database that looks fine on a SELECT may fail on INSERT or UPDATE due to missing triggers or constraints.
• Comparing only a few tables. Critical data might be in a table you didn’t think to check.

Tips

• Automate the process. Write a script that generates and compares object lists, checksums, and constraint checks. Run it on every restore.
• Use a dedicated test environment. Never validate on production; use a staging server that mirrors production configuration.
• Document your baseline. Keep a snapshot of the original database’s metadata and checksums to compare against future restores.
• Check the backup file itself. Use RESTORE VERIFYONLY (SQL Server) or pg_restore --list (PostgreSQL) to ensure the backup is not corrupt before restoring.
• Involve the application team. They know which workflows are critical; ask them to run a regression suite.

Troubleshooting

• Restore succeeded but database is inaccessible. Check that the database is online, not in single-user mode, and that the service account has permissions. Try restarting the database service.
• Object counts differ. Compare the object lists to find missing items. If the backup was taken from a different environment (e.g., prod vs. dev), some objects may legitimately differ.
• Checksums don’t match. Investigate the specific rows that differ by comparing a sample of rows. This could indicate data corruption in the backup or a non-deterministic function in the data (e.g., GETDATE() stored as a value).
• Foreign key violations found. Identify the orphaned rows and decide whether to delete them or fix the parent data. This may require manual intervention or a data repair script.
• Constraints don’t fire. Check if constraints are disabled (e.g., WITH NOCHECK in SQL Server). Re-enable them and run a validation query.
• Stored procedures fail. Look for missing objects or permission issues. Run sp_refreshsqlmodule (SQL Server) to update metadata.

Frequently Asked Questions

Q: Why is row count not enough?
Row counts only tell you the number of rows in a table. They don’t verify that the data is correct, that all columns are present, or that relationships between tables are intact. Two tables can have the same row count but entirely different data.

Q: How do I compare checksums for large tables without locking them?
Use a sample-based approach: compute a checksum on a subset of rows (e.g., every 100th row) or use a windowed function to process in batches. Alternatively, run the checksum during off-peak hours to minimize impact.

Q: What if the original database is no longer available?
You can still validate against a known-good backup. Restore that backup to a separate environment and compare the two restored databases. If you have no baseline, you can at least verify internal consistency (e.g., foreign keys, no NULLs in required columns).

Q: How do I check for data corruption in the restored database?
Run DBCC CHECKDB (SQL Server) or pg_amcheck (PostgreSQL). These commands check physical and logical integrity of the database.

Q: Should I validate every table or just critical ones?
At minimum, validate all tables that support core business functions. For a comprehensive check, validate all tables, but prioritize by data criticality and size.

Q: Can I automate this validation?
Yes, you can write a script that runs all the checks and outputs a report. Many teams integrate this into their deployment pipeline. Consider using existing tools like dbatools (PowerShell) or pg_dump with --schema-only for comparison.

Related

• Fine-Tuning vs Prompting: A Practical Team Guide
• Open Source vs Closed Source AI: Which Is Safer?
• Project Zomboid Error 1: How to Fix It