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
- Is that Restore syntax correct?
- What about my backup syntax?
Cheers!
Best Answer
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.