Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2002-03-08 : 11:35:39
|
| Prior to re-inventing the wheel I thought I would check and see if anyone else has encountered this issue before.For starters we have a development AND production environment. I know some of you are jealous already. The business that is conducted is fairly complex involving hierarchical relations that get real ugly very quickly. Here's the issue: When testing in development we will often times change the data in the entire development environment (for example someone in the hierarchy leaves and everyone up and down the tree will have various numbers altered as a result). Getting the database back to 'pre-testing' condition or a pre-test snapshot is the problem. Short of taking the production database and doing some kind of copy/backup-restore/etc solution we decided that there are certain tables that will need to be routinely refreshed. The tables would have to be truncated and the data loaded from production. They have foreign key constraints (jealous again) that need to be maintained on identity columns. This can be solved by using identity insert on and recreating the constraints after the load. I envision using a DTS and table based solution for something like this. Something like a table that has all the production tables that need to be 'refreshed' and the DTS package that reads that table and does the 'refreshing' which includes truncating, inserting rows, recreating FK constraints, etc. Has anyone done this before or something like it? If so, pointers, suggestions, warnings? Perhaps an off-the-shelf software package that does this?Thanks in advance for consideration |
|
|
Jay99
468 Posts |
Posted - 2002-03-08 : 11:50:14
|
| You solution should work fine.If you roll-to-prod process involves a roll script (always a good idea to document your release), then you could periodically backup/restore production over dev and then run you roll script onto dev, thus bringing you back to your release candidate. This process, however, requires that your team keep the roll script up to date, otherwise there development will be lost (except you backed up dev before you rolled prod over it, right?).jayJay |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-03-11 : 17:46:13
|
| James,What about this idea... When your DB is in the state you wish to save, Detach the development database (sp_Detach_db) and make a copy of the file-system files (.mdf, .ldf, etc.) Then re-attach the database (sp_attach_db). Do your testing, and when you want to roll back... Detach, copy in the .mdf/.ldf files, re-attach the newly rolled-back files and I think you're back in business.------------------------GENERAL-ly speaking... |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-03-11 : 18:48:42
|
| Thanks for the suggestions. As to the detach and reattach solution our organization is very tightly secured and the job duties are very specialized. Translation: developers can't touch anything like a backup file let alone reattach it. Security is a great thing until you want to develop in the environment. What we are going to do is have a table that lists potential tables to copy from production. In VB code loop through each table and do a DTS transformation of the data. Once done it will be nice because all we have to do is specify which tables are "active" candidates for copying and run the program which will only copy over "active" tables. This way if we seriously hose just a few tables in development testing we don't seriously hose the whole server copying the entire production environment to the development environment. It could be worse though. I have worked at places where we develop against our production database and getting it back to a previous state involves restoring a backup. Ick.Thanks for the help. |
|
|
|
|
|
|
|
|
Prior to re-inventing the wheel...