How do I drop a database which has brackets in its name?

Question

I have a database which has brackets in its name. (Don't ask! :-\ )

It shows in SSMS but not in Azure because it was canceled while creating. It does not show in the database listing in Azure. It shows in SSMS but it cannot be deleted by right-clicking. I'd like to drop it using SQL but I can't figure out the syntax to include the brackets as characters for the database name, as opposed to the escape/delimiter character.

I've tried the following to no avail:

drop database [xxx_2024-07-24]

Msg 911, Level 16, State 1, Line 1
Database 'xxx_2024-07-24' does not exist. Make sure that the name is entered correctly.

drop database [[xxx_2024-07-24]]

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '[xxx_2024-07-24]'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '[xxx_2024-07-24]'.

drop database [[[xxx_2024-07-24]]]

Msg 911, Level 16, State 1, Line 1
Database '[[xxx_2024-07-24]' does not exist. Make sure that the name is entered correctly.

Any idea on the correct syntax to drop this database? [xxx_2024-07-24]

Answer 1

With SET QUOTED_IDENTIFIER set to ON (as it always should be):

DROP DATABASE "[Oops]";

You can also escape the square brackets, but this is less readable:

DROP DATABASE [[Oops]]];

• The first [ starts the delimited identifier.
• The second [ is part of the identifier.
• The first two ]] are an escaped ] as part of the identifier.
• The final ] completes the delimited identifier.

Answer 2

As Martin Smith pointed out, you can use QUOTENAME() function to see the correct escaping if you don't remember the exact escaping rules.

E.g.:

SELECT QUOTENAME('[xxx_2024-07-24]')

Answer 3

OK, I hit upon the solution while typing up this question. The clue is in the final error message.

drop database [[[xxx_2024-07-24]]]

Msg 911, Level 16, State 1, Line 1 Database '[[xxx_2024-07-24]' does not exist. Make sure that the name is entered correctly.

Take a look at the database name which is emitted in the error message. The brackets are unbalanced. There's one at the end, but two at the beginning. From this I deduce, that if I leave the trailing three brackets as entered, but reduce the leading brackets to two, this should work:

drop database [[xxx_2024-07-24]]]

YES! This works!

Perhaps one day I'll figure out the why behind this encoding mystery. For now, note to self and everyone else:

Don't include brackets as literal characters on the name of any database artifact!!!