There is no need to map a drive in order to put a backup on remote storage. The BACKUP DATABASE command will directly accept a UNC path

BACKUP DATABASE DB1 TO DISK='\\192.168.1.93\share\db1.bak'

Here are a few benefits of directly using a UNC path: NOT a good idea to execute commands through xp_cmdshell that can trigger an error. For example, let's say that the Z: drive has already been mapped or that \192.168.1.93\share is unavailable. How do you handle those situations? Directly using a UNC path: if the path is unavailable, the BACKUP DATABASE command will throw a trappable error.

Security: If you grant the SQL Server Service Account the ability to see the share and read/write to the directory, that's all you need. With an xp_cmdshell implementation, you are forced to hard-code a user account and password. Not only is this NOT secure, it's a maintenance problem waiting to happen. What do you do when the password has to be changed?

Hi Marc,

Thanks for your nice comments,

Can we run the Bulk insert using UNC path or not ? or previous version also support the same or not ?

In your question "what do you do when the password has to be changed?" - Each time that script is using the latest password so password is not issue i think .

Yes, BULK INSERT can use a UNC path:

From SQL Server Books Online:

datafile must specify a valid path from the server on which SQL Server is running. If datafile is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \Systemname\ShareName\Path\FileName. For example, \SystemX\DiskZ\Sales\update.txt.

In regards to saving the password... you manually type in the commands to take a backup each time? Backups should be scripted and scheduled. The frequency is usually determined by the businesses needs (and tolerance for data loss), but at a minimum I'd suggest a full backup daily with either differential or transaction log backups hourly.

Thanks for sharing your views !