Sql-server – Restoring SQL Server Database Command Line Error

restoresql serversql-server-2008sqlcmd

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.

Best Answer

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