Read-only DB attach/detach

Question

Hello,

I had a DB that was read-only, a log-shipping copy from the live version.
Somehow the log-shipping was not working so I wanted to delete the database and restore from a more recent backup, so I could recreate the log-shipping event.

It seems that somehow because the DB was read-only and I used detach now I can't attach it anymore can't even create a new DB with the same name.

Is the problem that the DB was read-only, or I did something else wrong? If it is the former, how can I re-attach the DB?

Regards,

Answer 1

When you detached the database - the files were probably modified and the permissions removed. This is a known issue with detaching a database - which causes the kinds of issues you are seeing now.

I am assuming that when you try to restore it is erring due to the mdf/ldf files that already exist - and the system cannot overwrite the files even when you use the option to overwrite. If that is the case...it is due to the permissions left over on those files after detaching.

You can manually move or delete the mdf/ldf files for the old database - and then the restore should work. Just to be safe - I would move the files to a safe location and only delete them after you have restored the database, but since you are planning on restoring from backup anyways there shouldn't be any issues with just deleting those files.

The other option would be to reset the permissions on the files - once those have been reset you should be able to attach or restore as needed.

Answer 2

Database 'bagsAFK35_BI' already exists. Choose a different database name

Does the database already exists on that SQL Server, as the error message says?

Answer 3

The database only exists as a file MDF/LDF in a folder, I detached it from SQL Management Studio

Answer 4

The error message tells a different story. Does

select *
from sys.databases
where name = 'bagsAFK35_BI'

return a result?

Answer 5

Hi @Dren Selimi ,

>Somehow the log-shipping was not working so I wanted to delete the database and restore from a more recent backup, so I could recreate the log-shipping event.

You can reinitialize the log shipping by using backup/restore or delete/re-add the secondary database.

>The database only exists as a file MDF/LDF in a folder, I detached it from SQL Management Studio

Are the MDF/LDF files of the database still located in the default SQL Server data folder, did you move them after detaching the database?
Try to run: drop database bagsAFK35_BI.

Edit based on following response:
Try to remove this secondary database from log shipping, and restart sql server service.