Restore Problem

Question

Post reply

Restore Problem

Shah_hs

Ten Centuries

Points: 1117

April 24, 2007 at 10:50 am

#116953

Hi,

I am trying to restore a point in time restore of backup. My bkp plan includes full bkp-weekly, daily diff bkp and every 2 hr TLog bkp. I would like to restore the latest bkp as on ''2007-10-18 ' with minimul data loss. Hence i follow the following seqence of restores;

--Restore Full backup

RESTORE DATABASE [ABC] FROM ABC_BackUp WITH FILE = 1, NORECOVERY

GO

--Restore Diff backup

RESTORE DATABASE ABC FROM ABC_BackUp WITH FILE = 3, NORECOVERY

--Restore TLog backup

RESTORE LOG ABC FROM ABC_BackUp WITH FILE=4, NORECOVERY, STOPAT = '2007-10-18 15:11:00'

GO

The system works well with Full bkp and Diff bkp restore but throws following error while TLog restore;

ERROR:

Server: Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 86001000000103200001, which is too late to apply to the database. An earlier log backup that includes LSN 86001000000081300001 can be restored.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

The following is the backup information of my database:-

Position	Bkp Type	Bkp Size	First LSN	Last LSN
1	1	1923229696	86001000000001600002	86001000000005900001
2	2	7284736	85999000000278900001	86001000000081100001
3	5	534528	86001000000081100001	86001000000081300001
4	2	598016	86001000000103200001	86001000000176700001
5	2	729088	86001000000176700001	86001000000276800001
6	2	598016	86001000000276800001	86001000000353100001
7	2	663552	86001000000353100001	86001000000432900001

Pls. guide why i get this error. I don't do any shrinking between Full bkp and TLog bkps. Thanks in advance.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If the scripts above are accurate, I don't see you using the NORECOVERY option while restoring the differential backup.

Also, what happens if you restore the full backup (file 1) and the transaction logs (files 2 and 4) (this means not restoring the differential backup)?

Shah_hs Ten Centuries Points: 1117 More actions · Answer 2

Ya. I use NORECOVERY with diff bkp. (corrected above). Further, i can restore file 2 but not 4. While restoring file 4 it gives the same error (as above). Further, i would like to add that the backup is of production server and i am testing restore on test server. All the settings of production and test server are same. Pl.guide.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

It appears that there my have been a truncate of the log file done between the differential backup (file 3) and the next transaction log backup (file 4).

I am guessing that you have an automated process running that completes the Full/Differential/Transaction Log backups. Take a look at that process and see if there is a backup log with truncate_only being run after the differential backup is completed.

Either that, or someone ran a manual transaction log backup aafter the automated differential and before the automated transaction log backup. Check you SQL and NT Event logs. It should be recorded in one of them.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Also, you can query msdb.dbo.backupset to see what backups were completed if you don't have access to the logs for the time in question.

Shah_hs Ten Centuries Points: 1117 More actions · Answer 5

I even tried recreating the whole bkp and this time again i could not restore file 4 (TLog bkp file). I checked all truncate log is never runned. Why i am not able to restore tlog file??

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Have you checked the SQL Server Logs for the day that the full backup, differential backup, and transaction log backups you are attempting to restore were taken? If no backup log with truncate_only was run, the only other possibility is that there is a missing transaction log backup.

You can also query msdb.dbo.backupset for the same information. Read BOL for more info on this and related tables.

Rajasekhara Reddy SSC Enthusiast Points: 116 More actions · Answer 7

Hi,

Check ur restore database code once again,

you are using different database name in Restore Diff backup. ( i.e TelehopBilling insteadof ABC)

Before restoring the database you confirm the file names by executing Restore filelistonly against the backup device.

Shah_hs Ten Centuries Points: 1117 More actions · Answer 8

Shah_hs

Ten Centuries

Points: 1117

April 25, 2007 at 9:14 am

#702407

I have corrected. It was typo.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

If you are still have a problem with the restore, have you checked the SQL Logs or msdb.dbo.backupset table to see if there is a missing transaction log backup?

Shah_hs Ten Centuries Points: 1117 More actions · Answer 10

I checked the log but could not find any info. Also the table backupset doesnot give much help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

What information is available in the msdb.dbo.backupset table for the day in question?

Shah_hs Ten Centuries Points: 1117 More actions · Answer 12

Further, one thing i need to mention that during that day the TLog backup failed once but on its next run it succeeded. See the error below when it failed;

Executed as user: sa. Cannot open backup device 'ABC_BackUp'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.

Since it succeeded in next run, does this matter? Also, i am not good at looking msdb.dbo.backupset table, so kindly pls. guide what should is see when doing such checks of restore failure? Also looking at the error what could be the reason of above error.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

The simplest query is:

select * from msdb.dbo.backupset where backup_start_date between <earliest datetime> and <latest datetime>

Shah_hs Ten Centuries Points: 1117 More actions · Answer 14

I mean to say what we need to look for. It simply gives the sequence of backups created as similar to ;RESTORE HEADERONLY FROM ABC_BackUP? Also what could be the reason for LSN mismatches and why the device failed? Knowing this may lead to know exact cause of restore failure.

Thanks