Create and Restore a SQL Server Database Backup on a
Network File Share
Provided by: FMS Development Team
This tip helps SQL Server administrators and developers understand how to
configure backups that are more useful and secure. This strategy
promotes easier recovery of a failed system by backing up and restoring a
database from a network file share.
Do Not Create Backup Files on the SQL Server Machine!
Backups are part of your disaster recovery and business continuity plan. One of the failures to protect against is a computer and hard disk failure.
NEVER store your backups on the same machine/hard disk as your SQL Server!
If the physical device
containing both the database and its backup fails, you face limited
recovery options and will likely need to rely on other older backup
media, if they exist.
Ideally, you should create all of your database backups on a separate
device and/or separate machine, and in a central location. While some
of you are saying "That seems pretty obvious!" to this information, you'd be
surprised how many SQL Server users and database administrators are
creating a database backup locally on the server without duplicating it elsewhere.
How to Store SQL Server Backups on a Network Drive
While only local devices are shown in Enterprise Managerís Backup/Restore
dialogs, there is a way to create or restore a SQL Server database backup on
a network file share. Creating or restoring a database backup on a network
file share requires the following prerequisites:
- The SQL Server services, on the Server containing the instance of SQL
Server, must be running under a domain-level account (e.g. A Domain
Administrator account). This is accomplished by changing the "Log On"
properties for the services named "MSSQLSERVER" and "SQLSERVERAGENT" on the
server running SQL Server (not your local instance). When you have completed
changing the log on information for these 2 services, you will need to
restart the "MSSQLSERVER" service on that server. Note that this will ask if
you want to restart the "SQLSERVERAGENT" as well - Answer: Yes.
- The SQL Server service account must have FULL CONTROL rights to the
file system folder and to the share. That means you need to have a shared
location in which the logon account you specified in 1 (above) has full
- The file share should only be accessed via UNC name. Mapped drives may
not be consistently visible to the SQL Service.
- You cannot specify the path by using the browse ellipses (...). You
must type the fully qualified path into the text box as:
When creating or restoring backups on a network share, a significant
increase in network traffic may result. You should ensure that your network
can handle this without causing poor performance for other systems or end
users. It may also benefit you to establish a dedicated physical network
connection between your database server and your backup file host. Once the
SQL Server instance is configured, you can use your local Enterprise Manager
to create or restore a database backup that resides on a network device
other than the device your instance of SQL Server is running on.