Filegroup Backups

I seem to be having an issue with file groups and differential backups. I
have a database with multiple filegoups. If I do a filegoup backups along
with a differential backup, I cannot restore the differential backup. I get
the following error:
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 827000000462900035, which is too
recent to apply to the database. An earlier log backup that includes LSN
827000000460600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have tried filegoup as well as database differential backups with the same
result. Restoring logs seems to work fine, but in my scenario, I donâ't want
to rely on 1 week or a month of transaction logs depending on how often I do
a full backup on each filegoup. First question, are differential backups
supported with filegroups? If so, is there a special way to do the
differential backup/restore?
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Primary'
TO DISK = N'c:\Primary.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
FILEGROUP = N'Doc1'
TO DISK = N'c:\Doc1.bak'
WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [PartitionTest]
TO DISK = N'c:\diff.diff'
WITH NOFORMAT, DIFFERENTIAL
BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
TO DISK = N'c:\partition.diff'
WITH NOFORMAT, DIFFERENTIAL
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
FROM DISK = 'c:\Primary.bak'
with move 'PartitionTest' to 'c:\partitiontest2.mdf'
,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
,PARTIAL,norecovery,REPLACE
RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
FROM DISK = 'c:\doc1.bak'
with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
,standby = 'f:\standby.bak',REPLACE
-- this will error out
RESTORE log [PartitionTest_Restore]
FROM DISK = 'c:\diff.diff'
with norecovery
-- Same error.
RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
FROM DISK = 'c:\diff.diff'
with norecoveryDave B,
To restore a differential backup, you have to use "restore database"
statement instead "restore log". Also, when restoring multiple backups, you
need to use option "with norecovery" except in the final restore.
Check "restore database" in BOL for more info.
AMB
"Dave B" wrote:
> I seem to be having an issue with file groups and differential backups. I
> have a database with multiple filegoups. If I do a filegoup backups along
> with a differential backup, I cannot restore the differential backup. I get
> the following error:
> Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 827000000462900035, which is too
> recent to apply to the database. An earlier log backup that includes LSN
> 827000000460600001 can be restored.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> I have tried filegoup as well as database differential backups with the same
> result. Restoring logs seems to work fine, but in my scenario, I donâ't want
> to rely on 1 week or a month of transaction logs depending on how often I do
> a full backup on each filegoup. First question, are differential backups
> supported with filegroups? If so, is there a special way to do the
> differential backup/restore?
>
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Primary'
> TO DISK = N'c:\Primary.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> FILEGROUP = N'Doc1'
> TO DISK = N'c:\Doc1.bak'
> WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [PartitionTest]
> TO DISK = N'c:\diff.diff'
> WITH NOFORMAT, DIFFERENTIAL
> BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> TO DISK = N'c:\partition.diff'
> WITH NOFORMAT, DIFFERENTIAL
>
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> FROM DISK = 'c:\Primary.bak'
> with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> ,PARTIAL,norecovery,REPLACE
> RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> FROM DISK = 'c:\doc1.bak'
> with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> ,standby = 'f:\standby.bak',REPLACE
> -- this will error out
> RESTORE log [PartitionTest_Restore]
> FROM DISK = 'c:\diff.diff'
> with norecovery
> -- Same error.
> RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> FROM DISK = 'c:\diff.diff'
> with norecovery
>|||Thanks.
"Alejandro Mesa" wrote:
> Dave B,
> To restore a differential backup, you have to use "restore database"
> statement instead "restore log". Also, when restoring multiple backups, you
> need to use option "with norecovery" except in the final restore.
> Check "restore database" in BOL for more info.
> AMB
> "Dave B" wrote:
> > I seem to be having an issue with file groups and differential backups. I
> > have a database with multiple filegoups. If I do a filegoup backups along
> > with a differential backup, I cannot restore the differential backup. I get
> > the following error:
> >
> > Msg 4305, Level 16, State 1, Line 1
> > The log in this backup set begins at LSN 827000000462900035, which is too
> > recent to apply to the database. An earlier log backup that includes LSN
> > 827000000460600001 can be restored.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE LOG is terminating abnormally.
> >
> > I have tried filegoup as well as database differential backups with the same
> > result. Restoring logs seems to work fine, but in my scenario, I donâ't want
> > to rely on 1 week or a month of transaction logs depending on how often I do
> > a full backup on each filegoup. First question, are differential backups
> > supported with filegroups? If so, is there a special way to do the
> > differential backup/restore?
> >
> >
> > BACKUP DATABASE [PartitionTest]
> > FILEGROUP = N'Primary'
> > TO DISK = N'c:\Primary.bak'
> > WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [PartitionTest]
> > FILEGROUP = N'Doc1'
> > TO DISK = N'c:\Doc1.bak'
> > WITH NOFORMAT, INIT, NAME = N'PartitionTest-Full Filegroup Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> >
> > BACKUP DATABASE [PartitionTest]
> > TO DISK = N'c:\diff.diff'
> > WITH NOFORMAT, DIFFERENTIAL
> >
> > BACKUP DATABASE [PartitionTest] FILEGROUP = N'Primary'
> > TO DISK = N'c:\partition.diff'
> > WITH NOFORMAT, DIFFERENTIAL
> >
> >
> > RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Primary'
> > FROM DISK = 'c:\Primary.bak'
> > with move 'PartitionTest' to 'c:\partitiontest2.mdf'
> > ,move 'PartitionTest_log' to 'c:\partitiontest_log2.ldf'
> > ,PARTIAL,norecovery,REPLACE
> >
> > RESTORE DATABASE [PartitionTest_Restore] filegroup = 'Doc1'
> > FROM DISK = 'c:\doc1.bak'
> > with move 'PartitionTest_Doc1' to 'f:\data\PartitionTest_Doc1_1.ndf'
> > ,standby = 'f:\standby.bak',REPLACE
> >
> > -- this will error out
> > RESTORE log [PartitionTest_Restore]
> > FROM DISK = 'c:\diff.diff'
> > with norecovery
> >
> > -- Same error.
> > RESTORE log [PartitionTest_Restore] FILEGROUP = N'Primary'
> > FROM DISK = 'c:\diff.diff'
> > with norecovery
> >
> >