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 – Rajesh Ranjan Jul 17 '19 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 Jul 17 '19 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. – Learning_DBAdmin Jul 17 '19 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 Jul 17 '19 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. – user2431585 Jul 18 '19 at 2:13

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.

| improve this answer | |
  • Thank you Ray. I will explore Red Gate tools along with ApexSQL tools. I believe I have the answer. – user2431585 Jul 18 '19 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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