Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
So why do they offer FILEGROUP backups if you need to do a full restore anyways? A full restore == a database backup, then restore? Maybe I've misunderstood your answer. I don't mind grabbing a NEW FILEGROUP backup and restoring, if i've done something wrong in my backup step. –  Pure.Krome Sep 8 '11 at 5:11
add comment

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.

share|improve this answer
    
Hi Denny - thanks for the reply :) But I still don't understand blush :( So first up, my backup and restore syntax looks fine. Tick. But .. i need to also restore tx logs? but aren't those inside my PRIMARY filegroup? I'm not sure what I need to do (syntax wise) to ALSO backup the logs and then restore them into the db... ?? –  Pure.Krome Sep 8 '11 at 8:46
    
Transaction logs aren't stored within any file group. The database as a separate file which is the transaction log (usually with the ldf file extension). You backup the log using the BACKUP LOG statement to separate files. From there you'll need to restore the logs on the database to get everything in sync. –  mrdenny Sep 8 '11 at 9:10
    
@AHh .. so i need two more scripts. one to back up the FILEGROUP .. and now one to backup the logs. Then restore both. That's what you're saying, now? If so, any hints to the BACKUP LOG script? –  Pure.Krome Sep 8 '11 at 9:12
    
That's correct. "BACKUP LOG XWing TO DISK='D:\SomePath\File.trn" You'll want to backup to a separate file each time you backup the log, and you'll want to backup the log regularly. Most people backup the log between every 10 and 20 minutes depending on how much data you are willing to loose. –  mrdenny Sep 8 '11 at 9:18
    
So if i have a script that does these 2 backups, would i backup the FILEGROUP first, then the LOG ... zip the two files together .. download .. restore FILEGROUP then LOG <--- in that order? –  Pure.Krome Sep 8 '11 at 9:23
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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