Sql-server – Unexpected Results with File Restore

filegroupsrestoresql server

I am practicing a file restore using the AdventureWorks database and I am not getting the restore I am expecting. I am sure there's some embarrassingly simple cause, but I have not found it yet.

Here is what I have done

  1. Created a new file group named USERTABLES_01.
  2. Copied data from HumanResources.Employee table to new a new .HumanResources.Employee_01 in the USERTABLES_01 filegroup.
  3. Generated a Full backup.
  4. Executed DELETE FROM HumanResources.Employee_01 with no where clause
  5. Shazbot, I need to restore the table. OK, I'll restore the filegroup USERTABLES_01.
  6. Executed RESTORE DATABASE [AdventureWorks2012] FILE N'AdventureWorks2012_UserTables_01' …
  7. Generated a tail log backup
  8. Restored tail log backup

Everything restores without complaint. But my HumanResources.Employee_01 doesn't have any data.

Reviewed MS Example, maybe I didn't follow it correctly, but I am pretty sure I did.

Reviewed similar question, but not sure it's the answer I am looking for: Restore .mdf data file for a filegroup in SQL Server.

Best Answer

SQL Servers highest priority is to comply with the ACID properties. That means, no matter what you do, your database is always going to be transactionally consistent.

What you are trying to do however could get you into an inconsistent state. Think about the standard example to explain the ACID properties: The bank account. Say you have two accounts that reside on different filegroups (e.g. two account tables or one with partitioning). Now you transfer money from filegroup one to filegroup two within a transaction. Then you go and restore filegroup one to before the transfer. That results in the money being in both accounts now, clearly not a desired state.

So, what is happening in your example?

6) the filegroup is successfully restored to a point in time before the disaster. However it is in the "restoring" state and not accessible.

8) the log restore is bringing the filegroup up to speed with the rest of the database (on a transactional level). To do that, all committed transaction that are captured in the log backup are reapplied to that filegroup. However, the delete was a committed transaction, so it is re-executed too. This leaves the database consistent, but the table empty.

The Technet example you are pointing to makes the assumption that the file goes offline for some external reason, e.g. a drive failure. In that case the restore as described makes sense as you would want all the transactions to be re-applied.

In your case you need to restore the database somewhere else and then copy the data back into the original table. For that you can just restore the filegroup containing the data in question and the primary filegroup. You cannot just restore a secondary filegroup, the primary has to always be restored too. This is one of the reasons why it is recommended to not have any data in the primary filegroup. That way the impact of having to restore that one too is minimal.

Related Question