SQL Server 2005 - filegroup backup/restore problem

Hello everyone,

I have a problem with filegroup restore
- short description:
1) I restore a filegroup from the filegroup backup with NORECOVERY, then
2) I restore a transaction log with RECOVERY
and I obtain a message:
Processed 0 pages for database 'AdventureWorks', file
'AdventureWorks2_Data' on file 2.
The roll forward start point is now at log sequence number (LSN)
61000000323100001. Additional roll forward past LSN 66000000258700001 is
required to complete the restore sequence.
RESTORE LOG successfully processed 0 pages in 2.112 seconds (0.000 MB/sec).

and my filegroup is not restored. What is the problem?
Long description can be helpful to understand the problem.
Derek


-- long description (entire script, sorry for this):

-- for AdventureWorks
-- I create a SECONDARY filegroup
ALTER DATABASE AdventureWorks ADD FILEGROUP SECONDARY;
GO
ALTER DATABASE AdventureWorks
ADD FILE ( NAME = N'AdventureWorks2_Data',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\AdventureWorks2_Data.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
TO FILEGROUP SECONDARY;
GO

-- I add one table SalesOrderDetail to the SECONDARY filegroup
USE AdventureWorks
GO
CREATE TABLE [SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL CONSTRAINT
[DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)),
[LineTotal] AS
(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
[DF_SalesOrderDetail_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate())
) ON SECONDARY;
GO

-- I load data
INSERT INTO [AdventureWorks].[dbo].[SalesOrderDetail]
([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],
[ProductID],
[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[rowguid],[ModifiedDate])
SELECT [SalesOrderID],[SalesOrderDetailID],
[CarrierTrackingNumber],[OrderQty],[ProductID],
[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[rowguid],[ModifiedDate]
FROM Sales.SalesOrderDetail;
GO

-- I backup SECONDARY filegroup
USE master
GO
BACKUP DATABASE AdventureWorks FILEGROUP='SECONDARY' TO
DISK='C:\temp\backup\AWFilegroup.bak'
WITH INIT, NAME='AW copy SECONDARY group', DESCRIPTION='AW copy
SECONDARY group at 6PM', STATS=30;
GO

-- I obtain
Processed 1256 pages for database 'AdventureWorks', file
'AdventureWorks2_Data' on file 1.
Processed 6 pages for database 'AdventureWorks', file
'AdventureWorks_Log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 1262 pages in 1.169
seconds (8.837 MB/sec).

-- I modify data
USE AdventureWorks
GO

UPDATE dbo.SalesOrderDetail
SET UnitPrice = UnitPrice * 1.1
GO

-- I do the LOG backup
USE master
GO
BACKUP LOG AdventureWorks TO DISK='C:\temp\backup\AWFilegroup.bak'
WITH NAME='AW LOG after SECONDARY backup', DESCRIPTION='AW LOG after
SECONDARY backup', STATS=30;
GO

-- I obtain the message
Processed 3995 pages for database 'AdventureWorks', file
'AdventureWorks_Log' on file 2.
BACKUP LOG successfully processed 3995 pages in 2.348 seconds (13.937
MB/sec).

-- ooops, disaster
USE AdventureWorks
GO
DELETE FROM dbo.SalesOrderDetail
GO

-- I try to restore
USE master
GO
RESTORE HEADERONLY FROM DISK='C:\temp\backup\AWFilegroup.bak'
GO
RESTORE DATABASE AdventureWorks FILEGROUP='SECONDARY'
FROM DISK='C:\temp\backup\AWFilegroup.bak'
WITH REPLACE, FILE=1, NORECOVERY
GO

-- I obtain:
Processed 1256 pages for database 'AdventureWorks', file
'AdventureWorks2_Data' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 1256 pages in
1.033 seconds (9.960 MB/sec).

-- and I restore LOG
RESTORE LOG AdventureWorks
FROM DISK='C:\temp\backup\AWFilegroup.bak'
WITH FILE = 2, RECOVERY
GO

-- I obtain:
Processed 0 pages for database 'AdventureWorks', file
'AdventureWorks2_Data' on file 2.
The roll forward start point is now at log sequence number (LSN)
61000000326000001. Additional roll forward past LSN 66000000260400001 is
required to complete the restore sequence.
RESTORE LOG successfully processed 0 pages in 2.197 seconds (0.000 MB/sec).

-- and I have got a problem

can you help?

Best regards,
Derek

Darek (dmrozek99@poczta.onet.pl) writes:
> I have a problem with filegroup restore
> - short description:
> 1) I restore a filegroup from the filegroup backup with NORECOVERY, then
> 2) I restore a transaction log with RECOVERY
> and I obtain a message:
> Processed 0 pages for database 'AdventureWorks', file
> 'AdventureWorks2_Data' on file 2.
> The roll forward start point is now at log sequence number (LSN)
> 61000000323100001. Additional roll forward past LSN 66000000258700001 is
> required to complete the restore sequence.
> RESTORE LOG successfully processed 0 pages in 2.112 seconds (0.000
> MB/sec).
>
> and my filegroup is not restored. What is the problem?
> Long description can be helpful to understand the problem.
> Derek
>
>
> -- long description (entire script, sorry for this):

No need to be sorry for that. If more people included scripts like you
do, it would be a lot easier to answer questions. Big thanks for
providing it.

Since I don't use extra filegroups much, I had to consult with
MVP colleagues, who confirmed my suspicion that you can't do what
you seem to be wanting to.

To be able to access the table, you can backup the log again, and
the restore that log. That will bring the filegroup in sync with the
rest of the database. However, you will find that the table is
empty.

The reason your scheme does not work, is that SQL Server does not know
what else might have happened in the database. Say that there is a
SalesOrder table which is in the Primary group. Say also that after
the distaster DELETE, you also deleted order 11000 in the parent table.
If the filegroup would be available after the filegroup restore, there
would now be rows in SalesOrderDetail that violated an FK constraint.
That is impermissible.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx