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

-- 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

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.

---

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

---

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

---

quote:

---

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

---

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.

---

quote:

---

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

---