2

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. :)

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.

0

this might help:

ALTER DATABASE [Your DB Here] SET OFFLINE

Drop database [Your DB snapshot here] 
  • 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 '18 at 0:13

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.