Restoring database - Best practice?
ofrede
Posts: 11 Bronze 1
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).
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).
Comments
I hope this helps. It's certainly not intuitive and the behavior could indeed be improved.
David
Product Manager
Redgate Software