5 TB database backup
-
Does anybody have experience how (what to use) to backup 5 TB (or something like this) database? I tried Sql Backup 5 (Red Gate), LiteSpeed (Quest)- no success. Both started not bad but after (as a rule) 1 TB processed backup rate came down and after couple days I stopped backup (can not wait longer and log file grew up significantly). Forgot to mention this is network backup over 1Gbps card. Thanks in advance
-
check out Evault or Avamar which is now part of IBM or EMC. Forget which one.
is this on HP hardware? if yes you can look at the stats on the NIC card to see if there are errors. I would also put both servers on the same switch and make sure you have the latest drivers.
what kind of hardware are you using? we have HP servers and only buy HP branded hardware except for Emulex HBA's to put in there
-
network backup is your issue - backup to fast local disk ( or local san ) . more spindles and raid 10 will give performance. I can't give exacts because so much can be different but I used to backup around a TB in about 45 mins/hour using litespeed
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks guys for your answers. My main concern regarding local backup is not only extra 5TB (or less in case of compression) of space but mainly overhead from backup software running on production sql server. Colin: did you stop all activity on sql server when reaching this 1TB/hour backup rate or you have done this in parallel? Thanks
-
backups can slow performance, my preferred route always for backups is thus:-
- Backup to fast dedicated disk using native or compressed ( litespeed etc. )
- copy backup to backup repository , essentially another server with lots of disk space ( use robocopy for this )
- backup to tape from repository
- I use this with either/or/both DAS and SAN
I've found it's best to keep any backup software of any type away from sql server servers. ( with exception of C: drive system / registry backups as per what the system guys want ) All the sql backups I stream to another server and backup from there.
I've found log backups causing contention when being backed up to a raid 5 and I've found issues with direct to tape backups. I usually try to choose a quiet time for backups - getting things to happen quickly e.g. backups, allows better planning of your available window. I have sometimes put databases into dbo mode for backups but lately I've been working with systems which are almost 7 x 24 so this hasn't been an option.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks guys for your help
-
Can you qualify that? "will always fail after it reaches around 30%"
A Nas is a network link so contradicts your statement.
We used to backup 8TB to tape (Robot) which took around 24 hours. We used Veritas. Not the best option...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
well I have seen backups to network shares, not though the gui. It's not something I like but some sites I've worked use this on a regular basis and for simple shipping to a reporting server and for basic DR.
I guess a NAS will work, the problem is you're shipping data by tcp/ip and whilst dropped packets are a way of life on a network they're not very good for sql backups, that's why FC is so much more reliable, even though it's still a network.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Is it possible to split the data/indexes into different filegroups and perform separate filegroup backups each night ?
-
Yes it is possible to split data and its indexes on different filegroups but these filegroups have to be backed up together.
-
SQL Server desperately needs a backup product similar to the BMC DB2 backup, which includes a feature called 'Offline backup merge'. This allows you to take a full backup and some log backups, and produce a new full backup for any point in time within the log backups. The great thing about it is that the merge can be done on any server, then it logs on to the target server to record the backup just as though it was done locally. I know a lot of DB2 shops that now hardly ever do a real full backup, they just keep merging the log backups.
BMC (or any other vendor!) - are you listening?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Though I don't really like it, all our initial backups are first native and over the network and they do not fail.
Terry
-
You're always going to have to pay the price of the backup process. Idealy you want this process to finish as quicly as possible. You can't get any faster than local disk and write ahead cache. Compression exchanges cpu for IO's, thats an easy one.
-
Our DB is only 300 G, but we use Idera and backup to a local RAID 0 drive for performance, then put that to tape.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply