Answered by:

How to backup a database to a network drive

Question

It is OK to backup a database to the local drive (C:) on SQL Server 2005. But when I tried to backup a database to a mapped network drive (N:) on the server, it failed. Here is the message:System.Data.SqlClient.SqlError: Cannot open backup device 'N:\db.bak'. Operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

The network drive N: is accessible through Windows Explorer. Currently, we are backing up our production databases to a central place (SAN) on SQL Server 2000.

How to backup a database to a network drive on SQL Server 2005?Thanks,Tony

Answers

If you are using a unc, eg \\server\share\mydb.bak, for a backup or restore and it is complaining that the file location can not be resolved or that the login has failed, there is an easy solution. Make sure the file is visible from the machine with sql server. If it is but you still get the error, it can be only one thing - the login that the SQL Server service is running under does not have access to the network location. In my case, I went to Control Panel -> Administrative Tools -> Services (on the server). I found the SQL Server service and checked it's properties and discovered that it was using the local administrator account (.\Administrator) which obviously doesn't have access to the rest of the domain. I changed this to a domain user, restarted the service, and voila, unc paths work fine now.

Just found out that we can backup a database to a network address, like "\\network_path\db.bak". It worked. But it did not work for a mapped drive (N:). For the restore to another test server, there is no GUI through Management Studio. Everything has to be done by SQL scripts. With Management Studio, we could not view the contents of any backup file from a remote network address. In a query window, we can run "RESTORE FILELISTONLY FROM DISK = N'\\network_path\db.bak'" to view its contents. Then run "RESTORE DATABASE ..." to restore it to a test database.

All replies

Hi Tony I have faced this problem before and I read an article about it that states that you cannot do so cos the network drive is based on the logged user which cannot be seen by the SQL server service . N.B this has been for SQL Server 2000.

Thanks, Eisa. Then we definitely will be in trouble when we use SQL 2005 in the future. Right now, one of our production databases is 260 GB in size. With SQL 2005, we have to create a local drive to back it up. It may take a few hours. Then we have to copy the backup file to our central backup location (SAN) manually. It takes a few hours. Next, if we want to restore it to a test database server, we have to copy the backup file to the local drive of that test server since SQL 2005 could not read a backup file from a mapped network drive either. From the backup to the restore on another test database server, it may take 15 - 24 hours for one copy. In addition to the backup/copy/restore time, we also have to allocate more disk space for each extra copy. Production server needs 260 GB (production database) + 260 GB (Backup). The central backup location needs 260 GB. Test server needs 260 GB (backup file) + 260 GB (test database). The total is 1300 GB. Ouch! We may have to keep using SQL Server 2000 since SQL Server 2000 has no problem on any mapped network drives.

Just found out that we can backup a database to a network address, like "\\network_path\db.bak". It worked. But it did not work for a mapped drive (N:). For the restore to another test server, there is no GUI through Management Studio. Everything has to be done by SQL scripts. With Management Studio, we could not view the contents of any backup file from a remote network address. In a query window, we can run "RESTORE FILELISTONLY FROM DISK = N'\\network_path\db.bak'" to view its contents. Then run "RESTORE DATABASE ..." to restore it to a test database.

I am using Visual Studio 2005 with vb. Just try to build a small database on the network drive using the bindingsource and tableadapter (BindingSource.EndEdit(), and TableAdapter.Update). It can't read my database. I go ahead to network explorer -> modify connection, and in the database file name, type in "\\network_path\database.mdf

However, I got an error message of the following:"The file\\network_path\database.mdf is on a network path that is not supported for database files.And attempt to attach an auto-named database for file \\network_path\database.mdf failed. A database with the same name exists. or specified file cannot be opened, or it is located on UNC share."

So, how can I change my code so that I can access (read/write) on a network drive database? Thanks

If you are using a unc, eg \\server\share\mydb.bak, for a backup or restore and it is complaining that the file location can not be resolved or that the login has failed, there is an easy solution. Make sure the file is visible from the machine with sql server. If it is but you still get the error, it can be only one thing - the login that the SQL Server service is running under does not have access to the network location. In my case, I went to Control Panel -> Administrative Tools -> Services (on the server). I found the SQL Server service and checked it's properties and discovered that it was using the local administrator account (.\Administrator) which obviously doesn't have access to the rest of the domain. I changed this to a domain user, restarted the service, and voila, unc paths work fine now.

If you are using a unc, eg \\server\share\mydb.bak, for a backup or restore and it is complaining that the file location can not be resolved or that the login has failed, there is an easy solution. Make sure the file is visible from the machine with sql server. If it is but you still get the error, it can be only one thing - the login that the SQL Server service is running under does not have access to the network location. In my case, I went to Control Panel -> Administrative Tools -> Services (on the server). I found the SQL Server service and checked it's properties and discovered that it was using the local administrator account (.\Administrator) which obviously doesn't have access to the rest of the domain. I changed this to a domain user, restarted the service, and voila, unc paths work fine now.

Hope this saves somebody some time.

I share the same experience. The sql server services should be running on NT domain account that has priviledge to access the network share. Again, it is the SQL server service not the current login user that must have an access to the network share.

I sooo appreciated this post! Thank you! It may not have saved me as much time as it could have it I had found your post earlier, but at least my forehead is no longer bleeding from hitting it against the screen :) It worked like a charm
and now I can focus on my "real" work!

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.