Restoring a database

[smsinger3]

Hello all.

I have used the following T-SQL to restore a database:


restore database dbname
from db_device
with dbo_only,
norecovery,
stats,
move 'dbname_data' to 'd:\mssql7\data\dbname_data.mdf',
move 'dbname_log' to 'd:\mssql7\data\dbname_log.ldf'

Now, that it is restored, I cannot use it as I get an error message "Database cannot be opened. It is in the middle of a restore". I know that since I specified dbo_only, only the database owner can use it. However, I am logged in as "sa", the system administrator. I then do this command:

sp_dboption 'dbname', 'dbo only use only', 'false'

This is the error I get:
Database 'members' cannot be opened. It is in the middle of a restore. Checkpointing database that was changed.

Does anyone have any ideas how I can see the database again? Your help is greatly appreciated!

Thanks,

Steve S.
sms@hmbnet.com

 

[tlbroadbent]

The problem is the norecovery option. Do you have more transaction or differential backups to restore?

SQL BOL states,

NORECOVERY
Instructs the restore operation to not roll back any uncommitted transactions. Either the NORECOVERY or STANDBY option must be specified if another transaction log has to be applied. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.

SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).

If this is the final restore, either change the norecovery option to recorvery or remove it. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.