You have defined a share on a remote server, and you cannot authorize the SQL Server service account on that share. You just have a user information to connect to share.
To get backup in this way, you need to map that share to the server where sql server is located. In the article “How To Define a Share and Map This Share To Windows” you can find out how this is done.
After completing the map process, we get an error when we want to get backup to this share as follows.
In order to avoid this error, we can introduce the user information defined in remote share to the sql server with the help of the following script.
|
1 |
EXEC XP_CMDSHELL 'NET USE myMappedDriveLetter: "myShare" /user:myUser myUsersPassword' |
We are writing “Z” instead of myMappedDriveLetter. Because in our example map drive name is “Z”
You need to write your share path instead of myShare.
You need to write the authorized user name on the share instead of myUser.
You need to write the password of the authorized user on the share instead of myUserPassword.
After running this script with the parameters I specified, you can now backup.
If you want to remove this mapped drive later, you can remove it as follows. The following script removes all mapped drives. If we write “Z” instead of “*” we can remover Z drive only.
|
1 |
EXEC XP_CMDSHELL 'net use * /delete /y' |
