Online Restore without taking a Tail Log Backup

Question

I am in the process of learning Online Restores.  Is there a way to perform an online restore based on this scenario without taking a tail log backup?

Using SQL 2008 R2

1. Backup Primary and Secondary files/filegroups separately
2. Create a ‘damaged’ Secondary file by deleting it (by stopping/restarting SQL service)
3. Take Secondary File offline
4. Bring Database Online
5. Backup Transaction Log (NOT Tail Log)
6. Insert data in tables that reside in Primary (here is where I think it messes up the T-Log sequence)
7. Restore Secondary File backup
8. Restore Transaction Log Backup from step 5

The objective here is not having to put the entire database into recovery mode while restoring the Secondary file.

Thoughts?  Is this even possible?

TIA,

David

Answer 1

I am not a backup expert, and honestly haven't tried this based on a complete filegroup so far, only on page Level restores.

In theory the key Point is that no Access happens to the secondary filegroup. So from my understanding of the internal structure of backups your Scenario should work. I do know for a fact that this does work when running restore on page Level.

Answer 2

I just tested this and it works fine.  You have to restore log backups covering the period from the filegroup backup to the last LSN that affecting the offline filegroup.  But you do not need to take the database offline with a "Tail log backup", you just need to ensure that the "tail" of the log chain (ie the transactions in the log file but not in any log backup) does not contain any changes to the offline filegroup.

Restoring a log backup taken while the database was partially available will do.

Davdi

---

David http://blogs.msdn.com/b/dbrowne/

Answer 3

After executing step 6...the transaction log backup becomes invalid.  Unless I'm attempting to restore the log incorrectly.  I can still query primary tables after the restore, but the secondary still remains offline.

Answer 4

 

Here's a sample.  It does a filegroup restore to take a secondary filegroup offline.  This just

1. Backup Secondary filegroup
2. Make changes to the database
3. Restore Secondary File backup
4. Take transaction log backuo
5. Restore transaction log backup
6. Recover database

set nocount on

drop database p
go
create database p on  
 primary  (name = 'p', filename = 'c:\temp\partial_primary.mdf'), 
 filegroup s ( name = 's', filename = 'c:\temp\partial_secondary.ndf')
 log on ( name = 'log', filename = 'c:\temp\partial_log.ldf' )
go
backup database p to disk='c:\temp\p.bak' with init
go
backup database p filegroup='s' to disk='c:\temp\s.bak' with init;

create table p..t(id int)
insert into p..t(id) values (1)

create table p..ts(id int) on s
insert into p..ts(id) values (1)

go
restore database p filegroup='s' from disk='c:\temp\s.bak';
go
select state_desc, redo_start_lsn, redo_target_lsn
from p.sys.database_files
where name = 's'

print('^^^ after the restore note redo_start_lsn < redo_target_lsn
')

backup log p to disk='c:\temp\plog.bak' with init

--insert into a table on the primary fg
insert into p..t(id) values (2)

--restore the log backup taken before the change and covering the 
restore log p from disk='c:\temp\plog.bak' with norecovery

select state_desc, redo_start_lsn, redo_target_lsn
from p.sys.database_files
where name = 's'

print('^^^ redo_start_lsn > redo_target_lsn so we can recover the database to bring the secondary filegroup online
')

restore database p with recovery


select state_desc, redo_start_lsn, redo_target_lsn
from p.sys.database_files
where name = 's'

print('^^^ now the secondary filegroup is online
')

David

---

David http://blogs.msdn.com/b/dbrowne/