August 27, 2008 at 1:20 pm
What would be the best approach to restoring the production database to our development server? The production data file is 568GB in use and allocated to 59GB and the transaction log
is currently 530MB but allocated at 78GB. Right now the process take almost a day, what could I dow to cut this time but not impact production?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 27, 2008 at 1:24 pm
Are you using native SQL backups? If so, compression will definitely help. There are some nice 3rd party tools out there that can bring the time and the size of the backup down. Restore time to your dev server should be reduced as well.
I would look at either the Red-Gate or Quest tools as they seem to be the most reliable in my testing. Both are quality so, look at the price point to make your choice. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 27, 2008 at 2:01 pm
They are using ArcServe agent to backup it up
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 8:23 am
Yeah, ArcServ being the tape backup solution would probably be a mite slow in the restore process as well as the backup process. Are they / you backing up directly to tape?
I always recommend backing up directly to disk as the write speed is so much faster and your dbms can be out of backup mode quicker.
Your database is getting to be a decent size at this point and compression using one of the utilities I mentioned would be the way to go. It will save you both time, disk and tape over the long run, which will well offset the costs. Your manager will love you.
If you are not comfortable with 3rd party tools you could upgrade to 2008 which has it built in. 😉 Hey a selling point to management if nothing else.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 9:05 am
Would it gains us anytime to shrink the transaction log prior to the backup?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
August 28, 2008 at 9:11 am
Yes, the less physical space that has to be allocated on the drive the less time it will take. The remainder of the restore process will remain unaffected though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 2, 2008 at 7:35 am
Arthur.Lorenzini (8/27/2008)
What would be the best approach to restoring the production database to our development server? The production data file is 568GB in use and allocated to 59GB and the transaction logis currently 530MB but allocated at 78GB. Right now the process take almost a day, what could I dow to cut this time but not impact production?
If the tlog is using 530 Mb, why allocate 78Gb? Unless you have some major processing going on, this is not what you should be doing (scale it back to 3-5 Gb and monitor use). How often are you doing log backups and how heavy of a work load do you have? If possible, schedule tlog backups frequently enough to keep the log file size to a minimal, acceptable size (whether you backup every 15 minutes or whatever). Have you looked at any thrid party tools for backups/restores? There are several available that will reduce backup/restore times but 70-75% percent and backup files sizes by about 80% (assuming you are indeed on SQL2K or a lesser version, based on forum). RedGate, Idera and Quest each have solutions for larger databae backups (there are others as well). Could be money well spent as opposed to waiting a full day for a restore. And, as stated, backups direct to tape (and restores from) will definitley take more time than the same process to/from a local drive.
-- You can't be late until you show up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply