Restore Fails

Question

Restore Fails

Shah_hs

Ten Centuries

Points: 1117
More actions
April 19, 2007 at 3:00 pm

#116946

I am trying to restore backup and the system gives me error for LSN. No error no. is displayed but it seems that the system doesnot accept trn log restore.

My bkp strategy is:
Full Backup: weekly
Diff bkp: daily
T Log Bkp : every 2 hours.
But sometime I need to shrink Log file due to heavy growth. Hope that is not creating problem. The following is what i do for shrinking TLog file.
BACKUP LOG ABC TO ABC_BackUp
DBCC SHRINKFILE(ABC_Log, 500)

pL. guide why my restore fails. When i try to exclude TLog bkp, the system gives me another error;
System.Data.SqlClient.SqlError: Invalid column name 'lightweight'. (Microsoft.SqlServer.Smo)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 1

Hi Shah,

Post the error details, that would help to reach the target easily. As you said, while restoring the Tlogs it is throwing err. Check the following.

1. Verify the First and Last LS numbers of the specific Tlog file using RESTORE HEADERONLY .

2. If the LSN missing on that file, verify the next and try to restore the appropriate one.

Note. The last LSN of a Tlog backup file should match with the next Tlog backup file's first LSN.

Eg. Tlog1.bak --> First LSN 10242012012345 <>Last LSN 10242012012350

Tlog2.bak --> First LSN 10242012012350 <>Last LSN 10242012012360

Verify the last 4 digits of the above LSNs you can easily understand it.

...

Guru's, Correct me if am wrong anywhere...

warmest

Jayakumar K.

Thanks
Jay
http://www.sqldbops.com

Shah_hs Ten Centuries Points: 1117 More actions · Answer 2

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly

BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT

RESTORE VERIFYONLY

FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily

BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr

BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

MohammedU SSCoach Points: 19644 More actions · Answer 3

Run RESTORE HEADERONLY against your tlog backups and see if there is any issue with LSN...

And mostly log LSN mismatch only when you run log truncation or recovery model changed.

MohammedU
Microsoft SQL Server MVP

Shah_hs Ten Centuries Points: 1117 More actions · Answer 4

I recreated backup file and then tried to restore again but this time there is a mismatch between LSN Numbers. What is the reason for this. Pls. guide.

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 5

There are several reasons to fail a RESTORE. When you're doing a point in time recovery,we need to apply all log files after the Full/latest Diff backup. Have to apply Tlogs one by one. The following reasons may cause the failures.

1. If any one of the Tlog bak file missing then you can not proceed further. It will fail with LSN mismatch error.

2. If any corruption in the Tlog, that is also will lead to the above state.

So be keen on that, if it fails again, take a full backup again and RESTORE it on the target server.

In your scenario which you mentioned in the above logs, is that database involved with replication? If yes try with KEEP_REPLICATION option (I've not tried before) Also I found the Invalid Column name error, verify one of the table structure must be changed, if it is changed in publisher, take a snapshot and apply it on the subscriber. That would help you.

Good day...

Thanks
Jay
http://www.sqldbops.com

Shah_hs Ten Centuries Points: 1117 More actions · Answer 6

No log file can be missed as i take backup on bkp device. Also i even tried with keep_replication option but that also doenot work. I recreated the backups and again tried to restore new bkps but every time there is a mismatch between LSNs of TLog files. Why this happens?

RichB SSCrazy Eights Points: 9651 More actions · Answer 7

well, looking at the reference provided in the error:

http://msdn2.microsoft.com/en-us/library/ms151782.aspx

looks like it might help?