Backup and restore

Question

Post reply

Backup and restore

viswanath09

Mr or Mrs. 500

Points: 512
More actions
May 27, 2010 at 11:09 pm

#387038

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sommerville Grasshopper Points: 18 More actions · Answer 1

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

~RD Old Hand Points: 348 More actions · Answer 3

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

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
“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

~RD Old Hand Points: 348 More actions · Answer 5

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