none
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

    Friday, June 24, 2005 9:29 PM

Answers

  • Thought I should share this:

    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.

    Thursday, June 15, 2006 1:57 AM
  • 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.
    Monday, June 27, 2005 11:16 PM
  • HowTo: Backup to UNC name using Database Maintenance Wizard
    http://support.microsoft.com/?kbid=555128


    Tuesday, June 28, 2005 7:48 PM

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.
    Monday, June 27, 2005 8:30 AM
  • 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.
    Monday, June 27, 2005 6:13 PM
  • 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.
    Monday, June 27, 2005 11:16 PM
  • HowTo: Backup to UNC name using Database Maintenance Wizard
    http://support.microsoft.com/?kbid=555128


    Tuesday, June 28, 2005 7:48 PM
  • 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
    Tuesday, November 29, 2005 12:59 AM
  • SQL Server can't attach data files that are not on the server machine. 

    You can either copy the data file to the local server and attach it there, or else you can install a server at the remote location and access the server remotely.
    Tuesday, November 29, 2005 1:25 AM
  • Thought I should share this:

    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.

    Thursday, June 15, 2006 1:57 AM
  •  Aranda wrote:

    Thought I should share this:

    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.

     

    Thursday, June 15, 2006 2:24 AM
  • Hi Tony

    The approach suggested by you in your reply... works in my case....

    Thanks for this information

     

    Sharad Sharma (KAPS)

    Monday, September 18, 2006 1:27 PM
  • 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!

    Thank you very much!

    Friday, June 01, 2012 3:04 AM
  • http://cybarlab.blogspot.com/2012/12/sql-server-database-backup.html

    Thursday, January 31, 2013 11:42 AM