Error with "The log in this backup set begins...."

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error with "The log in this backup set begins...."

Author

Topic

lipkee85
Starting Member

7 Posts

Posted - 2009-11-27 : 12:43:11

--set database to full recovery
USE master;
ALTER DATABASE NetTrustOSK SET RECOVERY FULL;

--create new filegroup and file
USE master;
GO
ALTER DATABASE NetTrustOSK
ADD FILEGROUP Secondary;
GO

ALTER DATABASE NetTrustOSK
ADD FILE
(
NAME = Secondary2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Secondary.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Secondary;
GO

--create table in secondary filegroup
USE NetTrustOSK;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON Secondary;
GO

USE NetTrustOSK;
insert into MyTable (cola,colb) values ('1','A')
insert into MyTable (cola,colb) values ('2','B')
insert into MyTable (cola,colb) values ('3','C')
insert into MyTable (cola,colb) values ('4','D')
insert into MyTable (cola,colb) values ('5','E')

BACKUP DATABASE NetTrustOSK READ_WRITE_FILEGROUPS
TO DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
WITH DESCRIPTION = 'First BackUp Of NetTrust',
INIT
GO

--USE NetTrustOSK
--select * from usergroup --256 records
--select * from MyTable --5 records

USE NetTrustOSK
delete from usergroup --0 records
delete from MyTable --0 records

BACKUP LOG NetTrustOSK TO DISK='C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
WITH NOINIT;

BACKUP DATABASE NetTrustOSK
FILE = 'Secondary2', FILEGROUP='Secondary'
TO DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.diff'
WITH DIFFERENTIAL,
NOINIT,
STATS= 50
GO

RESTORE DATABASE NetTrustOSK
FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE DATABASE NetTrustOSK
FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.diff'
WITH NORECOVERY
GO
RESTORE LOG NetTrustOSK
FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
WITH FILE=1,NORECOVERY
GO
RESTORE LOG NetTrustOSK
FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.bak'
WITH FILE=2,NORECOVERY
GO
RESTORE LOG NetTrustOSK
FROM DISK = 'C:\Documents and Settings\Lip Kee\My Documents\Backup\NetTrust_2009.diff'
WITH FILE=1,RECOVERY

Currently, the primary filegroup contains usergroup table with 256 records and my secondary filegroup contains MyTable table with 5 records. After running all the codes above, I get this error.

Processed 98384 pages for database 'NetTrustOSK', file 'NetTrust_Data' on file 1.
Processed 16 pages for database 'NetTrustOSK', file 'Secondary2' on file 1.
Processed 5 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 98405 pages in 86.856 seconds (9.281 MB/sec).
Processed 16 pages for database 'NetTrustOSK', file 'Secondary2' on file 1.
Processed 3 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 19 pages in 0.421 seconds (0.353 MB/sec).
Processed 0 pages for database 'NetTrustOSK', file 'NetTrust_Data' on file 1.
Processed 0 pages for database 'NetTrustOSK', file 'Secondary2' on file 1.
Processed 5 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 1.
RESTORE LOG successfully processed 5 pages in 0.253 seconds (0.159 MB/sec).
Processed 0 pages for database 'NetTrustOSK', file 'NetTrust_Data' on file 2.
Processed 13 pages for database 'NetTrustOSK', file 'NetTrust_Log' on file 2.
RESTORE LOG successfully processed 13 pages in 0.026 seconds (4.017 MB/sec).
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 188485000000009400255, which is too recent to apply to the database. An earlier log backup that includes LSN 188485000000007400001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I will restore the database back to the original where both tables MyTable and usergroup remain 5 and 256 records each. Then by applying the differential backup for Secondary filegroup, MyTable table will have 0 records and usergroup table remain 256 records. Thanks in advance for any reply!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-27 : 12:50:14

You don't restore multiple differentials, just one and then the tlog chain after that if needed. So you can not restore that last differential at that point, but you could instead restore the last full backup and then that last diff.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

lipkee85
Starting Member

7 Posts

Posted - 2009-11-27 : 20:56:34

Thanks for the reply, tkizer. Sorry, I'm not really good at this. I only did one differential file backup on secondary filegroup. If I backup file or filegroups, I need to set the filegroup to Read-Only. So exactly how do I fix my clumsy code there?

lipkee85
Starting Member

7 Posts

Posted - 2009-11-29 : 22:02:53

I've decided to fix the above problem using Point of recovery method and it works. Thanks for the reply tkizer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources