February 17, 2014 at 3:00 am
1. Created a database with a couple of tables with no data.
2. Taken a full database backup - db.bak.
3. Deleted the database.
4. Restored the database db.bak and filled the tables with some data.
5. Taken log backup - dblog.trn
BACKUP LOG <dbname> to DISK='D:\Demo\dblog.trn' WITH NO_TRUNCATE, INIT
6. Dropped the database again.
7. Restored the database again from db.bak.
8. But when I am trying to restore log file dblog.trn on this database, i keep getting this error :
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
What is wrong, can someone enlighten me please. I am new to DB Admin. Thanks.
February 17, 2014 at 3:17 am
In step 7 you need to run the restore WITH NORECOVERY. That leaves the DB nonoperational and able to restore additional backups (differential or log). If you run the restore with the default options, SQL recovers the database and brings it online and no further backups can be restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2014 at 3:23 am
Thank you Gila monster. It worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply