PieceMeal Restores

[Skittle]

I have a query about Filegroup backups in SQL Server 2005.

---
I have four big file groups:- 'Primary', 'FGA' and 'FGB'
in a recovery mode FULL database.

Transaction log is backed up at on Monday at 1300.
'Primary' is backed up on Monday night at 1800.

Transaction log is backed up at on Tuesday at 1300.
'FGA' is backed up on Tuesday night at 1800.

Transaction log is backed up at on Wednesday at 1300.
'FGB' is backed up on Wednesday night at 1800.
---

On Thursday morning I find I need to perform a restore
following a failure early on Thursday morning at 0600.
To get the data back I believe I therefore need to:-

1) Backup the transaction log immediately.
2) Restore the 'Primary' WITH NORECOVERY,
PARTIAL
3) Restore the 'FGA' WITH NORECOVERY
4) Restore the 'FGB' WITH NORECOVERY
5) Restore the transaction log from step 1) WITH RECOVERY

Am I correct or do I need to restore the other logs from Tuesday, and Wednesday before the tail log restore?



Dazed and confused

 

[mrdenny]

Filegroup restores don't work well with transcation log restores. This is because when the filegroup backups are restored and then the transaction log is restored some of the same transactions may need to be rolled forward. I'm not sure how well SQL Server will handle having to roll forward transactions which have already been rolled forward.

From what I understand from people who noramlly do filegroup backups they don't to transaction log backups. They rely on file group backups and differentials to cover the changing data.

Within your large filegroups do you have data chanding within all the filegroups? Filegroup backups are usually done on filegroups which don't have much or any data change. For example if you partitioned a table by year you could do filegroup backups of the old years then your differential backups would cover the changed data in the new file groups.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Skittle]

The plan was for changing data on all groups.

I suspect you have to apply all transaction logs that carry any transactions that have not been applied, regardless of filegroup but I have not found a definitive text on this.

Dazed and confused

 

[Skittle]

Ok, I couldn't find a reference on the internet that was concise for this problem so I've spent an in hour in a big computer book store looking through SQL Server Backup chapters and found a precise description.


It refers to a single filegroup device failure where the filegroup may not have been the most recent filegroup backup.

MCTS 70-431 from Sybex states '...all you need to restore is the backup of the filegroup that failed and the transaction logs that occurred after the filegroup was backed up....SQL Server is fully capable of determining which transactions belong to each filegroup. When you restore the transaction log, SQL server applies only the transactions that belong to the failed group.'

That says to me that missing transactions from any file group will be applied and any that have already been applied via a restore will not be re-applied.


Dazed and confused

 

[Skittle]

I just performed a few tests.

Using my original posting example as a scenario, I've found the only way to get all the database back online with all the updates that might appear in a day is to

i) Backup tail log
ii) Restore the last full backup WITH PARTIAL, NORECOVERY
iii) Restore each filegroup backup in date sequence WITH
NORECOVERY
iv) Restore each transaction log in date sequence since
the full backup WITH NORECOVERY.
v) Restore the tail backup WITH RECOVERY.




Dazed and confused

 

[mrdenny]

That says to me that missing transactions from any file group will be applied and any that have already been applied via a restore will not be re-applied.

I read that the same way that you do. It's good to know that SQL is smart enough to do that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog