Backup SQL Server Database to NAS (Network Attached Storage)
How to backup SQL Server database to NAS drive directly? Can you do this with SQL Server Management Studio? Learn detailed steps in this article.
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.
*Replace the database name and NAS path with yours.
✎ 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 Cyber Backup here. It’s a centralized backup software that can manage all SQL databases within LAN (as long as it's installed on Windows 7 and Windows 2008 R2 onwards), and enables you to backup and restore databases of SQL Server 2005 - 2022.
Have a try on the 30-day free trial:
▶To set up the SQL backup software: run the installed program and navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.
▶Choose Download proxy program or Copy link to download and install it on to the device with SQL Server installed. Then, go to Already installed proxy tab to select the device and Confirm it.
▶After that, click -> Authentication to validate the database instance. And then you can follow the steps below to backup SQL database to NAS.
How to take SQL Server backup to NAS:
1. Navigate to Backup Task -> Create New Task, and select the Backup Type as Microsoft SQL Backup.
2. Click on Device Name to select the SQL instance and databases you want to backup. You can select one or multiple databases flexibly.
3. Specify a local path or network path as backup Target. To back up SQL database to NAS, you could click Add a new network storage to add it. The saved path will be listed in Add storage.
4. Set up a Schedule (optional) to run the SQL database backup daily/weekly/monthly, and set the backup method as Full/Incremental/Differential.
✍More useful features:
- Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
- Email Notification enables you to receive email notifications when the task is abnormal or successful.
5. Now you can click Start Backup to create and execute the SQL database backup.
Once the task is created, it will be listed in Backup Task tab. You can edit or restore from it anytime you need. With the centralized backup and restore feature, you can also backup SQL database to another server easily.
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 Cyber Backup.
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.