Restoring Transaction Logs 
Author Message
 Restoring Transaction Logs

Hi,

When I run the folowing code:

Restore database Destinations_FO3
From  disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK' --'c:\SQL
Server Backups\Destinations\DestSnap.bak'
With recovery, replace,
Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
File = 1

restore Log Destinations_FO3
From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
With norecovery,replace, File = 2

restore Log Destinations_FO3
From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
With norecovery,replace, File = 3

restore Log Destinations_FO3
From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
With norecovery,replace, File = 4

restore Log Destinations_FO3
From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
With recovery,replace, File = 5,
stopat = 'Nov 28, 2001 09:00 AM'

 to restore a database and its log files, I receive the following error
message when I attempt to use the STOPAT STATEMENT:

Server: Msg 4305, Level 16, State 1, Line 16
This backup set cannot be restored because the database has not been rolled
forward far enough. You must first restore all earlier logs before restoring
this log.

I have not received any error for the translog backups that I have scheduled
using the Jobs tool.   Also I know that file number 5 contains transactions
that are older than 9:00am becuase, I begin my transaction log backups at
7:30am every morning.

What could be the problem?

Thanks for your help
Sincerely
Elyse



Sat, 22 May 2004 03:27:52 GMT
 Restoring Transaction Logs

Elyse,

Shouldnt it be

Restore database Destinations_FO3
From  disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
WITH NORECOVERY ,replace
Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
 Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
 File = 1

followed by the tran.log restores in the order you mentioned ?

Either the NORECOVERY or STANDBY option must be specified if transaction
log(s) has to be applied.

Dinesh.


Quote:
> Hi,

> When I run the folowing code:

> Restore database Destinations_FO3
> From  disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK' --'c:\SQL
> Server Backups\Destinations\DestSnap.bak'
> With recovery, replace,
> Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
> Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
> File = 1

> restore Log Destinations_FO3
> From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> With norecovery,replace, File = 2

> restore Log Destinations_FO3
> From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> With norecovery,replace, File = 3

> restore Log Destinations_FO3
> From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> With norecovery,replace, File = 4

> restore Log Destinations_FO3
> From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> With recovery,replace, File = 5,
> stopat = 'Nov 28, 2001 09:00 AM'

>  to restore a database and its log files, I receive the following error
> message when I attempt to use the STOPAT STATEMENT:

> Server: Msg 4305, Level 16, State 1, Line 16
> This backup set cannot be restored because the database has not been
rolled
> forward far enough. You must first restore all earlier logs before
restoring
> this log.

> I have not received any error for the translog backups that I have
scheduled
> using the Jobs tool.   Also I know that file number 5 contains
transactions
> that are older than 9:00am becuase, I begin my transaction log backups at
> 7:30am every morning.

> What could be the problem?

> Thanks for your help
> Sincerely
> Elyse



Sat, 22 May 2004 04:14:39 GMT
 Restoring Transaction Logs
Hi Dinesh,

I'm sorry, I mistyped the first "recovery" word.  It was actually "with
Norecovery".  I ran the script again..just to be sure that the first
"Recovery" was actually "Norecovery", and I received the same message.

Thxs
Elyse



Quote:
> Elyse,

> Shouldnt it be

> Restore database Destinations_FO3
> From  disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> WITH NORECOVERY ,replace
> Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
>  Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
>  File = 1

> followed by the tran.log restores in the order you mentioned ?

> Either the NORECOVERY or STANDBY option must be specified if transaction
> log(s) has to be applied.

> Dinesh.



> > Hi,

> > When I run the folowing code:

> > Restore database Destinations_FO3
> > From  disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK' --'c:\SQL
> > Server Backups\Destinations\DestSnap.bak'
> > With recovery, replace,
> > Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
> > Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
> > File = 1

> > restore Log Destinations_FO3
> > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > With norecovery,replace, File = 2

> > restore Log Destinations_FO3
> > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > With norecovery,replace, File = 3

> > restore Log Destinations_FO3
> > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > With norecovery,replace, File = 4

> > restore Log Destinations_FO3
> > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > With recovery,replace, File = 5,
> > stopat = 'Nov 28, 2001 09:00 AM'

> >  to restore a database and its log files, I receive the following error
> > message when I attempt to use the STOPAT STATEMENT:

> > Server: Msg 4305, Level 16, State 1, Line 16
> > This backup set cannot be restored because the database has not been
> rolled
> > forward far enough. You must first restore all earlier logs before
> restoring
> > this log.

> > I have not received any error for the translog backups that I have
> scheduled
> > using the Jobs tool.   Also I know that file number 5 contains
> transactions
> > that are older than 9:00am becuase, I begin my transaction log backups
at
> > 7:30am every morning.

> > What could be the problem?

> > Thanks for your help
> > Sincerely
> > Elyse



Sat, 22 May 2004 06:10:19 GMT
 Restoring Transaction Logs
Elyse,

I thought so.But just wanted to make sure:)

What the error indicates is the absence of some transaction logs in between.
did you check it with RESTORE HEADERONLY ?

Dinesh.


Quote:
> Hi Dinesh,

> I'm sorry, I mistyped the first "recovery" word.  It was actually "with
> Norecovery".  I ran the script again..just to be sure that the first
> "Recovery" was actually "Norecovery", and I received the same message.

> Thxs
> Elyse



> > Elyse,

> > Shouldnt it be

> > Restore database Destinations_FO3
> > From  disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > WITH NORECOVERY ,replace
> > Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
> >  Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
> >  File = 1

> > followed by the tran.log restores in the order you mentioned ?

> > Either the NORECOVERY or STANDBY option must be specified if transaction
> > log(s) has to be applied.

> > Dinesh.



> > > Hi,

> > > When I run the folowing code:

> > > Restore database Destinations_FO3
> > > From  disk = 'c:\SQL Server

Backups\Destinations\DestSnap.BAK' --'c:\SQL

- Show quoted text -

Quote:
> > > Server Backups\Destinations\DestSnap.bak'
> > > With recovery, replace,
> > > Move 'Destinations_data' to 'c:\mssql7\data\DestSnap_data.mdf',
> > > Move 'Destinations_log' to 'c:\mssql7\data\DestSnap_log.ldf',
> > > File = 1

> > > restore Log Destinations_FO3
> > > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > > With norecovery,replace, File = 2

> > > restore Log Destinations_FO3
> > > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > > With norecovery,replace, File = 3

> > > restore Log Destinations_FO3
> > > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > > With norecovery,replace, File = 4

> > > restore Log Destinations_FO3
> > > From disk = 'c:\SQL Server Backups\Destinations\DestSnap.BAK'
> > > With recovery,replace, File = 5,
> > > stopat = 'Nov 28, 2001 09:00 AM'

> > >  to restore a database and its log files, I receive the following
error
> > > message when I attempt to use the STOPAT STATEMENT:

> > > Server: Msg 4305, Level 16, State 1, Line 16
> > > This backup set cannot be restored because the database has not been
> > rolled
> > > forward far enough. You must first restore all earlier logs before
> > restoring
> > > this log.

> > > I have not received any error for the translog backups that I have
> > scheduled
> > > using the Jobs tool.   Also I know that file number 5 contains
> > transactions
> > > that are older than 9:00am becuase, I begin my transaction log backups
> at
> > > 7:30am every morning.

> > > What could be the problem?

> > > Thanks for your help
> > > Sincerely
> > > Elyse



Sat, 22 May 2004 06:40:53 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Log shipping: Auto restore transaction log?

2. SQL 6.5 can't restore Transaction Logs

3. restore transaction logs to standby

4. Restoring Transaction Logs

5. Restore Transaction Log

6. HELP! need to restore transaction log

7. Restore Transaction Log

8. Restoring transaction log backup generates error 1511 (SQL 6.5)

9. Restoring Transaction Log Without Dump First

10. Restoring Transaction log problems

11. Trying to restore transaction log

12. Unable to restore transaction logs


 
Powered by phpBB® Forum Software