backup SQL Server database to a network mapped drive
We have created a maintenance plan in Microsoft SQL Server 2008 for backing up all database to a network mapped drive and give full access to Database Administrator domain user on shared folder but it SQL Server 2008 database backup maintenance plan was failing. We have tested that he was able to access the network mapped location and also was able to create folder in network mapped location but SQL Server backup was failing. Well, we were getting access denied in backup job logs and we know it was working on local shared folder drive and there was no issue in this job.
There are multiple solutions that we get from the internet but we have solved this in quite easy way, you might can go for the solution that suites to your nature.
- When we need to use a share or mapped network location using a UNC path, like \\servername\shared-folder\backup-job.bak, for a backup and in any way its saying access denied or the file location cannot be resolved, you just need to make it sure that you are using the right permissions on your shared folder on mapped network drive or your NFS.
- Please Click on Start > Run > Type services.msc and Press Enter or
- Go to Control Panel > Administrative Tools > Services on the server where your SQL Server is installed and SQL Database/s resides and required to be backup on network shared folder or mapped drive.
- Locate SQL Server service and Right Click on “SQL Server (MSSQLSERVER)” Service and click Properties.
- Move to “Log On” Tab and change NT Service\MSSQLSERVER to <ipaddresshost\rizwan.ahmad> or the username who has permissions on this server and on shared folder as well. < ipaddresshost\rizwan.ahmad> = your username
- Click on Apply and then OK. It will prompt you to restart service to effect service with new account. Press OK.
- Restart the Service and execute your SQL backup or maintenance plan to backup on network shared folder or mapped drive. it must goes to successful
- I assume that you are already login into your Microsoft SQL Server with SQL Server Management Studio, (See how to open MS SQL Management Studio shortcut)
- How to open Run the following command from a query window
EXEC xp_cmdshell 'net use '
where <drive name>: Letter used to map the drive
and hare name>: UNC path to the share
- After executing above example command, you will be able to backup on your mapped network drive.
- You also need to create an Automatic Execution of Stored Procedures to persevere mapped drive for mentioned command. It’s because once your SQL Server or your SQL Server Services is restarted, your mapped network drive will not be visible because it will be unmapped.
- Second easiest way is to create a backup device using the UNC path of remote shared location. One thing you need to keep in mind that the SQL Server startup account needs to have full permissions on the remote share.
- In you SQL Management Studio, Open a New Query and run this command
BACKUP DATABASE TO DISK = '' WITH INIT
You must make sure same as solution 1 that SQL Service Account have enough rights on network shared. Your backups will only fails if there is contradiction between the user on SQL Service Account and Backup Job executor.
if you think, you can make it more easy, please comes up with your idea, I will update it.