Problem while restoring T-Log backup

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)

Problem while restoring T-Log backup

Author

Topic

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-13 : 01:31:26

Hi All,

My scenario is something like this.
I have a database "Tran" with 1 tbale "tbl"


create table [tran].dbo.Tbl (i Int)

insert into [tran].dbo.TBl
select 1 union all
select 2 union all
select 3

now i have taken a full backup with following script


BACKUP DATABASE [Tran]
TO DISK = 'd:\Tran_Full.bak'

after this i inserted some more records to "tbl"


insert into [tran].dbo.TBl
select 4 union all
select 5 union all
select 6

now take the T-Log backup


BACKUP LOG [Tran]
TO DISK = 'd:\Tran_Log.trn'

after this i deleted some records from "tbl"


delete from [tran].dbo.tbl
where i in (4, 5, 6)

after this when want to restore the database with following script it is giving me the error


-- Restore Script
-- First restore the Full Backup
RESTORE DATABASE [Tran]
FROM DISK = 'd:\Tran_Full.bak'
WITH	MOVE 'Tran' TO 'D:\MSSQL2005\MSSQL.2\MSSQL\DATA\Tran.mdf',
		MOVE 'Tran_log' TO 'D:\MSSQL2005\MSSQL.2\MSSQL\DATA\Tran_log.ldf',
NORECOVERY

-- Then restore the Tran-Log Backups in Order
RESTORE LOG [Tran]
FROM DISK = 'd:\Tran_Log.trn'
RECOVERY

-- error msg
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.

is any thing wrong in the script.
Thanks in advance

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-13 : 02:52:43

All the log backups except the very last one need to be restored WITH NORECOVERY. Once you restore any backup WITH RECOVERY, no more log backups can be applied.

--
Gail Shaw
SQL Server MVP

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-13 : 03:04:38

I have taken only one log backup so i used RECOVERY.

i am getting the error while restoring the Full backup.

Sundaresan
Starting Member

28 Posts

Posted - 2008-08-13 : 03:11:55

Hi,

Dont give recovery stmt at the end of

RESTORE LOG [Tran]
FROM DISK='D:\Tran_Full.trn'

Try the above syntax after restoring the full backup with NORECOVERY MODE

Sundaresan.R

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-13 : 03:47:45

just fire

restore Database tran from disk = 'D:\tran_full.bak' with norecovery
restore log tranfrom disk from disk = 'D:\Tran_Log.trn'with recovery
command

but make sure that you have full backup and trn log backup

Regards,
Hitesh Soni

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-13 : 04:43:05

after


restore Database tran from disk = 'D:\tran_full.bak' with norecovery
restore log tranfrom disk from disk = 'D:\Tran_Log.trn'with recovery

still getting the same error

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-13 : 12:36:40

First run this:
This will give you all backups starting from your full backup (order in LSN for Tlog)

Then Restore all backups with Norecovery and last Tlog with Recovery option .Then you should be good. Otherwise take Diff backup and restore fullbackup with Norecovery and Diff backup with Recovery:

--Determine all backups starting from Last Full Backups.
declare @databasename as varchar(100)

set @databasename = 'Tran'

Select database_name + rtrim(case when type ='L' then space(5)+ name

when type ='D' then space(1)+name

when type ='I' then space(3)+name else '' end)+ space(3)+

convert(varchar, Backup_Start_date, 121)

from msdb..backupset

where database_name = @databasename

and Backup_Start_Date >= (select max(Backup_Start_Date)
from msdb..backupset
where database_name = @databasename
and type = 'D')
order by Backup_Start_date

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 12:40:34

WITH NORECOVERY,REPLACE on the full backup.

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

Subscribe to my blog

Haywood
Posting Yak Master

221 Posts

Posted - 2008-08-13 : 13:15:33

Since you have only the one tranlog file, you have to specify the FILE option to tell it which log backup within the file to recover from. In your case, the first.


FILE = { backup_set_file_number | @backup_set_file_number } ]


restore log foo
from  disk = 'c:\foo.trn'
WITH FILE = 1
, RECOVERY

Your friendly High-Tech Janitor:

http://grayburn.wordpress.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-13 : 13:43:23

He doesn't need the FILE option as he doesn't have multiple backups inside the file, but at least it won't hurt to have it there since FILE = 1.

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

Subscribe to my blog

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-14 : 00:16:50

Thanks Tara..

i used WITH NORECOVERY,REPLACE on the full backup and it works fine.

Thank you all for helping me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources