October 16, 2007 at 6:45 am
Hello
We have production server(client) with database of around 100GB. Every night we need to restore the entire database to another server(master). Master server would be used when process is going on every night on Client server.
Taking Backup of Client is fast but restoring it on Master server takes too long which is not acceptable.
Can you please suggest the best(performace) way to restore the database on another server.
October 16, 2007 at 6:50 am
- how many filegroups / files in the db ?
- how big is the backupfile(s)
- do you copy the backupfile(s) to local before the restore ?
- what kind of disk topology do both servers have ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 16, 2007 at 6:59 am
- how many filegroups / files in the db ? [1]
- how big is the backupfile(s) [90 GB]
- do you copy the backupfile(s) to local before the restore ? [No we copy to one backup server and from there we restore on the master server]
- what kind of disk topology do both servers have ? [RAID 0]
October 16, 2007 at 7:14 am
- [90 GB] : a normal xcopy would also need quit some time to copy it.
- [No we copy to one backup server and from there we restore on the master server] So you restore from a remote bakup file. (meaning as well a serverside effort (restore) as a network effort (datacopy). Can you try to copy local and then restore ? (rar the bak-file and you may end up with 30%)
- Raid 0 So if one disk fails, your sqlserver will be down ! :crazy:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 16, 2007 at 7:46 am
Tend to agree with Johan. If you need better performance, move the file local to the server.
Also, RAID 0 is ok for a dev server, but keep spare disks around. You'll need a matched set for every one in the server or you'll be down while you look for them.
Hmmm, keeping a matched set around? Why not use RAID 0+1?
October 16, 2007 at 8:00 am
We have Raid0 + 7 on MasterServer, Raid0 + 4 on ClientServer
How about copying directly .MDF and .LDF files instead of taking backup and restore. In this case i know that we need to detach the database,for this case we have approach of keeping the application up(live) on other server until the copy of .MDF and .LDF is done from client to master server. Once copy is done on master we can again attached the client server database and flip the application to point client server back.
Please provide your comments on same.
October 16, 2007 at 8:08 am
maybe just a thought :
Maybe you may want to take a look at log-shipping or another kind of replication ?
Check SSC and BOL 😎
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 16, 2007 at 8:38 am
Steve Jones any comments from your end?
October 20, 2007 at 6:36 pm
Or use a product like Idera's SQLSafe (or others) that will compress the backup from 90 G to 15-20 G. Then you can copy faster. The Idera restores are a bit faster than native restores too.
Or point your backup job to put the backup directly onto the destination server to start with, and eliminate the copy step.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply