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

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= 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.'

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.

StackExchange Database Administrators Q#5511, answer score: 3