How to verify the restored MS SQL DB from Backup matches with the source? 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.
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.