December 1, 2010 at 9:14 am
So, I've got a VLDB that I'm working with. The size is around 1.5TB. To do a full backup of this database, it takes around 30 hours or so. Currently everything is in 2 filegroups: The Primary filegroup and Indexes filegroup.
This database has several schemas, all which are effectively separated from each other logically. My thoughts are to split the schemas into filegroups. Each schema gets its own filegroup. The tables and Indexes of those schemas would be in their respective filegroup. The Index filegroup would be removed.
Now, my questions is.... if we have file backups of everything, should a full backup ever be done?
This database doesn't need point-in-time recovery that comes with Full recovery model since it's basically just a large data repository, populated by ETL jobs. We specifically want this to be Simple recovery model.
December 1, 2010 at 9:24 am
you database is store into san or standard disk ?
December 1, 2010 at 9:34 am
SAN, and the SAN is configured in such a way that there may be little or not performance benefit from arranging files on different volumes.
From the documentation from our vendor:
"It is also recommended that you abandon any type of storage optimization achieved by putting data files from a single database on different volumes. All I/O for a given volume on the 3PAR InServ is automatically spread across every drive in the array. Any optimizations attempted by spreading data across volumes generates the same result but with more effort."
My main concern is backup and recovery time. I want to reduce that as much as possible. Overall, I'm very pleased with the database's I/O performance for most tasks. Backup and recovery is the weak spot right now and that is mostly likely due to its size.
December 1, 2010 at 9:50 am
into my entreprise i'm use red-gate SQL backup and i'm backup 1 terrabyte into 30 minute other solution is look for snap you san drive and mount snap drive on other SQL machine to perform backup
December 1, 2010 at 10:46 am
30 hours seems a very long time for a backup !! Using BackupExec to tape, our 1 Tb database takes 6 hours. My 180 Gig backup takes 50 min across the network to another server.
December 1, 2010 at 10:52 am
Also you can use the filegroup backups to reduce the amount of backup time.
Thank You,
Best Regards,
SQLBuddy
December 1, 2010 at 11:08 am
What you're proposing won't work because of your recovery model requirement. You'll be able to backup files/filegroups, but you won't be able to restore them.
In simple recovery, the first backup used to restore the database must contain Primary and ALL read/write filegroups. Later restores may only contain read-only filegroups.
So, if you have primary and 4 read/write filegroups and you only have file backups (no full backup) when you go to restore you would only be able to restore primary. The others would fail because they are not read only.
Read up on Piecemeal restores.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2010 at 1:35 pm
Thanks Gail, that is exactly the information that I was looking for.
I will play around with a piecemeal restore with files/filegroups sometime.
The backup time is far longer than expected. Looking at the backup rates I get when going to a local volume, I'm getting approximately 50MB/s which is respectable. That does 1.5TB in roughly over 8 hours.
In my current environment, we're backing up across the network. There's no local storage for backups, not my preference... but that's what we've got here.
December 1, 2010 at 1:59 pm
Robert Biddle (12/1/2010)
I will play around with a piecemeal restore with files/filegroups sometime.
You need full recovery or all-but-primary filegroups to be readonly to do that
In my current environment, we're backing up across the network.
And that would be why your backups are slow.
Suggestions: Get some additional storage. Buy a 3rd party backup tool with compression. The combination will drop those backup times right down. I had a 1TB database on a really crappy SAN (0.75 sec/write sometimes) and with Litespeed we could do a full backup in 6 hours.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply