In our DB migration project we need to migrate data from a SQL Server DB which are used by Desktop application installed in our clients location. For that we have implemented a desktop tool which takes backup and uploads it to Blob storage which is attached as local drive (mounting S: using net use command) in the Azure windows and later at the server end we need to attach this backup file to sql server (and rest of the ETL process goes).
We have created SSIS package which does this DB attachment using running ATTACH DB command in master DB. When we manually run the SSIS PACKAGE, there is no issue. But the same thing is failing when this package is invoked from SQL AGENT JOB. Following is the command I am running from SSMS
RESTORE DATABASE MyDB FROM DISK='S:\backup.BAK'
Error:
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'S:\backup.BAK'. Operating system error 3(The system cannot find the path specified.). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Don't know what issue this is. Can some one help me understand why I am unable to access Azure storage drive from SSMS?