How can I backup & restore a single FILEGROUP in Sql Server 2008

Question

Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup.

Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:-

Processed 1895080 pages for database 'XWing', file 'XWing' on file 1.
Processed 4 pages for database 'XWing', file 'XWing_log' on file 1.
The database cannot be recovered because the log was not restored.
The database cannot be recovered because the log was not restored.
The roll forward start point is now at log sequence number (LSN) 221218000000010400001. Additional roll forward past LSN 221218000000010400001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE DATABASE ... FILE=<name> successfully processed 1895084 pages in 69.504 seconds (213.014 MB/sec).

I used the following Sql code...

alter Database [XWing] SET SINGLE_USER With ROLLBACK IMMEDIATE

restore database [XWing] filegroup = 'PRIMARY'
FROM  DISK = N'C:\Temp\XWing Manual Full Primary Filegroup.bak'
with
    MOVE N'XWing' TO N'D:\XWing.mdf',
    MOVE N'XWing_log' TO N'L:\XWing_Log.ldf',
    replace, recovery

So I'm assuming that the DB wasn't backed up correctly?

This is the script I sed to backup the PRIMARY FILEGROUP

BACKUP DATABASE [XWing] FILEGROUP = N'PRIMARY' 
TO  DISK = N'F:\Sql DB Backups\XWing Manual Full Primary Filegroup.bak' WITH NOFORMAT, INIT,
NAME = N'XWing-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'XWing' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'XWing' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''XWing'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'F:\Sql DB Backups\XWing Manual Full Primary Filegroup.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Questions

1. Is that Restore syntax correct?
2. What about my backup syntax?

Cheers!

Answer 1

You need to restore the transaction logs so that you have a consistent database. Currently the filegroup which you have restored is at a different point in time than the rest of the database. Once the logs have been rolled forward and everything is consistent then you can bring the database online.

If you look at the data in the msdb database you'll be able to see which transaction log files you need to restore in order to bring the database online.

There's nothing wrong with your backup and recovery syntax.

Answer 2

As it states in the message you have to restore the log backups up to the point in time of the filegroup backup. In order to bring the database online it needs to play the log back to the same point in time in order for the database to be consistent. However restoring just the primary file group is a special situation. I believe in order to restore the primary filegroup you pretty much have to do a full restore, not just a filegroup. (I'm not positive on that but believe the below text points to that requirement.)

Understanding How Restore and Recovery of Backups Work in SQL Server

Redo Consistency

In the redo phase, data is always rolled forward to a point that is redo consistent with the state of the database at the recovery point. All the data has been rolled forward to a point at which undo can occur.

The state of the database is defined by the primary file, as follows:

If the primary file is being restored, the recovery point determines the state of the whole database. For example, if a database is being recovered to a point in time just before a table was accidentally dropped, the whole database must be restored to the same point in time.

If the primary file is not being restored, the database state is known and restored data is rolled forward to a recovery point that is transactionally consistent with the database. SQL Server enforces this.

However, the database might contain changes made by transactions that are uncommitted at the recovery point. For online restore, data is recovered to a point in time consistent with the current state of the online part of the database.