14

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.
  • you can't issue an alter command on a restoring database, will need to do a drop. – Jason Cumberland Jun 10 '16 at 19:58
25
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.

  • 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
3

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.

  • There is no mention in the question about corrupted backups. – ypercubeᵀᴹ Jun 9 '16 at 10:39

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.