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?