Backups working through GUI but not through T-SQL, SQL Agent or SSIS?

Question

Post reply

Backups working through GUI but not through T-SQL, SQL Agent or SSIS?

Shark Energy

SSCrazy Eights

Points: 8389
More actions
May 1, 2011 at 6:27 am

#389413

Getting the "Cannot open backup device\Operating system error 3(error not found)." error on a monthend job that has worked ever since it existed.
It runs to a custom directory rather than the default.
I have had to run the backups through the GUI and it works. But to the same directory through the SSIS job manually, or via a SQL Job, or in a query window - the error above!
My user account that I'm logged in as has Admin rights. The SQL Agent and SQL service account should have admin rights (getting support team to check). But it fails when I run it in a query window as my user name anyway!
Very odd.
Any ideas?

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Can you post the full error message ? Check the SQL syntax that you are using to run the Backup.

Thank You,

Best Regards,

SQLBuddy

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Sounds like a permissions issue but if you provide the information requested, you should be able to get a solution to your problem?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 3

This is the error from running a copy of the t-sql from SSIS into a query window...

Msg 3201, Level 16, State 1, Line 2

Cannot open backup device 'D:\Backups\New\Data_backup_201105011315.bak'. Operating system error 3(error not found).

Msg 3013, Level 16, State 1, Line 2

BACKUP DATABASE is terminating abnormally.

Msg 3201, Level 16, State 2, Line 4

Cannot open backup device 'D:\Backups\New\Data_backup_201105011315.bak'. Operating system error 3(error not found).

Msg 3013, Level 16, State 1, Line 4

VERIFY DATABASE is terminating abnormally.

As I say, never been an issue until this month!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Something changed, my guess if that is permissions

Please check the permissions for the service account needs to have Write Permissions on the folder that you are trying to backup to.

Can you grant permissions? Are you a Domain Admin or Local Admin to the Server in which SQL Server is installed on?

If you do not have permissions to the folder you can test backing up to another folder that you have full permissions to?

Try running the backup procedure interactively, i.e do not use a job and do not run it as a service account.

You can always log on using the service account credentials but if you run the backup as a job it will be harder to diagnose.

Regards,

Welsh

http://support.microsoft.com/kb/207187

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 5

Had an inkling it was permissions and thats what I'm waiting for from our support guys (confirmation that they have changed something!)

Thanks for your help 😎

I'll come back with the final solution to confirm it

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

Check the path and permission on the service account which one is using for SQL Server

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

You should be able to navigate to the share on the server and check the permissions.

You may not have the permissions to grant access to the service account but you might want to check it out and let the support team know what you need.

Regards,

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 8

Yeah I need to get them to check the domain users and see that this service account I use is still in the right group as I could see the group was there, but no clue on the user.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

If you navigate to the path of the folder that the backup folder is located you can determine who has what permissions to the folder.

Regards,

Welsh

MCDBA, MCSE, MCITP, MCSD

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 10

Thats the thing - this user does still seem to be in the admin group - and the admin group as mentioned IS still in there. So i'm waiting on the support guys (or something stranger is at work!)

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 11

Permissions were fine. Can't believe I didn't spot it but they had restructured the directory and dropped the sub folder the job referenced....