If you have limited access to SQL Server’s file system, getting the .bak file back may be an issue. In this case, you can send database backup to some network folder if both you and the SQL Server have access to it.
We will show how to do it with SQLBackupAndFTP.
To automate SQL Server backups to UNC paths you need to install SQLBackupAndFTP, connect it to your SQL server, and select databases you want to backup. Then press the button next to “Store backups in selected destinations” add a destination where to store the backups:
In order to backup to UNC Paths, you would have to select the first option, “Local/Network Folder/NAS”.
Go to advanced settings and enter the credentials that would allow accessing the selected network share:
Note that the following requirements must be met
- The SQL Server service must be running under a domain-level account (to be able to access the network)
- The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share.
- If your SQL Service runs in LocalSystem account you can’t use this method – you wouldn’t have access to the network
- The remote share should only be accessed via UNC name. Mapped drives may not be consistently visible to the SQL Service.
After adding the backup destination folder, all you have to do in order to finish the setup for your backup schedule is to set the actual details of the schedule: the time you want it to run, the frequency (hourly, daily, weekly etc.) as well as other settings to add another degree of security that your backups are created correctly and that your data is safe.
More details on how to set up a schedule you can find in another article, here.