January 30, 2009 at 12:45 pm
I have a db in full recovery mode. This is a SQL Server 2005 DB. I have been reading that dbs in full recovery mode need to have tran log backups, and that only dbs in full mode should have a the TRN log files in the backup plan. Why is this? Could I just use the BAK to backup?
January 30, 2009 at 1:53 pm
Not sure I fully follow but I can try and help.
Since your DB is in full recovery mode, then yes, you need to take full and transaction log backups. How often you take your transaction log backups depends on the system.
For my systems, at this time, I have a job that runs full backups of my DB's (at night) and I have another job that runs transaction log backups since I run those all through the day and I need a PITR (Point in time recovery) solution.
Let me know if that helps.
JW
January 30, 2009 at 2:10 pm
I've been recommending this article written by Paul Randal. It may provide a little more insight into what's going on and maybe give you with a little more direction.
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
-- You can't be late until you show up.
January 30, 2009 at 6:03 pm
Just looking for a basic backup plan, and wondering why I need to use Transcation files with .BAK file in full recovery mode.
January 30, 2009 at 9:19 pm
First you should investigate why your DB is in Full Recovery mode? Is it according to your business?
There are many advantages for DB to be in Full Recovery model. First and foremost it point in time recovery, it leads you for a robust DR plan on one condition that only if you take regular transaction log backups. It minimizes the data loss for the business.
Full recovery model logs every transaction occurring in your database. If you do not take regular transaction log backups while DB is in Full recovery model your log file will grow enormous and it would not be truncated thus occupying your disk space which can lead to putting your database in suspect mode.
If you do not want point-in-time recovery you can change your recovery model to Simple which will do periodic checkpoints. But, before changing the recovery model you need to investigate your business requirements.
January 31, 2009 at 10:10 am
Please read through the article that Terry posted, and also this one - Managing Transaction Logs[/url]
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply