May 17, 2013 at 10:47 am
I have a maintenance plan which is set to backup all databases on the server to a network path D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups. In this path, it creates a folder by the name of the database it is backing up, and then puts the .bak file in there. So for e.g. I have:
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db1\DB1.bak,
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db2\db2.bak
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db3\db3.bak etc.
The maintenance plan has been running successfully with no issues for a few months.
Recently, I created a new database "db10". The maintenance plan is unable to backup the database. It is able to create the folder with the database name, so I do see
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db10 but there is no .bak file in there. The error is cut & pasted exactly as below :
Executing the query "BACKUP DATABASE [db10 ] TO DISK = N'D:\\Prog..." failed with the following error: "Cannot open backup device 'D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\SQLBackups\\db10 \\db10.bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It seems to be a permissions/path issue, but I need some pointers on how to proceed and where to look. thanks!
May 17, 2013 at 12:38 pm
Is the path and file name too long for whatever data type Maintenance Plans is using?
Try backing up to d:sql_backup\dbname\
May 17, 2013 at 1:32 pm
Like2SQL (5/17/2013)
I have a maintenance plan which is set to backup all databases on the server to a network path D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups. In this path, it creates a folder by the name of the database it is backing up, and then puts the .bak file in there. So for e.g. I have:D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db1\DB1.bak,
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db2\db2.bak
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db3\db3.bak etc.
The maintenance plan has been running successfully with no issues for a few months.
Recently, I created a new database "db10". The maintenance plan is unable to backup the database. It is able to create the folder with the database name, so I do see
D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\SQLBackups\db10 but there is no .bak file in there. The error is cut & pasted exactly as below :
Executing the query "BACKUP DATABASE [db10 ] TO DISK = N'D:\\Prog..." failed with the following error: "Cannot open backup device 'D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\SQLBackups\\db10 \\db10.bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It seems to be a permissions/path issue, but I need some pointers on how to proceed and where to look. thanks!
I may be wrong but is there a trailing space in the name db10? Above in brackets it looks like this: [db10 ] indicating a space (or some other non-printing character) in the name resulting in the path for the backup also having a space which is only valid if the entire path and filename is enclosed in double quotes (").
May 17, 2013 at 2:16 pm
May 17, 2013 at 2:20 pm
Lynn Pettis (5/17/2013)
Like2SQL (5/17/2013)
I may be wrong but is there a trailing space in the name db10? Above in brackets it looks like this: [db10 ] indicating a space (or some other non-printing character) in the name resulting in the path for the backup also having a space which is only valid if the entire path and filename is enclosed in double quotes (").
Lynn you are correct. I copied this off the T-SQL in the maintenance plan job status. But do note that there is a trailing space in the T-SQL's for the other db's that were successfully backed up also. This is why I didn't think it would be an issue related to the trailing space.
May 17, 2013 at 2:34 pm
Like2SQL (5/17/2013)
Lynn Pettis (5/17/2013)
Like2SQL (5/17/2013)
I may be wrong but is there a trailing space in the name db10? Above in brackets it looks like this: [db10 ] indicating a space (or some other non-printing character) in the name resulting in the path for the backup also having a space which is only valid if the entire path and filename is enclosed in double quotes (").
Lynn you are correct. I copied this off the T-SQL in the maintenance plan job status. But do note that there is a trailing space in the T-SQL's for the other db's that were successfully backed up also. This is why I didn't think it would be an issue related to the trailing space.
But if you look at the path from your error message:
'D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\SQLBackups\\db10 \\db10.bak'.
there is also a space after the database name. Since the entire path and filename is not enclosed in double quotes, this won't work.
May 17, 2013 at 3:09 pm
You know what - you are right. Actually the maintenance plan is setup to create a sub-directory using EXECUTE master.dbo.xp_create_subdir and it creates it using a trailing space. Since this db was created using SSMS I am assuming a trailing space entered after the name? Why else would EXECUTE master.dbo.xp_create_subdir be introducing a trailing space after the db name?
On the flip side, there is a front-end application using the database and it seems to have no issues connecting to the database.
May 17, 2013 at 3:23 pm
Funny. I didn't think db1, db2, db10 were the real database names.
May 17, 2013 at 3:46 pm
So I would have one last question for the team here. My maintenance plan was set to backup all databases. However it was unable to backup the one database db10, and in "view history" it appears that the whole job has failed. However, all the other databases seemed to have been backed up. Just wanted to confirm that my understanding is correct - that just because one db was unable to backup doesn't mean the others were not.
And yes db1, db2 are not the real db names of course 🙂
May 17, 2013 at 4:36 pm
That's possible that DBS 1,2,3... run successfully and the failure on db10 failing fails the job. A job scheduled from a maintenance plan only has one job step. But I would verify the .bak file's dates just to be sure. There's a bunch of ways to automate a backup plan. Maintenance Plans might be the least flexible.
May 17, 2013 at 11:07 pm
I had this Problem some time ago when I created a maintenance plan for Backup All Databases under SQL Server 2005. Once I had created the Job and let it run, the creation of a new database wasn't picked up in the Job resulting in an error.
My solution was simply to drop and recreate the Job. Afterwards there were no Problems....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply