Restoring SQL Server Database Command Line Error

Question

I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The command window lets me know the restore finished successfully; however, the database is in a restoring state still, and cannot be accessed. Because of that, the rest of my batch process fails. How can I get the database out of the restoring state, and back into an online state so that I can run additional batch process scripts?

I'm using this to do it:

restore filelistonly
from disk = 'c:\program files\microsoft sql server\mssql10.mssqlserver\mssql\backup\olddb.bak'
GO
RESTORE DATABASE NewDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\olddb.bak'
WITH NORECOVERY, REPLACE,
MOVE 'OldDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\newdb.mdf',
MOVE 'OldDB_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\newdb.ldf'

Any ideas why my db is still in restoring state if it says it completed successfully?

Thanks.

Answer 1

You used NORECOVERY which leaves the database ready to receive diff/log restores

You can remove it above, or simply run this

RESTORE DATABASE NewDB WITH RECOVERY

See MSDN RESTORE, look for "[ RECOVERY | NORECOVERY | STANDBY ]" header