5 TB database backup

Question

5 TB database backup

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

I abhor doing backups and restores over the network, but they do not always fail. I definitely agree that for large databases, you should backup locally onto a separate disk in use by nothing else.

RAID0 will provide the best write performance, but it is also the least fault tolerant. I wouldn't it use it for anything that would be painful to lose.

I tend to lean towards RAID10 myself. You want your array to made of more small disks rather then less big disks. For example, 8 100MB disks would be a lot faster than 4 200MB disks. I also prefer to get only well known, proven brands of disks. One poorly performing disk that you get on sale for half the price will slow the whole system down.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Out RAID 0 drive (actually a group of drives) is used only for short term backups that are written to tape shortly afterwards.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Yeah, I understand that. I'm assuming that recovery from tape is a fairly fast process for you. For myself, the department that handles tape backups has a 2 week SLA on making a backup available from tape.

However, currently my largest database is only a couple hundred GB's, and the largest I've ever handled was 350 GB, so I can and do keep backups locally in addition to tape. If I was dealing with TB+ databases, I might be able to get tape backup hardware of our own approved.

So bear in mind that I am speaking as someone who has a theoretical knowledge of handling VLDB's rather than practical.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

My experience with a 200+GB database has been:

Backup to network share: Over 4 hours, double that if anything else is tying up the database.

SQL Backup to four local drives: Backup finished in 7 minutes, followed by 2 hours of file copying to network share.

LiteSpeed backup to four local drives, compression level 5: Backed up and copied in about an hour.

LiteSpeed Pro has the added benefit of allowing object-level recovery, I have restored individual tables without having to restore the entire database. Like the backup, the performance suffers if restoring from the network. I usually copy the files back to the local drives and restore from there.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

" .. LiteSpeed Pro has the added benefit of allowing object-level recovery, I have restored individual tables without having to restore the entire database. ... "

Interesting !! Very useful ! Can you restore to a different database, in case you don't want to touch the live table but you want to look at the backed up table ?

We use Idera's SQLSafe which has been good, but I don't think it has that feature ... Unless it's in the latest version.

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 6

These failures of large network backup operations (restores can suffer the same issue if being done over the network) are usually due to running the target server out of kernel resources in the pageable area.

The following KB lists a few of the errors the OP may have seen, and some reccomendations to fix them.

http://support.microsoft.com/kb/304101

Myself, I've had to apply the registry fix to some servers when restoring databases over 150GB over the network...

Your friendly High-Tech Janitor... 🙂

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

You need to look at different options to keep your database backups to a reasonable run time.

If your database is multiple files or filegroups, you can backup the individual database files or filegroups in parallel.

If possible, backup to local disk arrays or to SAN.

If you have multiple disk arrays, you can specify multiple output files on your backup, each going to different arrays to get better performance.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

LiteSpeed Pro's object level recovery feature can restore an individual table to a different table, database, and/or server. It can even export a table from a backup to a CSV file.

RichB SSCrazy Eights Points: 9651 More actions · Answer 9

Now I am sold on that product! Will PO today

bnordberg SSCertifiable Points: 5480 More actions · Answer 10

Is this on a SAN? Many higher end SANS will now do Snapshot backups. The SAN disks are momentarily paused with writes going to cache, then a snapshot is taken. HP has a white paper here: http://h71028.www7.hp.com/ERC/downloads/4AA0-3714ENW.pdf Its an expensive option - but hopefully a good one. We use if for some massive DB's.

homebrew01 SSC Guru Points: 55548 More actions · Answer 11

Idera just announced that their SQLSafe backup product also does object level restore. I already have their previous version and am eager to upgrade and try it out.