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.


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.

| improve this answer | |
  • Thanks. Based on your remakrs, the question I referenced is applicable after all. The delete transaction being re-executed qualifies as the embarrassingly simple cause. I second your remarks regarding data in the primary filegroup. I once worked on a project migrating user tables off the primary filegroup. After that experience, it seemed like any green field database development should include user tables stored off the primary filegroup. – Mike Henderson Jan 8 '14 at 14:13

You had no data in your table after your restore sequence because you restored all of the transactions for your last log backup, and that included the data deletes from your table.

What you're basically looking for is a point-in-time restore. Here is a working example (Note: as Sebastian already hinted at, you need to restore the PRIMARY filegroup when doing a point-in-time restore on a file):

use AdventureWorks2012RestoreTest;

-- create a new filegroup
alter database AdventureWorks2012RestoreTest
add filegroup USERTABLES_01;

-- create a new data file in the new filegroup
alter database AdventureWorks2012RestoreTest
add file
    name = Aw2012Rt_data2,
    filename = '<backup dir>\Aw2012Rt_data2.ndf',
    size = 2 MB
) to filegroup USERTABLES_01;

-- create the duplicate table on the new filegroup
create table [HumanResources].[Employee_01]
    [BusinessEntityID] [int] NOT NULL,
    [NationalIDNumber] [nvarchar](15) NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL,
    [OrganizationNode] [hierarchyid] NULL,
    [OrganizationLevel] [smallint] NULL,
    [JobTitle] [nvarchar](50) NOT NULL,
    [BirthDate] [date] NOT NULL,
    [MaritalStatus] [nchar](1) NOT NULL,
    [Gender] [nchar](1) NOT NULL,
    [HireDate] [date] NOT NULL,
    [SalariedFlag] [bit] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [CurrentFlag] [bit] NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL

-- populate the new table
insert into HumanResources.Employee_01
select *
from HumanResources.Employee;

-- get initial count of table
select count(*)
from HumanResources.Employee_01;
-- 290

-- initiate a full backup of the database
backup database AdventureWorks2012RestoreTest
to disk = '<backup dir>\Aw2012RT_full.bak'
with init;

-- because we need to do a point-in-time recovery we might as
-- well get the datetime stamp right before the delete
select getdate();
-- 2014-01-08 05:34:56.767

-- "accidentally" delete all data
delete from HumanResources.Employee_01;

-- verify all of the data is gone
select count(*)
from HumanResources.Employee_01;
-- 0

-- start the restore sequence
use master;

-- you need to restore the PRIMARY filegroup in order for point-in-time recovery
-- othewise you'll get the following error message:
-- "Point-in-time recovery is not possible unless the primary filegroup is part 
-- of the restore sequence. Omit the point-in-time clause or restore the primary filegroup."
backup log AdventureWorks2012RestoreTest
to disk = '<backup dir>\Aw2012RT_log.trn'
with init, norecovery;

restore database AdventureWorks2012RestoreTest
file = 'Aw2012Rt_data2',
filegroup = 'PRIMARY'
from disk = '\\stringermain\VmShare\temp\Aw2012RT_full.bak'
with norecovery;

-- now you can do point-in-time recovery with the STOPAT clause
-- specifying a datetime stamp before the delete operation
restore log AdventureWorks2012RestoreTest
from disk = '<backup dir>\Aw2012RT_log.trn'
    stopat = '2014-01-08 05:34:56.767',

use AdventureWorks2012RestoreTest;

-- verify that we now have data in the table
select count(*)
from HumanResources.Employee_01;
-- 290

As Sebastian also alluded at the fact, if you're looking to get back data that was deleted it may be inherently easier to restore elsewhere and just copy the data. But I wanted to show your corrected test case just to illustrate how you would do that point-in-time restore.

| improve this answer | |
  • That's great. Thanks for the extensive answer. As I read Sebastian's remarks, it occurred to me a point in time recovery is what I need for the scenario I set up. – Mike Henderson Jan 8 '14 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.