Don't Use Delimiters In Your Object Names!

It started with a failed backup status check:

Msg 50000, Level 16, State 1, Line 73
** Backups listed in this file are missing / out of date **

db                status   recovery model   last full backup   Full BU hours old
[FinanceDB]       ONLINE   SIMPLE           NULL             NULL

Check out the database name - [FinanceDB],  At first my response was something like:

http://s.quickmeme.com/img/15/151d67b9a73228d44b1bfabea0d012b54b9cd2821a25bf4b4be1bad10c41a95d.jpg

I went and looked on the server, and sure enough in Management Studio I saw one database named "FinanceDB" and a database named "[FinanceDB]".

This was on a SQL 2008R2 instance, but as a test I created a database named [test] on my local SQL 2014 instance and sure enough it worked!

The source of the problem at the client was the LiteSpeed maintenance plan.  Even though the backup task was set to backup all user databases, it wasn't picking up the square-bracketed database.

On my test system I set up a "regular" SQL Server maintenance plan, and it had did not have the same problem - the backup job did pick up the square-bracketed [test] database.

I next ran the Ola Hallengren DatabaseBackup that I already had installed, and it also picked up the rogue database, creating a backup named TestServer_[test]_FULL_20160404_143721.bak.

--

Since I don't have LiteSpeed on my test system I created a quick test on the client's test system  (SQL 2008R2) - I created a new database named [test] and created a new LiteSpeed maintenance plan to back up *just* that database - the job failed with this error:

Executed as user: Domain\User. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:47:35 PM  Progress: 2016-04-04 15:47:35.81     Source: {D8AD0CC9-710A-4C59-A8E6-1B9228562535}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp_ma...".: 100% complete  End Progress  Error: 2016-04-04 15:47:37.01     Code: 0xC002F210     Source: Fast Compression Backup 1 Execute SQL Task     Description: Executing the query "execute master..xp_slssqlmaint N'-BkUpMedia DISK -..." failed with the following error: "LiteSpeed? for SQL Server? 8.1.0.644  ? 2015 Dell Inc.    Database 'test' is invalid: not found    Msg 62401, Level 16, State 1, Line 0: Database 'test' is invalid: not found    ".   End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:47:35 PM  Finished: 3:47:37 PM  Elapsed:  1.809 seconds.  The package execution failed.  The step failed.

That was strange - remember the original backup job succeeded (although not backing up the square-bracketed database).  I considered and realized in the original situation there were two databases - one with brackets and one without.  Sure enough when I created a database just named "test" and included it in my new maintenance plan, the job succeeded.

How did it succeed?  It backed up "test" TWICE:

The LiteSpeed Fast Compression backup backed up the "test" database twice (Fast Compression runs a FULL every so often and DIFF's in-between - this is why two backups in a row results in a FULL and a DIFF).  I ran the job again and saw the same thing:

I verified by creating an object in [test] and running the backups again and running a restore - what I was backing up was truly "test" over and over.

--

This is not about bashing Dell/Quest LiteSpeed - it is about pointing out a shortcoming in how SQL Server allows us to name objects and a cautionary tale on that naming.

Although it worked in most of the test scenarios above, you can see how the square-bracketed database name failed under one maintenance scenario - and not only did it fail, it failed in a particularly nefarious way because the backup job *didn't* fail - it just didn't pick up the database.

--

I re-created my [test] database on my local instance and wanted to see what the syntax would look like for a DROP - I went through the SCRIPT action in Management Studio and ended up with this:

USE [master]

GO

/****** Object:  Database [[test]]    Script Date: 4/4/2016 2:00:55 PM ******/

DROP DATABASE [[test]]]

GO

Note the *three* square brackets at the end of the DROP statement – two open brackets but three close brackets.

A syntax checker parse showed statement OK – when I removed one of the closing brackets, so that I had two open and two close, the parse showed me this:

Msg 105, Level 15, State 1, Line 5

Unclosed quotation mark after the character string '[test]

GO

'.

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '[test]

GO

…and an execution resulted in the same errors.  When I re-added the third close square bracket, my DROP statement succceeded.

A script of a simple BACKUP statement resulted in the same thing:

BACKUP DATABASE [[test]]]

TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\[test].bak' WITH NOFORMAT, NOINIT,  NAME = N'[test]-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--

This third bracket may be the source of the issue in LiteSpeed and it may not - but it shows the importance of handling object names properly and how touchy the parser can be when you don't make it happy.

--

At the end of the day - just don't do it.  There is always going to be some other name that is just as useful as any name that includes a delimiter, and you don't need to handle the bizarre and inconsistent scenarios that will arise.

Hope this helps!