Cannot open backup device. Operating system error 5(Access is denied.) while backing up\restoring SQL server Databases.

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

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

Infrastructure: SQL server 2000 and onward

Scenario: This is very famous  error which has been seen no of times in DBA life.

If you go on the error, it says there is some problems with backup device or permission issues and so on. You will get so many blogs and will find proper resolutions referring those blogs, so I am not going to write about that here.

I will simply talk about the scenario I had on my client environment.

Everything was correct  like  Backup command was right,backup media was rightly placed and had full permission and it was verified multiple times.

No problem with media was captured. all were seem to be good but though I was getting Cannot open backup device. Operating system error 5(Access is denied.)

And one thing was quite strange backup command was running fine in new query window in SSMS but it was throwing Cannot open backup device error while automating it through Maintenance Plan. No problem was detected in Maintenance Plan in-fact rest DBs backup were occurring fine.

Resolution:

Since there were no fault in Media, No permission issues, No syntax problems were found.

I run the backup script (GUI then script to new Window) for that particular database through SSMS and suddenly noticed an [EXTRA SPACE] in the end of database name in backup query.

That one extra space was the only culprit and the I checked the database name. I highlighted the DB name in Object explorer and found that DB name also had same extra space that the reason backup command also had same space.

I removed that extra space for that particular database and finally succeeded to take backup automatically.

Hope this helps!

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts