3

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.

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.