SQL Server database needs protection so you need to have a backup stored on the network share to enhance data security. See how to backup SQL Server database to a network share in 3 ways.
Updated by Nick Zhao on 2023/07/04
Database helps companies store a massive amount of data and increase data inquiry speed to improve data management. That’s why every company should build its database during transitioning from traditional data management to digital data management.
Since database stores important business data, companies should know the way to protect data to guarantee business continuity. You might have known the way to create a local backup but to enhance data security, you can save the data backup to another location like a network share. Therefore, in case any issues arise in the local environment, you can still recover data by retrieving data backup from the network share.
SQL Server is one of the most popular databases in the world. In this post, it will be mainly introduced to save the data from SQL Server database to a network share.
It is easy to create a local backup of SQL Server database with SSMS or command. The simplest way to backup SQL Server database to the network share is manually copying the bak file to a network share.
It is very long and tedious to repeat the procedures every day but you can make the whole process more intelligent by running a batch script.
SET LocalFolder=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup
SqlCmd -E -Q "Backup Database MyDB To Disk='%LocalFolder%MyDB.bak'"
XCopy "%LocalFolder%MyDB.bak" "192.168.16.55BackupDatabases" /Z /V
DEL "%LocalFolder%MyDB.bak"
This batch script will help you set up a variable to define the local backup directory, create a database backup and store it in the local directory, then copy it to network share, and at last, delete the local backup.
Why not directly backup the database to the network share? The reason is that making local backup is highly feasible. If you directly put a location of a network share here, you might get the error messages like access denied so you should do some configuration before that.
To backup SQL Server database to network share directly, you need to modify the properties of SQL Server service or map the network share on the server.
If you get error messages after running a script containing the directory of the network share, it may be because that SQL command can only be executed within the scope of the account running the SQL Server service so you should make sure the account has the permission to access and write the network share.
To change the account, you need to open Service on the server > locate SQL Server (MSSQLSERVER) > right-click it and select Properties > click Log On tab > enter the credentials of the right user > click OK > restart SQL Server service in order for the changes to take effect
Then you can write a script to directly backup SQL Server database to a network share, like:
SqlCmd -E -Q "Backup Database MyDB To Disk='192.168.16.55BackupDatabasesMyDB.bak'"
It is easy to backup SQL Server to network share with scripts but it is not the best way to protect business data because there is a likelihood that the task might fail and it not wise to manage substantial data with only commands. Companies can have a better solution to protect business data and business continuity.
Vinchin Backup & Recovery is a professional backup solution supporting various databases including SQL Server database and also for Oracle, MySQL, Postgres SQL, MariaDB, etc. It will help companies backup and restore data with better granularity.
To help you save backup data to more destinations, it supports local partition or disk, logical volume, local directory, Fiber Channel Storage Area Network (FC SAN) storage, iSCSI storage, NFS storage, CIFS storage, etc.
If you would like to backup SQL Server to a network share, you just need to add the directory to backup system in advance and then you can select it as backup destination later.
1. Select the database you would like to backup > click Next
2. Select the network share as the backup destination > click Next
3. Select backup strategies like Schedule, Log Backup, Data Compression, etc. > click Next
4. Submit the job
Vinchin Backup & Recovery has been selected by thousands of companies and you can also start a 60-day full-featured trial here. Also, contact us directly and then you will receive the tailored solution for your companies. We have established partnerships with companies all over the world so if you wish to start a local business, just find a local partner here.
SQL Server helps companies build highly efficient databases to better store and retrieve data but companies should know the way to backup database so as to protect business data and business continuity.
Saving SQL Server database to a network share can enhance data security. You can use the script to create a local backup and then copy it to a network share or directly backup it to a remote location.
Vinchin Backup & Recovery is a professional database backup solution that will help better manage and backup various databases in companies. Don’t miss the free trial.
Share on:
