Automating LIVE to TEST DB Refresh?

Hi Michael,

I am putting the finishing touches on full automation of our test refreshes.

In a nutshell, I have a script that stops necessary services, a fairly large SQL script that performs the restore of the backed up databases and performs all of the rest of the work that needs to be done. Finally, there is a script to bring the various services back up.

I'll try to put this together and post in my file area in the near future so you can take a look. I'll post a link here once it is ready.

Thanks,
David 

Hi Michael,

I am putting the finishing touches on full automation of our test refreshes.

In a nutshell, I have a script that stops necessary services, a fairly large SQL script that performs the restore of the backed up databases and performs all of the rest of the work that needs to be done. Finally, there is a script to bring the various services back up.

I'll try to put this together and post in my file area in the near future so you can take a look. I'll post a link here once it is ready.

Thanks,
David 

Hi David,

That sounds great! I'd very much like to see the scripts when they're finished.

Cheers, Michael J

Hi Michael,
Definitely, all the steps has to be implemented in SQL scripts , for instance modification of Environment-specific values in Test after the DB restoration from Live backup.

If interested, to preserve the Environment-specific values I use a separate database (a temporary storage) to where I copy Environment-specific tables  before moving the DB from Live to Test:
T_DEFAULTS
TR_CUSTOM_TAB
GOOESOFT_REPORT
GOOESOFT_APPLICATION
TR_PAHT_CONFIGURATION
TX_USER_GROUP
TX_MACHINE_LOCATION

After the DB restoration I use these stored tables to restore old Test data.

Also, I am attaching a couple of scripts I use:
- RESTORATION OF DB PRIVILEGES for Direct DB USERS(DB DEVELOPERS) ;
- FIXING OF ORPHANED DB USERS.

Thanks,
Simon Basyuk, DBA of Carnegie Hall
sbasyuk@carnegiehall.org