0

I'm trying to save some time on a repeated task I keep getting asked to do.

We have an internal application we'll call "App".

This App has a database called "AppLive"

We have a "Test" instance of the same application, which uses the Database "AppTesting". This is so we can perform tests etc without affecting our live data.

When the users want to start playing with the Test Instance of the application, I am asked to "import the live data into the test database". They need the most current set of live data from our AppLive Database imported into the AppTesting database.

My current process is this:

Take the most recently nightly full backup of the live database. Delete the test database "Restore" the AppLive backup file into a new database called AppTesting (same name as deleted database).

This gives us the most recent nightly data into the Test database.

The problem with this is that the database is getting huge. It's about 11GB - and the backup files are remotely stored on a NAS drive.

So I have to wait 45 minutes to transfer the database backup file onto the SQL server, and then begin the import. Which again takes an absolute age.

My ideal solution to this would be:

Continue to do my nightly full backups of AppLive database (for our normal backup requirements) and for an incremental backup to me made in addition to this - so that when I have to import live data into our Test database I don't have to play with the huge 11GB file. Ideally I'd like to be able to say to MSSQL management studio "please update AppTest db with all new data from AppLive" - I don't want this scheduled though, the Test data has to stay static until we're requested to update it with the new data.

I hope this is clear enough and that someone is able to point me in the right direction.

1 Answer 1

0

You have a couple of options.

1) Write a powershell script or something to automate the restore. This turns it into a fire and forget. You could then choose to combine this with a scheduled running of the tool (say on Sundays) into a third database. This would mean sitting down with the consumer of the test database to figure out what their tolerance for stale data is (i.e. how fresh does the database need to be). Then, when the request comes in to refresh the data, you drop the current testing database, rename the restored into copy (you'd probably want to rename the physical files as well) and you're good to go.

2) Log shipping

3) Database mirroring.

Options 2 & 3 seem like overkill for what you're trying to accomplish.

1
  • The scripting into a third database, and renaming it when needed idea is probably genius. Thanks for the tip!
    – CraigE
    Oct 2, 2012 at 7:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .