October 28, 2015 at 12:50 pm
Hi,
I got a request to have every day my Test environment refreshed from production.
In this moment, I copy every night prod backup to test server and then restore the backup in Test.
Copy the backup and restore the database is slow because the database is big. Do you know any faster alternative to backup/restore? I'm open to hear any alternative, even if enterprise edition is requiered.
Regards,
October 28, 2015 at 2:08 pm
WHen you say it runs a long time how long? If it runs all night long and you are asleep does it really matter how long it runs? just curious.
Two things.. are you using backup compression? If not that will greatly speed up the backup and restore. Secondly if it is very large you can write the backup to multiple files at the same time. That will also speed up the backup.
October 28, 2015 at 5:16 pm
Another thing to consider is the speed of your storage you're writing the backup to, copying it to and then reading it from. I've seen SAN volumes perform much faster than local drives for backups.
When you're refreshing test from production, it is just the data you're concerned with or are you also interested in copying all the procedures, functions, views, user security, etc?
October 29, 2015 at 2:22 pm
Hi,
Actually, prod environment has a database with a size of 300GB. Test environment should have every morning 5 refreshed databases from prod.
I mean, now I can:
1 - Backup PROD
2 - Copy backup to Test server
3 - Restore backup to a a renamed TEST1 database
4 - Restore backup to a a renamed TEST2 database
5 - Restore backup to a a renamed TEST3 database
6 - Restore backup to a a renamed TEST4 database
7 - Restore backup to a a renamed TEST5 database
I know that sounds crazy to have 5 refreshed databases every morning, but it's a request from my customer.
October 29, 2015 at 2:26 pm
October 30, 2015 at 3:44 am
No, because databases in Test server are open in "write" mode every morning, so they can be modified. Next day any change could be missed as database should be refreshed
October 30, 2015 at 4:56 am
You could still restore the FULL (i.e. from a file that you now have locally on the server, so no "transfer time", just "restore time") and then a DIFF and/or LOG files (which you have had to copy from the Source server)
Not sure it would be quicker though as the DIFF gets bigger through the week and LOG files may be large depending on what percentage of the DB changes during the day - particularly if there are INdex Rebuilds.
Are you already using compression? multiple files?
In my tests Compressed backup is 80% smaller (and faster to file-copy) and backup & restore times are 40% faster (less I/O)
November 1, 2015 at 4:02 pm
There are couple of ways doing this , you can use Replication or regular backup and restore. With regular backup and restore method take the backup into multiple files either to local or shared path ... to save some time and use the I/O at maximum. and while restoring generate the restore script from SSMS and make it multiple copies ( check the restore paths and filenames and database name ). and this way you can save more time at backup and restore each database individually.
@JayMunnangi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply