October 2, 2008 at 9:26 am
Hi I was wondering if anyone has any thoughts on which approach would be better to refresh data in a training database?
Basically this is what we have going, we have two identical databases 1 is the production database which connects to our live app and the other is the training database which connects to the training version of our app. Our users asked to have semi current data(1 week old) so that when we are training a new user the data is semi realistic to what they would see in the live version.
We are thinking of either writing a script to run every sunday to restore the bak (and or) trn files of the production database. Or setup a replication on the major tables that frequently change? The one issue we have with replication could be that some of our tables do not have p keys.
October 2, 2008 at 9:31 am
No question, use the backup and restore method. The replication piece will work but you have the overhead, which would be continual, you have the maintenance of the distributor and changes to the databases can be somewhat cumbersome when items are replicated. All that is avoided when using the backup and restore methodology.
In addition to the negatives of replication, you get to validate your production database backups every week to ensure that they are good should you ever need them. You have no idea how many people assume that backups are good and never do any testing against them and then when they need them find out that they haven't worked right for a while.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 2, 2008 at 9:37 am
ok... I was leaning towards that... just the question is should I restore the .bak or can I restore the transaction log or logs?
October 2, 2008 at 9:54 am
October 2, 2008 at 10:08 am
Don't really understand why are you trying to over complicate your own task?
Since your requirement is stating a "semi fresh, one week old data", just restore a last, 1 week old full backup of your database.
Just keep in mind that if security on training database is different from production, it will be overwritten.
That is the only potential candidate for scripting I can see.
October 2, 2008 at 10:20 am
Well one of the issues is that we want it to be in an automated job which gets the latest backup from say sunday night than to have someone do it every monday morning. All our full backups were created by using a maintenance plan. So with that we need to create a script to figure out the correct file because it'd be like production_db_backup200810022300.bak. It wouldn't be such a pain if the file was called backup.bak or something like that can be hardcoded. The script i found needs to be molded to our needs but it seems to be a good template on how to go about doing it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply