Restoring SQL2000 DB to SQLExpress on different machine

Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.