Problem restoring transaction log

Dear all
I have a problem restoring a transaction log backup.
Platform SQL Server 2000 Standard Edition SP 2
Recovery mode = FULL
Transaction log backed up when empty to give us a nice
point to return to after some tests.
Backup was performed with Enterprise Manager.
Tlog has been grown from 23Gb to 46Gb after backup to
accommodate large load of transactions.
No dump device was created when backing up Tlog. Tlog
backed up to a file.
Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
When restoring in Enterprise Manager (no matter what
options I choose) I get the infamous message:
The preceding restore operation did not specify WITH
NORECOVERY or WITH STANDBY.
Restart the restore sequence, specifying WITH NORECOVERY
or WITH STANDBY for all but the final step.
I've tried from filegroups and from devices. Specified
backups and specified "Leave DB non-operational
for more Tlog restores"
No joy.
I've tried to use a standalone restore script:
RESTORE LOG SD70DbR7
FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
WITH RECOVERY
I get the same message.
I have to specify a file as the TLog is not backed up to a
backup device.
This could be a big mistake on my part
I've specified WITH NORECOVERY and still the same message.
I've said FILE=1 but sill the same message.
When I leave out the very necessary FROM statement I get
teh nice message:
RESTORE LOG successfully processed 0 pages in 0.000
seconds (0.000 MB/sec).
I have some theories as to why I can't restore my TLog but
it seems obscene:
Could the fact that this database has not been backed up
be the issue?
Not on a suitable service pack.
Any suggestions?
Thanks
/SteveYou have to restore the database before you can restore the log. The error
message is saying that you did not do a restore leaving the server in
"norecovery" or "standby" mode so you cannot restore the log. Restore the
database backup and all log backups before that t-log backup using
norecovery and then apply that restore with recovery.
Christian Smith
"Steven Devaney" <Steven.Devaney@.Nospam.thx> wrote in message
news:1281a01c3f646$a327e430$a401280a@.phx.gbl...
> Dear all
> I have a problem restoring a transaction log backup.
> Platform SQL Server 2000 Standard Edition SP 2
> Recovery mode = FULL
> Transaction log backed up when empty to give us a nice
> point to return to after some tests.
> Backup was performed with Enterprise Manager.
> Tlog has been grown from 23Gb to 46Gb after backup to
> accommodate large load of transactions.
> No dump device was created when backing up Tlog. Tlog
> backed up to a file.
> Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
> When restoring in Enterprise Manager (no matter what
> options I choose) I get the infamous message:
> The preceding restore operation did not specify WITH
> NORECOVERY or WITH STANDBY.
> Restart the restore sequence, specifying WITH NORECOVERY
> or WITH STANDBY for all but the final step.
> I've tried from filegroups and from devices. Specified
> backups and specified "Leave DB non-operational
> for more Tlog restores"
> No joy.
> I've tried to use a standalone restore script:
> RESTORE LOG SD70DbR7
> FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
> WITH RECOVERY
> I get the same message.
> I have to specify a file as the TLog is not backed up to a
> backup device.
> This could be a big mistake on my part
> I've specified WITH NORECOVERY and still the same message.
> I've said FILE=1 but sill the same message.
> When I leave out the very necessary FROM statement I get
> teh nice message:
> RESTORE LOG successfully processed 0 pages in 0.000
> seconds (0.000 MB/sec).
> I have some theories as to why I can't restore my TLog but
> it seems obscene:
> Could the fact that this database has not been backed up
> be the issue?
> Not on a suitable service pack.
> Any suggestions?
>
> Thanks
> /Steve
>|||Thank you Christian.
As there is no space to do a backup to, I will have to go
for a plan B.
Thanks for the advice - at least it stops me searching for
something that can't be done.
>--Original Message--
>You have to restore the database before you can restore
the log. The error
>message is saying that you did not do a restore leaving
the server in
>"norecovery" or "standby" mode so you cannot restore the
log. Restore the
>database backup and all log backups before that t-log
backup using
>norecovery and then apply that restore with recovery.
>Christian Smith
>"Steven Devaney" <Steven.Devaney@.Nospam.thx> wrote in
message
>news:1281a01c3f646$a327e430$a401280a@.phx.gbl...
>> Dear all
>> I have a problem restoring a transaction log backup.
>> Platform SQL Server 2000 Standard Edition SP 2
>> Recovery mode = FULL
>> Transaction log backed up when empty to give us a nice
>> point to return to after some tests.
>> Backup was performed with Enterprise Manager.
>> Tlog has been grown from 23Gb to 46Gb after backup to
>> accommodate large load of transactions.
>> No dump device was created when backing up Tlog. Tlog
>> backed up to a file.
>> Filename: D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog
>> When restoring in Enterprise Manager (no matter what
>> options I choose) I get the infamous message:
>> The preceding restore operation did not specify WITH
>> NORECOVERY or WITH STANDBY.
>> Restart the restore sequence, specifying WITH NORECOVERY
>> or WITH STANDBY for all but the final step.
>> I've tried from filegroups and from devices. Specified
>> backups and specified "Leave DB non-operational
>> for more Tlog restores"
>> No joy.
>> I've tried to use a standalone restore script:
>> RESTORE LOG SD70DbR7
>> FROM DISK ='D:\DBSYS\MSSQL\BACKUP\SD70DbR7-Empty-Tlog'
>> WITH RECOVERY
>> I get the same message.
>> I have to specify a file as the TLog is not backed up
to a
>> backup device.
>> This could be a big mistake on my part
>> I've specified WITH NORECOVERY and still the same
message.
>> I've said FILE=1 but sill the same message.
>> When I leave out the very necessary FROM statement I get
>> teh nice message:
>> RESTORE LOG successfully processed 0 pages in 0.000
>> seconds (0.000 MB/sec).
>> I have some theories as to why I can't restore my TLog
but
>> it seems obscene:
>> Could the fact that this database has not been backed up
>> be the issue?
>> Not on a suitable service pack.
>> Any suggestions?
>>
>> Thanks
>> /Steve
>
>.
>