Once you restore the backup, how do you verify that the source and destination are in sync (schema, datatypes, number of records etc)? assuming that the source has not change. Appreciate your insights.

  • You can use Apex SQL Compare to a database schema with another database It's free to download. apexsql.com/sql-tools-diff.aspx Commented Jul 17, 2019 at 4:22
  • If no errors/warnings during both backup and restore then no reason for the source and destination are not in sync. Exclusions - the problems external to server like OS, software or hardware problems, viruses, etc.
    – Akina
    Commented Jul 17, 2019 at 5:18
  • Did you face any issue(difference) after restoration between source and destination? if no error/warning and backup and restoration process completed successfully, I don't see any possibility of difference. Commented Jul 17, 2019 at 5:50
  • You can generate an entire database script and include schema and data against both databases. Just make sure to disable all commenting that will generate date and time. Then perform a file compare between the two scripts.
    – Queue Mann
    Commented Jul 17, 2019 at 20:32
  • Thanks Rajesh. The tool looks good for my scenario. I will explore ApexSQL Diff as it can help verify the entire DB at one go. Commented Jul 18, 2019 at 2:13

2 Answers 2


Red Gate tools include both schema and data compare utilities that can be run against a "source" and "target" database. These tools provide detail comparisons and can generate scripts to make A = B or B = A.

Any Server Specific items (e.g. SIDs in sys.Database_Principals) will be different.

  • Thank you Ray. I will explore Red Gate tools along with ApexSQL tools. I believe I have the answer. Commented Jul 18, 2019 at 2:15

Once the backup is restored to target DB, it will be reflecting the state of source DB at the time of backup. From my understanding, restore is almost a drop and recreate of DB in target server. Normally, if Prod DB is restored to a non-prod - logins/Users accounts may be different, or if any alerts are configured to make any data changes thru triggers/SP's once a DB is restored. Otherwise, there is no possibility of difference. Even if you restore DB on a server with a different collation, it works unless you create some tempdb tables and try joining them to the restored DB. Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.