Restoring database - Best practice?

ofrede

I have my test-database in SQL Source Control which works fine.

Sometimes (about once each three months - or if I need live data) I backup my production database and restores it in my test environment. This is only done right after I have made a schema compare so I know I wont overwrite any development progress.

After the restore my SQL Source Control "thinks" that each and every item has changed - and to clean that up, I commit my db to source control.

This does not seem to be best practice in my world! The only other way I can think of right now is to make a full data compare between the two databases and then copy over data. As the database is rather big - and making a data compare on the prod database does take a while, I would rather try to avoid that!

Any suggestions on how to:
1: Avoid having to commit my whole database to the Source Control server
2: Avoid having to make a data compare on prod server

(Only suggestion I can come up with is: Backup prod database. Restore as new database in test and make a data compare between new test database and real test database. Then delete the new test database again).

David Atkinson

What you should be able to do is to unlink the database from source control in the Setup tab, and relink it immediately. This results in SQL Source Control offering you to resolve what is now a "conflict" situation, which means you can now choose to do a "get latest" rather than a commit, which I believe is what you're trying to do. You will have to visit the Get Latest tab, Ctrl-a on the grid to select all items and select the "take theirs" radio button. Clicking "get latest" now will update your restored database to be the same as the latest in source control.

I hope this helps. It's certainly not intuitive and the behavior could indeed be improved.

David