March 18, 2011 at 5:43 am
Hi,
I will soon be involved in testing the restore of a 6TB database.
We will be using Litespeed and I will be initially restoring the last fullback to a test server.
What is the best way to copy the backup file from the Live server to the Test server? We do not want to copy it across the network if possible.
Thanks,
George
March 18, 2011 at 6:40 am
The only thing I can think of with files that size would be compress, copy to a big portable drive (or more than one), and move it that way. That might be faster, too. Even Gigabyte ethernet isn't as fast as the data bus to an external eSATA connection.
Alternatively to a portable drive, how about a shared NAS/SAN location? Then you don't have to move it at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 1:37 pm
Thanks for your response G-Squared.
We have decided to go with your suggestion and compress, copy to a portable drive, and move it that way.
Cheers,
George
March 22, 2011 at 2:05 pm
Cool biz. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 2:33 pm
Just a quick warning but I have had those portable drives crash a server before so, test on a non-production box to see how it responds to connecting that up.
As for network copy, being that you are at 6 TB I'm guessing that will compress to about 1 TB. That being said your copy could be about 5 hours. Rough guess. Robocopy might be a good choice if you decide to go that way. Not pretty but functional.
And as GSquared stated if you have the ability to copy via the SAN, etc. that is always best.
Copy time assuming decent network connectivity - Edit...
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 23, 2011 at 6:40 am
What is the best way to copy the backup file from the Live server to the Test server? We do not want to copy it across the network if possible.
Thanks,
George
If you are not backing up to multiple files, you can gain some benefits by doing so. The SQL CAT team has a blog post here on some of the benefits of multiple backup files. Also, having multiple files is just more "scalable" in terms of compression, copying to portable drive(s). etc.
March 23, 2011 at 8:57 am
I'm in the same situation, using Litespeed, needing to refresh the dev dbs at corporate from the dbs in the datacenter.
I'm on SQL2005 so to get any compressed backups I need to use Litespeed compression (zipping isn't an option for me) . Using Litespeed compression level 1 to get good size reduction without chewing up all CPU:
*500GB db compresses to 46GB
*4TB db compresses to 1TB
*Your actual compression depends on the content in your database, so your 6TB may not follow my compression ratios.
After this, it all depends on your network. On my network, I can get 17GB copied in a 24-hour window. So, I have no choice but to use the portable drive on the big db. On the smaller db I can use the portable when in a rush, or use Litespeed to make multiple files instead of one big one, so if the network hiccups during copy, only one smaller piece is interrupted and I haven't lost all my copy time. It will take a few days but I can start the copy and check on it from time to time.
I don't have Litespeed on the dev machines so I use create the Litespeed standalone EXE.
The part to note with the EXE on a Windows machine is that it chokes on files > 4GB, so the bigger the database the more smaller files you'll have. I size my files to be 3.5GB. Take your full backup size, divide by 3.5GB, and that's the number of files to specify on the backup.
Key parts:
* use Litespeed EXE
* use Litespeed backup compression, level 1
* split the backup into multiple files, each file under 4GB
March 23, 2011 at 1:17 pm
I had a similar requirement last year, but with the added complexity of requiring encryption of the files to protect them in transit between 2 server facilities (LAN bandwidth was at a premium)
We used Red-Gate SQLBackup, but Litespeed has similar capabilities
Some lessons learned the hard way and from hindsight are:
1. Compression is good and the file will be much smaller as stated, but backing up to the local drive and copying it to an external drive can be problematic. Windows seems to require a huge chunk of memory to copy large files (never did get to find out exactly how much). The SQL Server was in a shared resource environment and just didn't have access to that much RAM. We ended up backing up the DB to a drive on another physical server in the same data centre and then using that server to copy to the external drive. A real pain and a lot of wasted time staring at the file copy progress dialog.
2. Using striping when backing up (I believe that Litespeed has this capability) creates a batch of smaller files (up to 32 files from Red-Gate). These will be much smaller and will copy directly to the external drive easily. In fact, for the second iteration of this process, we actually managed to copy these files over the network in small batches without causing network contention and saved a lot of hassle with a physical transfer. Just wish I'd thought of this sooner, but sometimes you can get just too close to a task....
Andy
March 24, 2011 at 7:19 am
We have about 100 old 5.25 inch floppy disks. Let me know if you need some. 😀
March 24, 2011 at 9:54 am
Hi all,
Thanks for all your comments. They are much appreciated. We will be doing the restore using Litespeed on a non-production test server, and being the pessimist that I am, I have warned that it will probably take a number of hours (if not longer) to complete.
At the moment we are doing it as a test run to see how long the restore will take, and what works and what doesn't.
I will be implementing a number of the suggestions provided in your posts, and also being wary of some of the pitfalls that occur.
The work is taking place next week so I will let you know if anything unusual occurs.
Thanks,
George
March 24, 2011 at 9:02 pm
I have a 1.2TB database that I restore nightly to a report server. The backups are made with Litespeed and we compress to a bit less than 200GB.
Instead of copying the file, we put our backups on a BCV (Business Continuity Volume) on our EMC (DMX) SAN. With this in place and the BCV volume properly configured, I can run a split operation on that BCV. Once split, I can then mount the volume on the report server and start restoring the databases. Once the restores are complete, I then unmount the volume and establish the BCV so it can synchronize.
Check with your SAN team - and vendor to find out what options are available. If we wanted to purchase the additional module for SQL Server, we could use the BCV on the actual data and log LUN's. In that scenario, we would be able to set it up so all we had to do was detach the databases, delete the existing LUN's on the report server, present a new copy of the BCV to the report server and attach the LUN's.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2011 at 6:49 am
Outside of having a SAN feature available to allow you to copy/replicate/present this backup file over to the other server SneakerNet is almost certainly your best option. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 25, 2011 at 1:05 pm
Just to share my experience- month ago I did this: 1) DB size- ~5.5 TB; 2) backup file size- ~1.3 TB (Red Gate Sql Backup 5, compression level = 2); 3) coping (Xcopy) file to network test server- ~24 hours; 4) restoring DB- ~48 hours. Good luck!
March 25, 2011 at 1:15 pm
Try eseutil /y option to copy large files. I use this regularly to copy large backups across. this worked faster than xcopy for me. http://technet.microsoft.com/en-us/library/bb124326(EXCHG.65).aspx
April 4, 2011 at 4:23 pm
I don't know if this is feasible in your environment but thought it was worth mentioning.
Instead of copying large backup across network, can you instead backup the source database to the server where you want the restore to happen, assuming its a compressed backup. I know doing that will result in longer backup times but atleast the the restore will be faster and the overall times will be faster due to the fact that we are cutting down on the large copy operation and the restore will be a local DB restore.
Just a thought!
Amol Naik
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply