3

I have a job that drops and re-creates a snapshot, it's currently failing when trying to create the database as it says the database already exists.

The database however does not have an entry in master.sys.databases, and is not visible in SSMS.

The [database].ss file does exist, and I can even run queries on tables in the database.

I'm not sure how to drop the database fully however, if I try to drop, I get the folowing: 

drop database [database_snapshot]

Msg 3701, Level 11, State 11, Line 1
Cannot drop the database '[database_snapshot]', because it does not exist or you do not have permission.

(I know it's not a permissions issue)

Similarly I am unable to create the database:

CREATE DATABASE [database_snapshot] ON 
(Name = N'[database]', FILENAME = N'[path to .ss]')
AS SNAPSHOT OF [database]

Msg 1801, Level 16, State 3, Line 2
Database '[database_snapshot]' already exists. Choose a different database name.

If anyone has ideas on how to proceed here, it would be most appreciated. :)

Improve this question

2 Answers 2

Reset to default
2

We ended up renaming the .ss file while the service was down. When it came back up again we could create a new snapshot. It's not ideal that services had to be down for us to resolve this, but it's better than nothing.

Improve this answer
1

this might help:

ALTER DATABASE [Your DB Here] SET OFFLINE

Drop database [Your DB snapshot here]
Improve this answer
1
  • Thanks for the suggestion, unfortunately (or rather, fortunately for us) it has been resolved so I can't test this, although I'd suspect this will not work as dropping the snapshot is something we do often, and it doesn't require the main db to be offline.
    – Sam Peacey
    Jul 25, 2018 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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