SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to restore .trn files in databases


how to restore .trn files in databases

Author
Message
zahidshah
zahidshah
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 86
Dear all,

i have restore backup till last night

we have the trn every 10 mint
now, i want to restore datbase till today 12:10Pm,
can any body help me how to restore databse till time
Adiga
Adiga
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2680 Visits: 21012
This article has all the details.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
zahidshah
zahidshah
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 86
Dear All,

we folows the below query

1)
Restore databse TESTDB

from disk='c:\backup\testbackup.bak'

with replace,norecory

After some changes in database, we want to restore the data,we have the trn file

2)
Restore log TESTDB

from disk='c:\transcation\testdb_20101205.trn'

with norecovery

but, we got the msg

Msg 3117, Level 16, State 4, 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.
please help me

regards,
zahid shah
Adiga
Adiga
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2680 Visits: 21012
The transaction log backups must be restored in the same sequence as they were backed up after the FULL backup. The log backup that you are trying to restore, was it taken immediately taken after the full backup?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87741 Visits: 45272
zahidshah (12/6/2010)

After some changes in database, we want to restore the data,we have the trn file


What do you mean by 'after some changes'?

The error indicates that you brought the database online (not that some logs are missing, that would be a mismatched LSN error). Once the database is online (recovered) no further restores can be done. If you want to restore logs, you need to restore the full backup WITH NORECOVERY, then restore the logs WITH NORECOVERY, with nothing happening in between.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Malcolm Daughtree
Malcolm Daughtree
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 335
I think you need to do some reading in Books on line regardsing the role of the transaction log and the backup and recovery options avalable. This is clearly a misunderstanding on what a transaction log backup means.

Read here and follow the further read contained therein

http://msdn.microsoft.com/en-us/library/ms177446.aspx

CodeOn
:-P
zahidshah
zahidshah
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 86
on tuesday we want to restore databse on stand by server,
we have monday full backup and approx 40 transaction log till 1PM of tuesday

we run the below query

Restore database Databasename

from disk='c:\backup\testbackup.bak'

with norecovery

restoration sucssfully till mondy night

Now, we want to restore multiple trans till 1 pm
so we run below query

Restore log databasename

from disk='c:\transcation\testdb_20101205.trn'

with norecovery

but, we got the msg

Msg 3117, Level 16, State 4, 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.

please help me
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87741 Visits: 45272
zahidshah (12/9/2010)
Restore database Databasename

from disk='c:\backup\testbackup.bak'

with norecovery


You ran it exactly like that? You ran no other restore commands prior to the log restore? No one else ran a 'restore database with recovery' statement?

The error indicates that the database was recovered (restore with recovery) before trying to restore the log. If you're doing any restore through the gui, doublecheck that you set the option correctly, it's not the default.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


zahidshah
zahidshah
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 86
Thanks for reply.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search