Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.
share|improve this question
you can't issue an alter command on a restoring database, will need to do a drop. – Jason Cumberland Jun 10 at 19:58
up vote 14 down vote accepted
FROM DISK = 'dbname .bak'
WITH REPLACE, RECOVERY --force restore 

or just


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.

share|improve this answer
I believe what you are saying should have worked. My solution though was to restart the sql service and then deleting the databases worked after that. – peter Jun 14 '12 at 2:48
true, while service is off, deleting the transaction logs will always create a new one. all the best – AmmarR Jun 14 '12 at 4:38

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.

share|improve this answer
There is no mention in the question about corrupted backups. – ypercubeᵀᴹ Jun 9 at 10:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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