SQL Server 2005/2008 And Osql Command Line Error: Cannot open backup device ‘%DBNAME%’. Operating system error 5(Access is denied.)

While trying to install a new version of my Antivirus management software I was making a backup of the databse with the command line:

>osql -E -S "SERVER\DBINSTANCE" -n -b -Q "BACKUP DATABASE DBNAME TO DISK = 'D:\BACKUPNAME.BAK'"

It threw the error:

>Cannot open backup device 'D:\BACKUPNAME.BAK'. Operating system error 5(Access
is denied.).

followed by:

>BACKUP DATABASE is terminating abnormally.

Failed

I tried the Microsoft SQL Server Management Studio console, but received the same error:

After some googling I stumbled upon this page where a fellow-countryman  took the trouble to post his solution some 3.5 years ago.

After checking, it seems the SQL Server service runs under the NETWORK SERVICE account.

This account, however, had no file system rights on the partition I was trying to write the backup file to.

After I granted modify permissions to the NETWORK SERVICE account, my issue was solved.

The tricky thing about this exercise, is the fact that I thought I had enough user rights to write the file because I opened the command prompt and Microsoft SQL Server Management Studio console with administrative rights. Obviously this was not the case because the actual process runs in the context of the SQL Server service.

Advertisements

About Yuri de Jager
Technology Addict

One Response to SQL Server 2005/2008 And Osql Command Line Error: Cannot open backup device ‘%DBNAME%’. Operating system error 5(Access is denied.)

  1. Jeremy says:

    You are awesome! Adding the NETWORK SERVICE NTFS permissions to the folder solved it. Thank you, thank you!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: