Maintance plan -- full backup error

Question

Post reply

Maintance plan -- full backup error

sita.yeleswarapu

Mr or Mrs. 500

Points: 574
More actions
December 18, 2013 at 3:54 am

#302466

Hi,
We do run a maintenance plan full backup job for all db's in the server. The job is able to take full backup for all DB's except one DB. When I run the T-Script full backup or maintenance plan full backup job particularly only for this DB, it runs successful.
Please suggest me for an solution:
Executing the query "BACKUP DATABASE [xxxx ] TO DISK = N'xxxx" failed with the following error: "Cannot open backup device 'xxxx.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.
Error No: -1073548784

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sita.yeleswarapu Mr or Mrs. 500 Points: 574 More actions · Answer 1

I have tried deleting the folder and run agin.. but no luck.

It runs good through T-SQL script

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

Are the backups of the other databases written to the same folder?

Does the SQL Agent service account has the correct permissions to that folder?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Yeah, it sounds like the account that it's running under doesn't have access. Validate which account is running Agent. Make sure it's an account with access to your network and not local system accounts.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sita.yeleswarapu Mr or Mrs. 500 Points: 574 More actions · Answer 4

Hi HanShi,

Thank you for responding..

All the backups are taken to same folder . and also SQL Agent also has the required permission on it .

Thanks,

Sita

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

You have a very clear error:

The system cannot find the path specified

It has to be something related to this issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

Do you create your backups directly to disk, or are you writing to a backup-device? In this last mentioned situation the backup-device need to exist. You can query the existing ones by executing

select * from master.sys.backup_devices

You can add a new device by executing the "master..sp_addumpdevice" stored procedure.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

sita.yeleswarapu Mr or Mrs. 500 Points: 574 More actions · Answer 7

Yes , got it. There was a space at the end of the database name which was found and creating issue.

Thanks a lot

sita.yeleswarapu Mr or Mrs. 500 Points: 574 More actions · Answer 8

Any idea to remove/consider space while taking backup through maintenance plan?

HanShi SSC-Dedicated Points: 33507 More actions · Answer 9

Default setup of maintenance plan is through a GUI. This will always have the risk of wrong manual input.

If you want to control the variables you need to build the maintenance tasks with scripting.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

sita.yeleswarapu Mr or Mrs. 500 Points: 574 More actions · Answer 10

sita.yeleswarapu

Mr or Mrs. 500

Points: 574

December 25, 2013 at 2:48 am

#1675874

Yes I have done that.

sita.yeleswarapu Mr or Mrs. 500 Points: 574 More actions · Answer 11

Declare @path Varchar(200)

set @path='xxxx'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'

BACKUP DATABASE [xxxx]

TO DISK = @path

HanShi SSC-Dedicated Points: 33507 More actions · Answer 12

sita.yeleswarapu (12/25/2013)
Declare @path Varchar(200)
set @path='xxxx'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
BACKUP DATABASE [xxxx]
TO DISK = @path

So, what is your question or problem?

The code above does work well even with a space in the path. The only thing to keep in mind is to add a backslash at the end of the path. Because you entered a dummy value in your post I'm not sure you have done that.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **