Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

up vote 3 down vote accepted
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.

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

Your Answer

 
discard

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.