Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup and restore Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 11:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 21, 2013 1:01 AM
Points: 82, Visits: 124
Hi

which backup and restore strategy used for the database size above 2TB.



Post #929458
Posted Sunday, May 30, 2010 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 01, 2010 11:17 AM
Points: 2, Visits: 7
I assume that your Server is SQL Server 2005 -

I would suggest -
Full database backup - Once a week
Differential Backup - Everyday night (After prod hours)
Transaction log backup - Every 15 mins. (Depends on your application and criticality)

If you can upgrade to SQL Server 2008 that would be better - as you would get backup with compression option.


Post #930156
Posted Sunday, May 30, 2010 10:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
viswanath09 (5/27/2010)
which backup and restore strategy used for the database size above 2TB.


I would really hope that if you have 2 TB databases, you have a senior DBA who's more than capable of answering that question.

There is no way, from the limited (non-existent) information you have given to answer that question. It depends on what your daily and weekly maintenance windows are, it depends what your maximum downtime and data loss SLAs specify.

If you're managing a DB that size and don't know how to plan a backup strategy, and have no one there there that is capable of doing so, I would recommend you get a consultant in to help you out with planning one and teaching you.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #930165
Posted Monday, May 31, 2010 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 AM
Points: 10, Visits: 75
It completely depends on your strategy.If resources are not a constraint a Weekly full and followed by daily differential is enough. Also you should possibly look at table partitioning and further look into file group backups but that needs bit of planning...

For File Group Backups and Table Partitioning Please refer
http://msdn.microsoft.com/en-us/library/ms179401.aspx
http://www.databasejournal.com/features/mssql/article.php/3640891/Data-Partitioning-in-SQL-Server-2005---Part-II.htm


~RD


~RD
Post #930403
Posted Monday, May 31, 2010 11:06 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:17 AM
Points: 4,379, Visits: 9,470
rahul_dhar (5/31/2010)
It completely depends on your strategy.If resources are not a constraint a Weekly full and followed by daily differential is enough. Also you should possibly look at table partitioning and further look into file group backups but that needs bit of planning...

For File Group Backups and Table Partitioning Please refer
http://msdn.microsoft.com/en-us/library/ms179401.aspx
http://www.databasejournal.com/features/mssql/article.php/3640891/Data-Partitioning-in-SQL-Server-2005---Part-II.htm


~RD


You really cannot say this, because you don't know what the actual requirements are, what hardware is available, what the maintenance window is, etc...

If the maintenance window is large enough, and the hardware can support it - it might be better to perform daily fulls and 15 minute transaction log backups.

If the business requirements were to be able to restore the system within 2 hours - would this plan work? No telling, because we don't know enough about the system to design a backup solution that would meet the requirements.

And, moving down the path of performing filegroup backups shouldn't be considered until all other options have been tried to meet the business requirements.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #930437
Posted Tuesday, June 01, 2010 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:00 AM
Points: 10, Visits: 75
yeah that's true ....My apologies I forgot the log backups option...let me re-correct ...A weekly full backup followed by a daily differential backup and log backup(10-15 min or more depending on what business is willing to accept).

It wont be a good strategy to go for a full backups daily as this is a 2 TB database that means a lite speed backup will be somewhere about 200 GB( Depends on the utilization % of the DB). Every MB is a cost so all things have to be taken in to count.

Lets assume that full backups are swapped on the tape daily on production environment and there is corruption that happens and the backups that are in place are also corrupted ....now a 200 GB(approx) backup needs to be downloaded from tape which will approximately take 2-3 hrs depending on the environment and resources....and thus increasing the recovery time approx ( 3 hrs download + 1 hr of restore time) instead what can be done is to have a weekly full backup with tape sweep and retention period of 1 week and the daily differential with a tape sweep and a retention period of 72 hrs in term of log backups, tape backup with a retention of 24 hours is fine. There are big benefits of this strategy ...you have a full backup and a differential is on disk for 3 days...so your recovery time is reduced.

Also going by the file group backups that I had stated, let me give you some details as to why I suggested it...See to my understanding the database that is in talk out here is a 2 TB database ....possibly a warehouse kind of thing ...that means there is possibly data that wont be changed and there will be data that will get changed....In my previous post when I stated file group backups I meant backing up the file groups based on read only file groups and read and write file groups. Off course this needs to be planned as it would require a major re-structuring of data and has to be done in parallel with the prod environment....


~RD
Post #930596
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse