Backup failed with the following error: "xp_create_subdir() returned error 161

Users might receive below error when executing backup.

Executed as user: NT Service\SQLAgent$SQLTEST. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:50:45 PM Progress: 2015-05-07 13:50:46.22 Source: {2FEF4D33-99C5-4CC6-9ECC-85EDDACC8E9E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2015-05-07 13:50:46.41 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\SQLbackup\db..." failed with the following error: "xp_create_subdir() returned error 161, 'The specified path is invalid.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error

Error: 2015-05-07 13:50:46.60 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [master] TO DISK = N'\\ SQLbackup \db..." failed with the following error: "Cannot open backup device '\\SQLbackup\SQLTEST\\Full_Backup\master\master_backup_2015_05_07_135046_3323057.bak'. Operating system error 5(Access is denied.). 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.

If you see highlighted messages there are two reasons error

1. failed with the following error: "xp_create_subdir() returned error 161

First check SQL server or SQL Server Agent service running using the local or service account and the account should have enough privileges on the backup folder

2. Cannot open backup device. Operating system error 5(Access is denied.)

Check whether the account have read/write privileges on the backup folder.

In my case I see that SQL Server Agent running using a local account and that account don’t have permission on the backup folder. I have changed the SQL Server Agent “Log on As” account to service account which has permission and restarted the service.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com