FULL recovery model but no log backups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

FULL recovery model but no log backups

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-30 : 07:30:15

Good afternoon,

This is probably a daft question but I need to ask to make sure that I've understood this correctly.

My database is in full recovery mode.

am I right in saying that without log backups, I won't be able to recover the database to a point in time? I'm pretty sure that’s what it means. For example:

Day 1: Full backup
Day 2: Differential Backup
Day 3: Differential Backup
Day 4: Disaster strikes at Midday.

Can only recover to last Differential Backup so it would be

Restore Day 1 Full backup + Day 3 Differential

To be able to recover to a point in time I need to perform log backups.

Day 1: Full backup
Midday: Log backup
Day 2: Differential Backup
Midday : Log Backup
Day 3: Differential Backup
Midday : Log Backup
Day 4: Disaster strikes at Midday.

I can do

Day 1 Full + Day 3 Differential + Day 3 Midday Log Backup

Have I understood this right?

Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-30 : 07:54:06

Almost.
If you aren't truncating the log then it will be growing and you will have all the transactions (ever) contained in it.
After a failure you might be able to back up the log and do a point in time recovery.

Of course the log will eventually fill the disk and your database will fail and you will get a chance to test this.
That's a warning to do someting about it by the way.
Think of something like weekly full, daily diff and hourly log backups as a default.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-30 : 08:34:55

quote:
Originally posted by nigelrivett

Almost.
If you aren't truncating the log then it will be growing and you will have all the transactions (ever) contained in it.
After a failure you might be able to back up the log and do a point in time recovery.

Thanks Nigel.

But I thought that backing up the transaction log makes SQL Server free up the space of the logs that have been backed up?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 11:04:05

quote:
Originally posted by Abu-Dina

[quote]Originally posted by nigelrivett

Almost.
If you aren't truncating the log then it will be growing and you will have all the transactions (ever) contained in it.
After a failure you might be able to back up the log and do a point in time recovery.

Thanks Nigel.

But I thought that backing up the transaction log makes SQL Server free up the space of the logs that have been backed up?
[/quote
yep ..though not always immediate

some cases delay can occur for truncation
see
http://msdn.microsoft.com/en-us/library/ms190925.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-30 : 12:29:48

Thanks visakh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources