Question :
I am running log shipping with SQL server 2008 R2.
I have a situation where the secondary database drive ran out of space and was not applying log shipping transaction logs.
The way I want to fix this is delete the databases at the secondary and configure log shipping from scratch.
The problem I have now is that my secondary databases are in the restoring state, and I cannot delete them. How can I proceed?
For instance if I try to take them offline I get the error,
ALTER DATABASE is not permitted while the database is in the Restoring state.
Answer :
RESTORE DATABASE dbname
FROM DISK = 'dbname .bak'
WITH REPLACE, RECOVERY --force restore
or just
RESTORE DATABASE dbname WITH RECOVERY
the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it
RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.
That should bring the database online. Then you can delete it & try again.
Best way as i have done with this problem is write this command ‘drop database [database name]’ and then create new one with same name and then restore database from another .bak file because .bak file is corrupted or faulty.