Bulk-Logged Recovery Model
-
Hello everyone
I have a 1 TB database, recovery set to Bulk-Logged. I did a full backup of the database and applied log backups every 30 mins.
My question/concern is, is it a must to do a full backup daily? Can I do it once a week (although recovery would take longer).
Or can I take a full backup twice a week?
Thank you.
A
-
Anchelin (3/26/2008)
Hello everyoneI have a 1 TB database, recovery set to Bulk-Logged. I did a full backup of the database and applied log backups every 30 mins.
My question/concern is, is it a must to do a full backup daily? Can I do it once a week (although recovery would take longer).
Or can I take a full backup twice a week?
Thank you.
A
It is not a must 🙂 What are your requirements for recovery time? What is the main problem with taking daily full backups? Is it time? Is it space? There are third party tools available that can help you with this (e.g. compressing the backup on the fly, thus using less space and speeding the backup up significantly).
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Hi Andras
Thank you for the reply.
It should be point in time. The time (how long the backups take to complete). My backup is running since this morning 3:30. just to add, its across the network backup to another drive on another box.
We have currently experiencing space issues. :hehe:
Can I do it (full backup) then twice a week?
-
If it's for "point in time", then I'm pretty sure you need FUll Recovery and differential backups multiple times per day. Do 1 full backup a week... differential backups ever 30 minutes or so... depends on how important the data really is.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff is right, if you want point-in-time recovery you will need Full recovery mode.
Concerning whether you can decrease the frequency of full backups, test, test, test, test! Imagine you had a major failure on Thursday. Test (in a staging environment) how long it would take to get the database back online. If the time required for this is acceptable for you, then ... 🙂
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
They are doing large bulk inserts, so we need to make use of Bulk-logged Recovery. So to recover the database to the end of last transaction log backup.(what I meant to say)
So my question is, can I do two full backups twice a week....or should I take full daily backups (and applying transaction log backups as well)?
Thank you in advance
-
They will both do the same thing ultimately, assuming the backups are good. the main downside to this approach is that if you do full backups twice a week, you have a LOT of log backups to replay if you have a need to use it.
I'm not a big fan of having to replay several days' worth of activity. That's the last thing I want to have to worry about in case of a disaster.
Are you really maxxed out on slots for disks? You'd be better off backing up to a (even non-RAID) local disk drive/volume, and then copying that over the wire. A backup seems to be equivalent to copying that file about 5-6 times in terms of traffic, so this will severely slow down your network link.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I think that you should Really consider using third party tools.
-
Do a Full backup 1x a week (say Saturday night). Then do Differential Backups Daily (if you must) and caryy on with the Log backups as normal.
-
Lionel E. Nzenze (3/27/2008)
Do a Full backup 1x a week (say Saturday night). Then do Differential Backups Daily (if you must) and caryy on with the Log backups as normal.Just to add, you will only need the last successful differential backup together with the last full backup and the log backups from that cover the period from the last differential backup.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
First of all, if you have multiple database file groups and time is more of concern than point of recovery, you can have separate backups for the filegroups scheduled at your ease, along with a full database backup once a month. You also need to carefully have backups of the log, which will become huge, as all the non-logged bulk operations will now be logged into the log file backup. Also, you can have differential backups during off-peak hours.
You need to plan it carefully and over a period of time, but be sure of the fact that due to advanced technologies like the RAID and others, it will hardly be required unless to be sure of recovery due to natural calamities.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit. -
What we do here is the following:
Full backup every Sat night at midnight (Sun morning, actually)
Differential backups midnight every other day of the week
Log backups evey 15 minutes
These are backed up to a local folder. We then compress them using bZip2 and copy these compressed backup files (full and differential only) to two geographically separate file servers. We keep two complete days on the local servers, 14 complete days on the file servers.
What was said earlier about only needing the latest of each type is correct. To illustrate this, let's say one of our databases puked on a Friday afternoon. We would need to take the following steps:
Restore the last full backup
Restore diff backups in order
Restore log backups in order
I actually built a T-SQL script that does all this for me, simply because I didn't want to be sitting at the console the whole time this was going on. In addition, it speeds the process up significantly (it eliminates all the time spent either clicking things in the GUI or making changes to SQL scripts to run the commands).
Hope this helps!
Cogiko ergo sum (I geek, therefore I am).
-
David,
are you sure that you have bulk-logged as your recovery model?
What is the size of your database, and the average size of the log backups?
How frequent and in magnitude are your bulk operations?
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit. -
Are you sure that you have bulk-logged as your recovery model?
None of these are bulk-logged. Some are full recovery and some are simple. The full-recovery databases are the only ones that get the T-log backups, obviously.
What is the size of your database, and the average size of the log backups?
There are over 300 databases, ranging in size from a few tens of megabytes to almost a terabyte.
How frequent and in magnitude are your bulk operations?
Again, no bulk ops. A LOT of data entry (on the order of 25,000 business transactions a day, generating over 450,000 SQL transactions a day). Since the log backups occur every fifteen minutes they tend to be on the small side.
Cogiko ergo sum (I geek, therefore I am).
-
Hi All
Thanks for your replies. Much appreciated.
David Naples i do like your DR approach. Is it possible for you to issue your SQL script? 🙂
Sounds like something all of us need.
Thanks in advance
A
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply