0

I've got a copy of our 130GB production database restored on our development SQL Server 2008 R2 Standard Edition 64-bit.

I'm developing some test scripts, which need to de-duplicate a lot of data that has been created in error, so I've got a copy of the database in order to develop and test the de-duplication scripts.

The full restore just took 45 minutes and I want to be able to test my script, then quickly restore the database back to the initial state, then fix any issues with my script and re-test the improved database script.

Since I'm not on Enterprise Edition snapshot backups is not available to me.

What backup and restore strategy could I use to prevent this 45 minute bottleneck? If there is one available, please can you suggest a script for the backup and restore?

Improve this question
4
  • 3
    Why don't you just run your scripts in a 'transaction' and rollback the transaction if you find errors in your logic. You can still select and evaluate your results in the same SSMS window as your update scripts.
    – Scott Hodgin - Retired
    Aug 22, 2016 at 12:14
  • Another alternative is to save off the underlying mdf, ndf and ldf files from the restore database to another folder. When you need to get a fresh copy of your database, simply detach the existing database, copy the saved files over the existing files and reattach.
    – Scott Hodgin - Retired
    Aug 22, 2016 at 12:18
  • @ScottHodgin I can't do that sadly. The amount of data being affected is too large. To able to be performant I'm already having to temporarily drop all the indexes and FKs on the affected tables. It also uses a transaction inside the script to chunk through (while loop) 10k of records at a time, deleting from a view wrapped around a CTE.
    – Rebecca
    Aug 22, 2016 at 12:19
  • 1
    You could install Developer edition on your local machine and do your development there? Then you have access to snapshots.
    – Martin Smith
    Aug 22, 2016 at 12:22

1 Answer 1

Reset to default
1

Since you are limited by Edition, I agree with @MartinSmith that you can use Dev edition (to create snapshot and revert when you have to rollback) since you are only testing and no one is connecting or using the data except you - provided you have enough space and memory. Also, I assume that 130GB is compressed backup !

Also, restoring a 130 GB database takes 45 mins .. you need to check if Instant file initialization is enabled or not (should be enabled). Also, you can play with BUFFERCOUNT and MAXTRANSFERSIZE parameters.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.