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 filesmicrosoft sql servermssql10.mssqlservermssqlbackupolddb.bak' GO RESTORE DATABASE NewDB FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBackupolddb.bak' WITH NORECOVERY, REPLACE, MOVE 'OldDB' TO 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAnewdb.mdf', MOVE 'OldDB_Log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAnewdb.ldf'
Any ideas why my db is still in restoring state if it says it completed successfully?
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