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!
