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 Backups and tran Backups and a Gap!

Author  Topic 

Julien.Crawford
Starting Member

21 Posts

Posted - 2011-09-21 : 02:04:20
I'm going though a teething process here, so this might be a dumb question.

I've setup a job to do a fullback just after mid-night

declare @sql varchar(4000)
set @sql = 'D:\SMART02\Backup\'+convert(varchar(8), GETDATE(), 112)
exec master.dbo.xp_create_subdir @sql
go

declare @sql varchar(4000)
set @sql = 'if DB_ID(''?'') > 4 begin backup database ? to DISK = ''D:\SMART02\Backup\'+convert(varchar(8), GETDATE(), 112)+'\?_full_'+replace(convert(varchar(8), GETDATE(), 108),':','')+'.bak'' with COMPRESSION, init end'

exec sp_msforeachdb @sql


and a job to do a transaction log back and differential backup every 15 minutes (The differential is just there in case I need it - I plan to remove this)

-- Routinely during the day
declare @sql varchar(4000)
set @sql = 'if DB_ID(''?'') > 4
begin
BACKUP LOG ? TO DISK = ''D:\SMART02\Backup\'+convert(varchar(8), GETDATE(), 112)+'\?_tran_'+replace(convert(varchar(8), GETDATE(), 108),':','')+'.trn''
backup database ? to DISK = ''D:\SMART02\Backup\'+convert(varchar(8), GETDATE(), 112)+'\?_diff.bak'' with DIFFERENTIAL, COMPRESSION, init
end'
exec sp_msforeachdb @sql


With the fullbackup and set of tran backups I can restore to any point in the day - Yay.

At this point I thought I was pretty cool. A warning in disguise?

Here is my question...
What if I need to restore to 11:52pm? I do not have that transaction log. In fact it seems there is a race condition here. If anything happens between the last transaction log back and the subscquent Full backup I do not have a means of restoring to that point in time.

At this stage I'm thinking of just having a "Small Race Condition" and putting the backup tran in the same batch as the Full backup.

What is the correct thing to do here?

Julien.

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 03:16:11
Transaction and Full / Differential are unrelated - although one may block the other.

If you don't make a Tlog backup for months (perish the thought!) your TLog file will just grow (and GROW!) and then the TLog backup, when you do make it, will be everything since the last Tlog backup you made. You can restore a Full (plus DIFF) if you like to any point after the start of the Tlog backup, and then restore the Tlog backup and it will "start" restoring from the point after the Full/Diff were made [tehcnially the point at which that backup finished, rather than started], and you can then restore any subseuqent TLog backups.

So for restore:

Restore Full and optionally a later Differential backup which was based on that Full backup using WITH NORECOVERY

Restore the first Tlog backup made after that (using WITH NORECOVERY)
Restore all remaining Tlog backups, in order (using WITH NORECOVERY)

Use STOPAT command if you want to restore to a point-in-time

When you are done use WITH RECOVERY to put the database "live"

Why are you skipping DB_ID <= 4 ?

I would think you want to exclude TEMPDB (and maybe Sample databases) but that's it.

Observations I would make on your approach:

Not sure I like the approach of having one folder per day. I think it will be a pain restoring where Tlog files cross a date boundary, but you may be doing it for ease of purging of stale backup files. (And I can't actually remember when I had to restore TLog files over a date boundary, but for disk space reasons we only take a FULL backup once a day ... that would make your stale-file-purging more problematic? We retain (weekly) Full backups for 4 weeks, (daily) DIFFs for two weeks, and TLog files for 3 days - beyond that we have to resort to tape restore, which takes several hours to request)

How will read-only / offline / non-Full Recovery model databases be handled? (you may get errors trying to take Tlog backups etc)

We explicitly exclude databases with names that start with "RESTORE_" so that we can restore a database for testing purposes without immediately creating a stream of backups
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-21 : 03:35:52
quote:
Originally posted by Julien.Crawford

Here is my question...
What if I need to restore to 11:52pm? I do not have that transaction log. In fact it seems there is a race condition here. If anything happens between the last transaction log back and the subscquent Full backup I do not have a means of restoring to that point in time.


You restore the previous full backup, all log backups up til 11:45 then you restore the 00:00 log backup WITH STOPAT = '11:52'

Simple as that.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Julien.Crawford
Starting Member

21 Posts

Posted - 2011-09-21 : 18:59:17
Thank you Kristen, you've given me things to think about.

quote:
Originally posted by Kristen

Transaction and Full / Differential are unrelated - although one may block the other.

If you don't make a Tlog backup for months (perish the thought!) your TLog file will just grow (and GROW!) and then the TLog backup, when you do make it, will be everything since the last Tlog backup you made. You can restore a Full (plus DIFF) if you like to any point after the start of the Tlog backup, and then restore the Tlog backup and it will "start" restoring from the point after the Full/Diff were made [tehcnially the point at which that backup finished, rather than started], and you can then restore any subseuqent TLog backups.

So for restore:

Restore Full and optionally a later Differential backup which was based on that Full backup using WITH NORECOVERY

Restore the first Tlog backup made after that (using WITH NORECOVERY)
Restore all remaining Tlog backups, in order (using WITH NORECOVERY)

Use STOPAT command if you want to restore to a point-in-time

When you are done use WITH RECOVERY to put the database "live"

Why are you skipping DB_ID <= 4 ?

I would think you want to exclude TEMPDB (and maybe Sample databases) but that's it.

Observations I would make on your approach:

Not sure I like the approach of having one folder per day. I think it will be a pain restoring where Tlog files cross a date boundary, but you may be doing it for ease of purging of stale backup files. (And I can't actually remember when I had to restore TLog files over a date boundary, but for disk space reasons we only take a FULL backup once a day ... that would make your stale-file-purging more problematic? We retain (weekly) Full backups for 4 weeks, (daily) DIFFs for two weeks, and TLog files for 3 days - beyond that we have to resort to tape restore, which takes several hours to request)

How will read-only / offline / non-Full Recovery model databases be handled? (you may get errors trying to take Tlog backups etc)

We explicitly exclude databases with names that start with "RESTORE_" so that we can restore a database for testing purposes without immediately creating a stream of backups

Go to Top of Page

Julien.Crawford
Starting Member

21 Posts

Posted - 2011-09-21 : 19:07:01

I was with you up to 'simply' :-)

I assume by "restore the 00:00 log backup" you are referring to the one taken at mid-night.

What I am suggesting is that there is a non-zero amount of time that passes between the last transaction log backup and the full backup.

If I happen to want to recover to the point in time there, what can I do?

This is TOTALLY academic at this point - I do not have this problem. I'm just doing my due diligence so I can tell my bosses that I can recover to any point in time.

thank you.

Julien

quote:
Originally posted by GilaMonster

quote:
Originally posted by Julien.Crawford

Here is my question...
What if I need to restore to 11:52pm? I do not have that transaction log. In fact it seems there is a race condition here. If anything happens between the last transaction log back and the subscquent Full backup I do not have a means of restoring to that point in time.


You restore the previous full backup, all log backups up til 11:45 then you restore the 00:00 log backup WITH STOPAT = '11:52'

Simple as that.

--
Gail Shaw
SQL Server MVP

Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-21 : 19:29:47
quote:
[i]I assume by "restore the 00:00 log backup" you are referring to the one taken at mid-night.


Yup.

quote:
What I am suggesting is that there is a non-zero amount of time that passes between the last transaction log backup and the full backup.


So what? Doesn't matter in the slightest.

quote:
If I happen to want to recover to the point in time there, what can I do?


You restore the log backups in sequence, from a full backup (probably the one from the prior night) and use STOPAT to specify the appropriate time

Log backups on the 15 min, full backup starts at 00:00 and finishes (for eg) 00:52

Want to restore to 11:59? Restore the previous day's full, all log backups in sequence up to and including the 11:45 one, then restore the one taken at 00:00 WITH STOPAT.

Want to restore to 00:25? Restore the previous day's full, all log backups in sequence up until and including the one that ran at 00:15, then restore the one that ran at 00:30 WITH STOPAT.

This assumes you're not running SQL 2000. If you are, in that case the last log backup that's restored WITH STOPAT will be the first one taken after the full backup (in SQL 2000 full backups block log backups, not true since then)

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 02:17:54
"What I am suggesting is that there is a non-zero amount of time that passes between the last transaction log backup and the full backup."

I had this misunderstanding too when I was first putting a backup system in place

The transactions after TLog backup, and before the Full backup, will be included in the first TLog backup after the Full backup - that TLog backup will include everything from the previous TLog backup - it is completely unrelated to Full Backup.

You could recover a Full Backup from months ago, and every TLog backup since, if you wanted to.

Or a more recent Full backup, and the Tlog backups since then

Or yesterday's Full backup, and the Tlog backups since then

You would get the same result
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-22 : 10:27:08
Also note that as of SQL Server 2005 - the blocking issue of running both a transaction log backup and full (or diff) backup at the same time is no longer an issue.

You can run transaction log backups during the full backup - even if that full backup takes 6 hours to complete.

Jeff
Go to Top of Page
   

- Advertisement -