Thursday, February 14, 2013 9:57 PM
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
- Backup Primary and Secondary files/filegroups separately
- Create a ‘damaged’ Secondary file by deleting it (by stopping/restarting SQL service)
- Take Secondary File offline
- Bring Database Online
- Backup Transaction Log (NOT Tail Log)
- Insert data in tables that reside in Primary (here is where I think it messes up the T-Log sequence)
- Restore Secondary File backup
- 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?
Friday, February 15, 2013 6:51 PM
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.
Friday, February 15, 2013 7:13 PM
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.
Friday, February 15, 2013 8:36 PMAfter 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.
Friday, February 15, 2013 10:41 PM
Here's a sample. It does a filegroup restore to take a secondary filegroup offline. This just
- Backup Secondary filegroup
- Make changes to the database
- Restore Secondary File backup
- Take transaction log backuo
- Restore transaction log backup
- 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
- Marked As Answer by Allen Li - MSFTModerator Wednesday, February 20, 2013 6:08 AM