Restore database from network drive

  • ALIF-662928

    SSCertifiable

    Points: 6054

    Hi All,

    All of our database servers are doing backups and saving the .bak files out

    on a network share. With SQL 2005, how can I point to that share and restore

    the database in SQL 2005 environment?

    When I do 'restore database --> From Device --> File; when I click the 'ADD;

    button, I only see my local drives on the computer. How can I map to my

    network share and point to my restore file on the network?

    I need to do a restore for a database with the latest backup which is there on a network drive.

    --

  • Ian Yates

    SSCoach

    Points: 19738

    Browse there manually using Windows Explorer and then cut&paste the path and backup name into the bottom text field of the dialog box that's showing you your folders. You cannot map a network drive in your windows session and have SQL Server use it as its running under a service account. I haven't tried but you could set SQL Server to log on using a specific user account and have that account have a mapped drive persistently mapped? Using a full UNC of the form \\computername\share\folders\file.bak is always going to be more reliable than a mapped drive letter.

  • ALIF-662928

    SSCertifiable

    Points: 6054

    thanks, but is this possible to do that, i dont know exactly where to put the copied pathname you are telling, in which dialog box, More over when i specify the network path name, its unable to access even if i am keeping the network drive on local system through map drive.

  • James Horsley

    Hall of Fame

    Points: 3196

    Remember the network share the backup is being restored from has to be accessible to the user that is running the SQL server service - it is not enough to log on to the server console yourself and find the file

  • SG_Explorer

    Ten Centuries

    Points: 1339

    When you do 'restore database --> From Device --> File; click the 'ADD' Button-->

    Get the FULL UNC path as "\\ComputerName\Shared folder\Backup\abc.Bak" by mapping in the

    explore window.

    Copy the path & paste this path under add button-->File Name. It will work.

  • ALIF-662928

    SSCertifiable

    Points: 6054

    i am not restoring it, i am trying to give the backup path, anyways thanks for your reply, let me know how can you add a drive

    In my case, the drive is there in the local system, but the problem is when i am giving a backup, its only showing 2 drives out of 4 i have. its on a cluster server

  • Marvin D

    SSCarpal Tunnel

    Points: 4058

    I think I see your issue, you have a local drive that you want to back up to, yet, your SQL Server database is on a Cluster that does not see that local drive (probably because it is connected to the San box).

    This situation will require you to use UNC naming as there is no guarantee that the local server will be the active server in the cluster. This basically mean that you will have to backup over the network even though this is a local drive because SQL Server does not see the local drive since it is not part of the cluster.

    The next issue you will face is backing up over the network. Not a very good way to accomplish backups as SQL server is not very tolerate for small network hiccups that always occur.

    The best way to do this is to backup to the cluster and then utilize xp..cmd shell to move the file using UNC to the drive on the local machine to your target backup drive.

    Remember, Clusters are very different from stand alone servers. The best way is to think of the cluster as it's own server as it is very independent of the nodes hosting it.

    Hope this helps

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • johnb-522860

    SSC Journeyman

    Points: 75

    I have the similar Issue.

    SQL 2005 Cluster with a san box attached. After the Cluster was configured we added a drive on the san ( G.) Now we want to create a maintenance plan so the back up copy to the G drive rather than the local D or E Drive. I can't see the G drive in the context of Management Studio when creating the maintenance plan.

    I can only accomplish the task using a share. G=\\sql\backups

    Is there a way to get the cluster to see the G drive as a local drive?

  • harmondan

    Valued Member

    Points: 54

    It may help to check your cluster configuration. Use the cluster admin to add the G:\ drive as a dependent SQL resource.

  • ansz5

    Ten Centuries

    Points: 1282

    I am trying to restore a database on SQL Server 2005, using the backup placed on a different server ( in same domain). I have shared the folder and tried the same with sharing the complete drive too.

    The account, I am using, has been granted full permisisons... but still when I type the complete path ( in file name test box), the message says..the database engine cannot find the specified path or the account does not have access to it. Should the server(with shared folder) have SQL Server installed on it..???

    Also what should be the computer name here - "\\ComputerName\Shared folder\Backup\abc.Bak" I used the local computer name (in case of mapped drive) and the other server name in case of shared folder.

    Pls advise.

    Thanks !!!

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    ansz5 (8/1/2008)


    I am trying to restore a database on SQL Server 2005, using the backup placed on a different server ( in same domain). I have shared the folder and tried the same with sharing the complete drive too.

    The account, I am using, has been granted full permisisons... but still when I type the complete path ( in file name test box), the message says..the database engine cannot find the specified path or the account does not have access to it. Should the server(with shared folder) have SQL Server installed on it..???

    Also what should be the computer name here - "\\ComputerName\Shared folder\Backup\abc.Bak" I used the local computer name (in case of mapped drive) and the other server name in case of shared folder.

    Pls advise.

    Thanks !!!

    Does the account running SQL Server have access to the share? If the account is not a domain account, then you cannot get access to the shared folder.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • rajiniforu

    Ten Centuries

    Points: 1147

    Try restoring the database using the following query:

    restore database "dbname"

    from disk = 'Servername\sharedlocation\db.bak'

    servername - This is not the local server name, it is the servername where the backup files are located.

    or else to do it through GUI:

    Know the exact shared location, you can type the servername(\\servername) in the "run" and that would autoamtically display all the shared locations on the server to which your account has access. From there navigate to the correct location and use it in the restore path.

    The other server need not have SQL installed on it. Its just storing the backup files.

  • ansz5

    Ten Centuries

    Points: 1282

    Hi !

    Thank you every one for all you replies and suggetsions,

    The problem had been resolved , I could not post it here on the forum.

    I used the command net use on command prompt to find out exact name (UNC) name of the shared drives and used this complete name in the File name text box of locate backup window , while restoring the backup.

    It worked.

    Thanks again !

    Best Regards,

    ansz5

  • kma-359837

    SSC Journeyman

    Points: 90

    I have similar issue as the last user. I try to restore a db from a db backup file on another server. I use the full UNC name as \\sourcecomputer\shre drive\testdb.bak in the file name. Got the same erorr. I wonder if someone can confirm if itworks for them. My account I use has full admin priv on both servers.

  • ansz5

    Ten Centuries

    Points: 1282

    May I know , if you are using a shared folder or you have a network drive mapped on your server ?

    If its a network drive mapped, then pls use 'net use' command on command prompt to fetch the complete name for the drive.

    I faced the same problem but when I fetched the name this way, it worked.

    Please refer to my previous reply for steps.

    Hope it helps.

    🙂

    Rgds,

    ansz5

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply