Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to restore .trn files in databases Expand / Collapse
Author
Message
Posted Sunday, December 5, 2010 11:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:55 AM
Points: 9, Visits: 57
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




Post #1030464
Posted Monday, December 6, 2010 1:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
This article has all the details.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1030481
Posted Monday, December 6, 2010 11:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:55 AM
Points: 9, Visits: 57
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
Post #1031036
Posted Monday, December 6, 2010 11:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1031042
Posted Tuesday, December 7, 2010 12:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1031071
Posted Tuesday, December 7, 2010 3:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 9:50 PM
Points: 189, 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
Post #1031575
Posted Thursday, December 9, 2010 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:55 AM
Points: 9, Visits: 57
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

Post #1032361
Posted Thursday, December 9, 2010 9:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1032522
Posted Thursday, December 9, 2010 10:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:55 AM
Points: 9, Visits: 57
Thanks for reply.....
Post #1032832
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse