Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked

Rate This
  • Comments 9

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked, with error similar to the below,

“Cannot open backup device 'D...  The package execution fa...  The step failed.,00:00:01,0,0,,,,0”

 

Detailed Error Message
==================

Executed as user: MachineName\SYSTEM. ...9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  7:15:03 AM  Progress: 2008-07-24 07:15:04.11     Source: {134957B2-5C5F-4D4F-BDB7-ECAC9C3D8E20}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2008-07-24 07:15:04.69     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Program F".: 100% complete  End Progress  Error: 2008-07-24 07:15:04.71     Code: 0xC002F210     Source: Back Up Database Task Execute SQL Task     Description: Executing the query "BACKUP DATABASE [Mstest ] TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak' WITH NOFORMAT, NOINIT,  NAME = N'MStest _backup_20080724071504', SKIP, REWIND, NOUNLOAD,  STATS = 10  " failed with the following error: "Cannot open backup device 'D...  The package execution fa...  The step failed.

 

Analysis
=======

When the “Create a sub-directory for each database” option is selected, the Maintenance task executes the xp_create_subdir procedure to create a directory with the Database Name. If you carefully look at the Detailed Error message above, you will see that there is a space character in the Database name.
“D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MStest \Mstest _backup_200807240715.bak”


If you try to run the Backup command extracted from the above error in the Query windows it gives a more informative error ,

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

 

Cause:
======
The Operating System does not allow you to create a folder with trailing spaces. When xp_create_subdir creates the folder with the space, the OS creates the folder but without the trailing space.

For example you can try this command EXECUTE master.dbo.xp_create_subdir N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \'

Now when the backup command fires it fails to read the directory, since there is no directory with a space created.


Resolution
=========
Follow the below method to remove the trailing space from the Database Name

  1. Right click and rename the database to any name. (This is because Management Studio will not allow you to remove the trailing space, since it thinks there is no change made to the name and that a database already exist with the same name
  2. Now rename the database to the original name without the space.
  3. Reconfigure your Maintenance Plan

 

Levi Justus
TL, Microsoft SQL Server

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • PingBack from http://wordnew.acne-reveiw.info/?p=13331

  • Had this exact problem and the solution worked great, thanks for sharing!

  • I had come across the same issue and the same solution worked. In my case i checked the logical name and it didnot have any trailing space. But the database name had a trailing space. Instead of giving the database a different name and renaming it again i just detached and attached the database.

  • Great job. Thanks for information... Space is a big trouble...

  • I'm having this problem on master, model, and msdb, so I know it's not the database name.  Renaming them isn't an option either.

  • the database which we are running are in production and we cant rename it also its 24*7 process. please suggest with alternate solution.

  • Eu tive um problema parecido " failed with the following error: "Cannot open backup device ', o nome do database  tinha um espaço em branco no final

  • Eu tive um problema parecido " failed with the following error: "Cannot open backup device ', o nome do database  tinha um espaço em branco no final

  • " failed with the following error: "Cannot open backup device '

    o nome do database estava com espaço em branco no final

Page 1 of 1 (9 items)