May 18, 2011 at 2:11 pm
Using SQL 2008 R2 Enterprise, create a database named 'MyDb ' with a space at the end of the database name.
Note: when you are in Management Studio looking at databases, you would be VERY hard pressed to tell there is a trailing space in that database name.
Now set up a Maintenance Plan, just add in a Backup Database Task, choose just that one database, and be sure to check Create a sub-directory for each database.
Run it, and it will fail.
In Management Studio, under SQL Server Logs, look at the Current log, you should see a message similar to this:
BackupDiskFile::CreateMedia: Backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE\MSSQL\Backup\MyDb \MyDb _backup_2011_05_18_160000_2704322.bak' failed to create. Operating system error 3(The system cannot find the path specified.).
Note that in Windows a folder cannot have a space at the end. You can enter one in, but Windows just deletes it. This Maintenance Plan tried to create a path using the name of the database, which DOES have a space at the end. So a path was created -- without a space at the end of the folder name -- which is fine. But then the SQL statement that is trying to save the backup file is trying to save it to the path which DOES have the space at the end of the name, and that path does not exist, therefore the error about the OS can't find the path.
It seems SQL can/should check, when you are creating a database with a name that has a trailing space, and warn you that bad things such as this can happen.
BTW, this was very hard for me to hunt down!
Do you think this qualifies as a bug, to report to Microsoft?
May 18, 2011 at 2:54 pm
It does seem like a bug but in terms of T-SQL code SQL Server does not seem to mind. It appears you can safely rename it to remove the trailing space...even if there is code referencing the DB in square brackets including the trailing space. On SQL 2008R2:
-- no trailing space
CREATE DATABASE [test_db];
GO
USE [test_db];
GO
CREATE TABLE dbo.test_table (id INT IDENTITY(1,1));
GO
INSERT INTO dbo.test_table DEFAULT VALUES ;
GO
-- notice the trailing space but the query still works
SELECT * FROM [test_db ].dbo.test_table;
GO
USE [master];
go
-- notice the trailing space again and the DROP still works
DROP DATABASE [test_db ];
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 18, 2011 at 2:58 pm
...
Do you think this qualifies as a bug, to report to Microsoft?
It's probably easier to just not create databases with a trailing space in the name.
May 18, 2011 at 3:27 pm
Michael Valentine Jones (5/18/2011)
It's probably easier to just not create databases with a trailing space in the name.
Agreed, but this is a server I inherited, with over 100 databases on it including this one, and our enterprise monitoring tool was blasting out it-is-the-end-of-the-world-your-backups-are-failing! alerts, so I had to hunt it down, and it was all very silly, and presumably could be either disallowed, or at least warned against, in SQL. At least the GUI. It is a problem caused by the behavior of MICROSOFT SQL Server with MICROSOFT Windows, after all.
May 18, 2011 at 4:08 pm
It's kind of a corner-case but I did not want to bother stating the obvious about not adding trailing spaces in the first place 😎
Did you read my post?
It was already reported as a bug so it appears as though you can safely rename the DB and just move on.
http://connect.microsoft.com/SQLServer/feedback/details/627321/maintenance-backup-step-could-faill
If you want to find these on other instances this query will find them:
SELECT *
FROM sys.databases
WHERE RIGHT(name, 1) = ' ' ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2012 at 11:47 am
We have a database that's had a trailing space at the end of it's name since before I started with the company. So far they've resisted letting me fix it because they're worried that the application that uses it (built in-house) might develop issues.
Previously it wasn't an issue. We were getting backups - we do nightly backups to disk, to tape, and we also do a snapshot with NetApp SnapManager. Recently however, we upgraded from our SQL2005 server to a new SQL2008R2 server. And since then, the SnapManager backups fail for that database. All of the other databases back up just fine on SnapManager (and the disk and tape backups seem unaffected).
I believe the "right" solution is to rename the database and fix the application as necessary. However, in the short term, I am wondering if there is a way to create an alias of some kind. For example, we have a server alias on our other servers so that any queries that reference the old server name via linked server actually go to the new server. Is this possible to do at the database level, so that whenever something points at database [BLAHBLAH ] it's actually getting [BLAHBLAH] instead?
February 13, 2012 at 11:51 am
cphite (2/13/2012)
We have a database that's had a trailing space at the end of it's name since before I started with the company. So far they've resisted letting me fix it because they're worried that the application that uses it (built in-house) might develop issues.Previously it wasn't an issue. We were getting backups - we do nightly backups to disk, to tape, and we also do a snapshot with NetApp SnapManager. Recently however, we upgraded from our SQL2005 server to a new SQL2008R2 server. And since then, the SnapManager backups fail for that database. All of the other databases back up just fine on SnapManager (and the disk and tape backups seem unaffected).
I believe the "right" solution is to rename the database and fix the application as necessary. However, in the short term, I am wondering if there is a way to create an alias of some kind. For example, we have a server alias on our other servers so that any queries that reference the old server name via linked server actually go to the new server. Is this possible to do at the database level, so that whenever something points at database [BLAHBLAH ] it's actually getting [BLAHBLAH] instead?
Not really.
There are ways to provide synonyms for database objects and for linked servers, but not really for a database itself.
Have you contact NetApp to see if they have a workaround you can use for their product?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2012 at 12:04 pm
If I was using native backups, I would script it myself to leave out the space when creating backups... Or simply don't have it create the path, give it the path. If you cannot do that with your 3rd party app, I would think that is a bug for them being that they are not removing the trailing space when trying to create a folder.
Jared
CE - Microsoft
February 14, 2012 at 9:14 am
According to NetApp, they consider this to be a SQL problem. Can't say I'm thrilled with their support at this point.
They initially insisted that it was a SQLVDI problem; but all of the other databases will back up just fine - as long as the one with the space isn't included, in which case the whole job fails. My argument was that if SQLVDI was the issue, none of them would work.
Then they insisted it was a security issue. I made a copy of the database without the space in the name, and backed that up - not a security issue.
At this point, I think I'm just going to insist that we rename the thing and that our programmers suck it up and edit their connection strings if need be. I don't know for the life of me why, when they were doing the work in the first place, they didn't fix the name instead of coding around it :crazy:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply