Point In Time Recovery - SQL Server Forums

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

SQL Server Administration (2005)

Point In Time Recovery

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-02 : 01:01:15

When I tried to restore to its Point in Time (PIT) Recovery I am getting this error.

I am trying to get the restoration done in chronological order of loading .BAK followed by .TRN’s the above error is prevalent.

These were the Errors:

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "WYCOR" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3117, Level 16, State 1, Line 5
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 5
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 9
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 9
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 13
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 13
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 17
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 17
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 21
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 21
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 25
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 25
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 30
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 30
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 34
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 34
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 38
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 38
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 42
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 42
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 46
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 46
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 50
Cannot open backup device 'R:\BACKUP\WYCOR_backup_2010080208450.trn'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 50
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 54
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 54
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 58
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 58
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 62
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 62
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 66
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 66
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 70
Cannot open backup device 'R:\BACKUP\WYCOR_backup_2010080201000.trn'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 70
RESTORE LOG is terminating abnormally.
Msg 3217, Level 16, State 1, Line 75
Invalid value specified for STOPAT parameter.
Msg 3013, Level 16, State 1, Line 75
RESTORE LOG is terminating abnormally.

Can anyone let me know where I went wrong. Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-02 : 09:37:14

let's see your restore statement

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-02 : 10:19:49

It looks like you are trying to restore over an existing database, that still has contents in its transaction log that has not been backed up. Either back up the existing transaction log first, or if you do not need the contents in there, use the REPLACE option in your full database restore command.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-02 : 19:21:54

The Steps I have executed on the T-SQL and the Errors occured.

I have already Restored the backup onto the Database and later took the Log backup then I have issued in this pattern.

ALTER DATABASE WYCOR SET RECOVERY FULL

RESTORE DATABASE WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008012328.bak'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020600.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020615.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020630.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020645.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020700.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020715.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020730.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020745.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020800.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020815.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020830.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020845.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020900.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020915.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020930.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020945.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_2010080201000.trn'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_2010080201015.trn'
WITH RECOVERY,
STOPAT = 'Aug 02, 2010 10:15:00 AM'

ERRORS:
Msg 3117, Level 16, State 1, Line 7
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 11
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 15
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 15
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 19
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 19
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 23
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 23
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 28
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 28
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 32
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 32
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 36
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 36
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 40
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 40
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 44
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 44
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 48
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 48
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 52
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 52
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 56
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 56
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 60
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 60
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 64
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 64
RESTORE LOG is terminating abnormally.
Msg 3117, Level 16, State 1, Line 68
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 68
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 72
Cannot open backup device 'R:\BACKUP\WYCOR_backup_2010080201000.trn'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 72
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 76
Cannot open backup device 'R:\BACKUP\WYCOR_backup_2010080201015.trn'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 76
RESTORE LOG is terminating abnormally.

Can Anyone give me the needed advise. Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-08-02 : 21:28:35

Did you backup Tail_Log with NO_Truncate Option?? Don't you have Differential backups in between?

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-02 : 22:12:44

I am trying to test the Databases transactions that were happened with the intention to know the culprit who made the particular transaction.

So I have the same database on the testing environment and copied across from Production to testing with TRN files and started doing the Point in Time Recovery. However, I was held up as above indicated with the errors.

So Now I dont have any differential backups in between since it is straight forward with .BAK followed by .TRN extension. and trying to restore it till that particular time.

Any clues for troubleshooting... Thanks.

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-03 : 02:24:33

Can you confirm that the 'R:\BACKUP\WYCOR_backup_201008012328.bak' contains a full database backup, and not a differential database backup? Run RESTORE HEADERONLY on that file e.g.

RESTORE HEADERONLY FROM DISK = 'R:\BACKUP\WYCOR_backup_201008012328.bak'

What is the value of the 'BackupType' column?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-03 : 18:19:21

The value of the 'BackupType' column = 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-03 : 18:38:05

Use WITH NORECOVERY, REPLACE.

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

Subscribe to my blog

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-03 : 22:27:26

I have followed these steps:

Initially Restored Backup from Production to the testing server's Database;

Then tried to do the Point in Time Recovery:

In the Restore script as indicated above, executed with header only then I got the TYPE = 1;

Then I have scripted all the Restore commands using the TRN's which are also copied from the Production Server and started with 6 AM till 10:15 Am which where I have found the biggest TRN, which is abnormal.

(Intention to pluck out what would be the reason to make such big transaction in the span of 15 minutes generation and also finding the culprit user)

However, the 10:15 AM Statement happens to be last transaction and prompted with STOPAT Phrase.

But after that I have changed each script WITH NO RECOVERY, REPLACE statement.

Still I got the same error for 6 AM till 10 AM.

Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
For 10:15 AM

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'R:\BACKUP\WYCOR_backup_2010080201000.trn'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Can anyone help me as I was out of order here. Many Thanks for your posts.

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 02:41:16

"Cannot open backup device 'R:\BACKUP\WYCOR_backup_2010080201000.trn'. Operating system error 2(The system cannot find the file specified.)"

Did you check the filename exists? Looks like you have an extra "0" in there - 2010080201000

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-04 : 17:55:39

Exactly Correct the Zero is existing.

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-04 : 20:56:26

When you run just the full database restore on its own:

RESTORE DATABASE WYCOR FROM DISK = 'R:\BACKUP\WYCOR_backup_201008012328.bak'
WITH NORECOVERY, REPLACE

does it succeed?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-04 : 22:09:20

It Got worked but, I want to see how the transactions were getting into the database using the TRNs. But it got failed.

POint In Time Recovery will tell me how and why the transaction got so big at particular juncture is all my aim, not just recover or restore of database.

Till no clue why it got not take off, any clues further with this?
Thanks.

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-05 : 02:23:47

Ok, so we know the full database restore succeeded. Now if you restore only the first transaction log backup that was taken after the full database backup, does it succeed?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

Kristen
Test

22859 Posts

Posted - 2010-08-05 : 03:36:50

quote:
Originally posted by dbalearner

Exactly Correct the Zero is existing.

OK, I assumed that the file for 10:00 was the only file that failed, re-reading your post it looks like all files from 6:00 onwards failed.

Are you sure that the server, where the SQL Service is running, can see all the R:\BACKUP\*.* files?

Can you successfully do a

RESTORE HEADERONLY FROM DISK = ...

on each/every file?

That will prove that the SQL Service can see the file, has O/S permissions to read the file, and that the filename is spelt correctly, etc. and you can double check that BackupType = 2 (transaction backup) for each file.

Sorry if you have already done this.

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-05 : 17:45:34

Big Thanks to All who patiently answered my blurrs here !!!!

Yet I have done the RESTORE HEADER and got BACKUP TYPE 1 for the BAK and 2 for TRN files.

May Be I need to CORRECTLY FOLLOW the POINT IN TIME Recovery STEPS.

However going round the circle can anyone see where I am getting wrong in POINT IN TIME (PIT) Recovery Steps. There after I can another set being uploaded to test the environment which is the only way to find the culprits.

Once again Big thanks for Answering all.

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 02:52:26

"May Be I need to CORRECTLY FOLLOW the POINT IN TIME Recovery STEPS"

They are:

Restore FULL backup with NORECOVERY
Optionally restore a later DIFFERNTIAL backup which was based on that FULL backup (with NORECOVERY)
Restore ALL TLog backups, starting with the next one made after the previous step (with NORECOVERY)
For the restore from the final TLog backup file use STOPAT
Either use WITH RECOVERY for the previous step, or then do a final RESTORE MyDatabase WITH RECOVERY (i.e. NOT specifying any backup file)

The error message "The log or differential backup cannot be restored because no files are ready to rollforward." normally means that NORECOVERY was not used for the previous Restore.

"RESTORE DATABASE WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008012328.bak'
WITH NORECOVERY

RESTORE LOG WYCOR
FROM DISK = 'R:\BACKUP\WYCOR_backup_201008020600.trn'
WITH NORECOVERY"

Were there no Tlog backup files between 2010-08-01 23:28 full backup and 2010-08-02 06:00 transaction backup?

RESTORE HEADER will show you the LSN for each backup. The TLog FirstLSN should be before the Full backup FirstLSN, and its LastLSN should be after that of the Full Backup

Is it possible that R:\BACKUP\WYCOR_backup_201008012328.bak and R:\BACKUP\WYCOR_backup_201008020600.trn were made from different databases, and thus don't have valid LSN sequences to restore from? Not sure which values in RESTORE HEADER should be the same for all the backups, perhaps BindingID?? FamilyGUID??

(I think you would have got a different error message if that was the case ... but I'm running out of ideas!)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-06 : 04:50:07

I was sort of bored so I created a script that makes all the restore commands for you. Unfortunately I don't have 2005 available at the moment so it's for 2008 only but maybe you can make some use for it after all:

http://thefirstsql.com/2010/08/06/create-a-full-point-in-time-restore-script-automatically/

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-08-09 : 01:29:47

I have posted the error above with the RESTORE Statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources