September 8, 2010 at 3:11 pm
Hi,
I was just approached to comment on the strategy to backup a database with initial size 2 TB and grows by 15 million rows a day.
I never delta with this size before. I have been doing some research and found different voices. Most of them point to adding hardware which is a constraint. We cannot ask for new hardware at this point.
You advise is much appricated.
Thanks
September 8, 2010 at 3:25 pm
Hi Shriji,
Use multiple filegroups for the VLDB. Leave the Primary File group for the system tables and create additional file groups for user tables and indexes.
Then you can take Filegroup backups which can enable you to spread the filegroup backups on to different times and also with SQL Server 2005 we have online restore for the filegroup backups.
Thank You,
Best Regards,
SQLBuddy
September 8, 2010 at 5:46 pm
How long does it currently take to backup?
What is your maintenance window for backups?
The first thing I would look at is buying a third-party backup utility that compresses the backups and allows you to backup faster. SQL Backup (Redgate) and Litespeed (Quest) are two of the better options.
If that still doesn't get you to the point you need - then you need to invest in hardware. You'll need to add multiple HBA's and multiple LUN's so you can stripe the backups across the multiple LUN's through separate HBA's. As well as making sure your data files are accessed through dedicated HBA's and on separate LUN's also.
You can look at separating your data across multiple files - and doing filegroup backups, but you really need to make sure you understand this fully because you need to make sure everything is separated across the different filegroups appropriately to be able to support your restores.
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
September 9, 2010 at 8:21 am
Using a 3rd party tool like LiteSpeed is probably the answer for you.
We have a 1.3TB DB that used to take about 2.5-3 hours using the native backup. With LiteSpeed we are doing (a now much bigger) backup in about 1 hour flat. We are also using 4 backup files to speed up the whole thing and not having to move 1 big file across the network for backup purposes, etc. The total size of these 4 files is about 230GB.
Additionally restore time is also cut down to about 2-2.5 hours from about 4 hours.
September 9, 2010 at 3:07 pm
Thank you guys.
This is first project of this size for us. We do not have any stats yet. We are changing back end from Oracle to SQL Server for a given application. We do have LiteSpeed and yes it does make life very easy.
We are supposed to discuss the backup/restore strategy in the meeting and wanted to take advise from the experts from this forum over the top my own research.
I will go through the information. thanks for your time.
September 10, 2010 at 4:10 pm
About 5 years ago, when my main Prod database was about 1.5TB (on SS2K) we were using the following:
LiteSpeed for backup and restore.
Tran Logs Dumps every 15mins.
Diff backups every morning.
Full backup every Saturday night.
Backup Set consists of 3 files and total time to take a full backup took from 1.5hr to 2hrs
September 13, 2010 at 4:53 pm
A lot of it depends on what you have as your backup infrastructure (tape..disk to disk..SAN/LUNs..etc)
I would have you think of a product from NetApp thats called snapmanager and it takes a "picture" of the system and goes from there. I truely cant explain it in detail but i do know its very fast and complete in its working. I have plenty experience with using it just dont know the bits and bytes of it (my apologies) also one thing with the NetApp SnapMirror software is there are NO incremental backups as everything is full backups because they happen so quickly. Hope this helps..and good luck =]
DHeath
September 14, 2010 at 7:30 am
I'll throw in my vote for redgate SQL backup or litespeed as well. I have several DBs of aroun 7TB which all compress to well under 1 TB each AND can be backed up over the network or to local drives.
You may also seriously look into migrating to SQL 2008/ 2008 R2 Enterprise for table compression. With table compression turned on, although you can expect slightly higher CPU use, you will see much better I/O due to less data being moved on the physical disk.
September 14, 2010 at 11:42 am
The last time I checked Litespeed did not do command line filegroup backups this was a few years ago.
September 15, 2010 at 10:12 am
Hi
if you don't have access to the tools like Redgate's Sql Backup or LiteSpeed and you are running Enterprise Edition of SQL Server 2005, then you may consider doing filegroup backups and implementing a piecemeal restore strategy. This will reduce the time that you spend running backups on your VLDBs. Please see http://blogs.msdn.com/b/sqlcat/archive/2005/10/27/how-can-sql-server-2005-online-piecemeal-restore-improve-availability.aspx and http://searchsqlserver.techtarget.com/tip/Piecemeal-restore-with-SQL-Server-2005 for some insight into this type of backup strategy.
If the older data is not needed online in production then the alternate is to implement table partitioning, where you archiving the data periodically to an archiving Db. This Db can be located on cheaper storage or taken offline as backups onto long retention tape storage.
November 9, 2010 at 7:05 am
hi
I Have 2 TB database(SQL SERVER 2005) could you help me what will be best backup policy (how to take backup of huge database) and what time i take the backup with best policy
November 9, 2010 at 7:07 am
Hi Sir
what new in sql server 2005 for backup
November 9, 2010 at 7:12 am
rajanishvibha 3763 (11/9/2010)
Hi Sirwhat new in sql server 2005 for backup
In this forum --> go SQL Server 2005 --> Backups-->Add Topic
-->then finally ask your queries
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 9, 2010 at 7:42 am
muthukkumaran (11/9/2010)
rajanishvibha 3763 (11/9/2010)
Hi Sirwhat new in sql server 2005 for backup
In this forum --> go SQL Server 2005 --> Backups-->Add Topic
-->then finally ask your queries
And try to be a bit more specific with your question. Page 1 had some good posts, so read those, then ask about what you don't understand.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply