sql-server-2008backupaccess-deniedpermission-denied

Cannot open backup device. Operating System error 5

Below is the query that I am using to backup (create a .bak) my database.

However, whenever I run it, I always get this error message:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Users\Me\Desktop\Backup\MyDB.Bak'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This is my query:

BACKUP DATABASE AcinsoftDB
TO DISK = 'C:\Users\Me\Desktop\Backup\MyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';
Solution

  • Yeah I just scored this one.

    Look in Windows Services. Start > Administration > Services

    Find the Service in the list called: SQL Server (MSSQLSERVER) look for the "Log On As" column (need to add it if it doesn't exist in the list).

    This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)

    NOTE: Remember to give permissions to the actual directory AND to the share if you are going across the network.

    Apply and wait for the permissions to propogate, try the backup again.

    NOTE 2: if you are backing up across the network and your SQL is running as "Local Service" then you are in trouble ... you can try assigning permissions or it may be easier to backup locally and xcopy across outside of SQL Server (an hour later).

    NOTE 3: If you're running as network service then SOMETIMES the remote machine will not recognize the network serivce on your SQL Server. If this is the case you need to add permissions for the actual computer itself eg. MyServer$.