How do I restore a SQL Server backup from a network share?

When using SQL Server’s Enterprise Manager, only the local hard drives on the server are exposed. Mapped drives do not appear, nor do drives on other servers that may exist in the network.

To write your backup to another server you need to use the UNC path instead of the hard drive letter. For example, instead of typing “G:Northwind.BAK” you would specify the server name and path like so: \TestServerG$Northwind.BAK”. This can be done both with Enterprise Manager and in your backup command as follows:

BACKUP DATABASE Northwind
TO DISK ‘\TestServerG$Northwind.BAK/’

To restore a backup, you would do the same thing. Just reference the file using the UNC path instead of the drive letter.

One of the biggest obstacles you’ll encounter when creating a backup file is not having the proper access rights to the file system. When you log in to Enterprise Manager or Query Analyzer and execute the backup command, it uses the credentials of the service accounts, not the rights that you have as a user. Even if you have the necessary rights to add or delete files, it does not mean the service account does.

When running a command without sufficient rights you will see this error: Cannot open backup device ‘\TestServerG$Test.bak’. Device error or device off-line. See the SQL Server error log for more details.

To check the rights, you may use XP_CMDSHELL and run a directory command to determine whether you can see the directory contents or get access denied. xp_cmdshell ‘dir \TestServerG$’<

If the service account does not have access you will get an “Access is denied” message — otherwise you will see the directory contents.

Make sure your service accounts have the appropriate access levels or change to a share where the account does have the necessary rights to create new files.