Sql-server – Sql database dumps failing every night

databasedumpsql server

Hey guys, I have sql05 and my maintenance plan which backs up a database to an external storage SAN, has been failing every night.
Here is my error:

Executing the query "BACKUP DATABASE [master] TO  DISK = N'\\\\192.168.x.x\\vmbackup\\server\\dbbackup\\master_backup_201004222300.bak' WITH NOFORMAT, NOINIT,  NAME = N'master_backup_20100422230002', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Cannot open backup device '\\\\192.168.x.x\\vmbackup\\server\\dbbackup\\master_backup_201004222300.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.

I googled this error and tried adding permissions to the backup device for network service as recommended at experts exchange, no dice.

Does anyone have any ideas?

Best Answer

What service account is SQL Server running under (you can check this in SQL Server Configuration Management or in Services.MSC)? That account will need permissions on the share and the folder where the file is being written to. A couple of provisos:

  • If the SQL Server is running under Network Service, this will map to Domain\Computer$ where Domain is the domain the SQL Server is in and Computer is the name of the server SQL Server is installed upon.
  • If the SQL Server is running under System (Local System), it can't do this because the System account does not map outside the computer as of Windows Server 2003.
  • If the SQL Server is running under Computer\User (not a domain account, but an account local to the server), then it faces the same problem as running under System.
Related Topic