Can you backup SQL Server database to NAS drive?
I would like to set a backup device to a network attached storage (NAS) on my network. I can see the storage device I want on the network but when I make a backup device to that corresponding drive it only sees the local drives on the machine. Is this something that's just an impossibility or am I overlooking something?
NAS devices enable multiple network connected clients to store and share files with quick access - no wonder some users want to backup SQL Server database to NAS as a protection strategy. However, when you actually trying to do this in SSMS, you will find no network path in the destination list.
Does this mean you can only backup SQL Server to local drive and then copy the bak file to NAS drive? Not exactly. You can specify a network location as you like, no matter it’s a NAS drive, network share, or mapped network drive. Let’s see how it works.
How to make SQL Server backup to NAS successfully?
To backup SQL database to network location, first you need to enter the correct UNC path manually. It should contain the server name and folder name, like \\server\sharefolder or \\ip\sharefolder.
If you mapped a network folder as a drive and use its drive letter as the path, you will most likely receive Operating system error 3 (The system cannot find the path specified).
Let me give you an example here:
1. Right-click the database you want to back up and choose Tasks > Back Up.
2. Configure the backup settings. Remove the default destination so as to Add a new one.
Please do not click the … icon to select the destination. Instead, type the UNC path plus the backup file name in the popping out window. In my case, the backup destination is "\\nas\sharefolder\sqldb.bak”.
Alternatively, you could replace the nas name with the IP address, like “\\192.168.0.x\sharefolder\sqldb.bak”.
3. Click OK to run the backup. You can see it completed successfully.
Similarly, you can use T-SQL to perform the SQL backup to NAS. Click New Query > enter the following commands > then Execute it.
TO DISK = '\\nas\sharefolder\sqldb.bak'
*Replace the database name and NAS path with yours.
Related Tips:
✎ If you want to automatically execute SQL Server backup to NAS, you could combine this tip with Maintenance Plans, SQL Server Agent or batch file (please refer to auto backup SQL database). Just remember always use the UNC path of a network location.
✎ As for SQL Server backup to Synology NAS, you can also use Active Backup for Business following this tutorial.
✎ To fix Operating system error 5, please refer to SQL Server backup to network share access denied.
Easy alternative to backup SQL database to NAS or network share
In addition to above methods, you can also use powerful 3rd party software to backup SQL Server to NAS device. This will greatly simplify the operation of selecting databases, performing backups and automating tasks.
As an example, I will use AOMEI Centralized Backupper Database here. It’s a centralized backup software that can manage all Windows PCs or servers within LAN, and enables you to backup and restore databases of SQL Server 2005 - 2019.
Have a try on the 30-day free trial:
*Install Centralized Backupper Database on the central machine and the client packages on computers you want to manage. You can also install them on the same computer.
1. Gain controls over the client computers you want to backup.
Then navigate to Tasks > New Task > SQL Server Backup.
2. In Step 1, click + Add Computers to detect the controlled computers with SQL database, and choose one to backup.
3. Move to Step 2, click + Add to detect existing instances on the selected computer, and choose one or multiple databases to backup..
4. Click on Step 3, hit Add Storage to specify a NAS device or network share as destination. Once it’s added, you can directly use it next time.
5. If you want to auto backup SQL Server database, click Schedule to automate the task, and switch to Advanced tab to choose the backup mode (full/differential). Then you can click Start Backup to Create and execute tasks.
Conclusion
Having problem performing SQL Server backup to NAS? This is usually because you did not enter the UNC path correctly. To solve it, you can either try above tips, or use an easier alternative - AOMEI Centralized Backupper Database Edition.
It supports you to backup SQL Server database to NAS drive, and set up a schedule to automate it. What’s more, you can even backup SQL database and restore to another server within several simple steps.