Backup large database for datawarehouse

Question

Post reply

Backup large database for datawarehouse

sharon-644450

Default port

Points: 1442
More actions
November 17, 2009 at 5:03 am

#216810

hi all,
we have a very large database (about 165GB) which is part of our datawarehouse (SQL Server 2005 database). we need to plan database backup as well as DR plan.
is it a good idea if i take a full database backup once a month and schedule a differential backup everyday. we would'nt require a point in time recovery so i was thinking of avoiding transaction log backups.
we have just started with this datawarehouse so we do not know at what rate the size of the database will grow. but it is expected to grow at a fast rate.
the database is divided in 4 filegroups.so taking filegroup abckups will also be time consuming.
primary filegroup - 72 MB
secondary filegroup_1 - 165 GB
secondary filegroup_2 - 224 MB
secondary filegroup_3 - 10MB
log file - 2GB
thanks for your time and help.

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

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

You can start with a Weekly Full backup and daily Differential backup (if needed twice a day).

Monitor the Database growth over a few weeks period, and then decide to go with a Weekly Full or Monthly Full Backup.

It is a trade off between storage space and Recovery time - that you need to do, if you have enough storage space to store backups, then doing a Daily Full backup is a better way so that you can recover from one single backupset, and if Storage space is premium, then do a Monthly Full backup and daily Differential backups, so at a max, you need to recover from 1 Full + 30 Diff Backups, which will be time consuming.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Bru Medishetty (11/17/2009)
You can start with a Weekly Full backup and daily Differential backup (if needed twice a day).
Monitor the Database growth over a few weeks period, and then decide to go with a Weekly Full or Monthly Full Backup.
It is a trade off between storage space and Recovery time - that you need to do, if you have enough storage space to store backups, then doing a Daily Full backup is a better way so that you can recover from one single backupset, and if Storage space is premium, then do a Monthly Full backup and daily Differential backups, so at a max, you need to recover from 1 Full + 30 Diff Backups, which will be time consuming.

Actually, since a differential backup backs up all changes since the last full backup, any restore would consist of one full backup and one differential backup file. Depending on how much data is added between full backups as each differential backup will get bigger each day.

I would look at doing a weekly backup on Saturday or Sunday and differential backups the rest of the week. I would also be sure that the database is using the SIMPLE recovery model, or you will find your transaction log file growing uncontrollably.

Atul DBA Hall of Fame Points: 3610 More actions · Answer 3

As it is not an OLTP database and point in time recovery is not required, you should put the database in Simple recovery mode.

You sould take Weekly Full backup of your database and then everyday evening/night time Diffrential backup.

The restoration process involves only two files, the latest Full Backup file and then latest Diffrential Backup files, you need not save the Diffrential backup files for more than 2 days.

Your current database is not big enough to suggest the Data File backup.