Sql-server – Cannot open backup device Error

backupSecuritysql server

I have a SQL Server Agent job that backs up a database on a remote server to a network drive. Linked Server, I believe is set up correctly but I get the following error:

Msg 3013, Sev 16, State 1, Line 35
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

Msg 3201, Sev 16, State 1, Line 35
Cannot open backup device. Operating system error 1326
(failed to retrieve text for this error. Reason: 1815). [SQLSTATE 42000]

I have used SQL Server Configuration Manager to change the Log On to "This Account", selected the domain account and supplied the right password.

Then, on the Linked Server Properties, I mapped the Local Login to the domain account and ticked "Impersonate" The security on the network drive folder is set to "everyone" and full permission is given. And yet still getting this error and cannot backup the database as a result.

What can I do?

Best Answer

So in Configuration Manager, you configured the SQL Agent service account, correct? That is the account that will execute SQL Agent jobs, by default.

This query will verify your settings:

SELECT servicename, service_account
FROM   sys.dm_server_services
go

Do you get the error when executing the job via a schedule, when executing manually, or both?

If you are using T-SQL commands to perform the backup, what happens when you paste the commands into a query window and manually execute? Can you reproduce the error there as well?

Related Solutions

Sql-server – Backup SQL Server To NAS

Your syntax is wrong for adding a backup device.

When you create a backup device in SQL Server you are actually creating the "bak" file as a media set.

Along with the suggestion from Bob that you should be using the full UNC path, your syntax should be changed to something like this:

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'NAS_YEDEK_DB')
EXEC master.dbo.sp_dropdevice @logicalname = N'NAS_YEDEK_DB'
GO

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NAS_YEDEK_DB', @physicalname = N'\\10.1.1.x\yedek_db\YEDEK_DB_backup.bak'
GO

Sql-server – SQL Server 2008 Database Restore Error – Cannot open backup device 15105

OS Error code 3 is ERROR_PATH_NOT_FOUND: The system cannot find the path specified. With a path starting with Z: my pshyhic powers tell me it is a mapped drive. Drive mapping is session scoped and I'm convinced your service session does not see Z:. Mapping a drive to be seen by a service is, for all practical purposes, impossible.

Use a valid path. Use UNC names like \\server\share. Consider the security implication of impersonation and delegation when accessing remote shares.

Best Answer

Related Solutions

Sql-server – Backup SQL Server To NAS

Sql-server – SQL Server 2008 Database Restore Error – Cannot open backup device 15105

Related Question