Restore database from network drive

  • kma-359837

    SSC Journeyman

    Points: 90

    Hey guys,

    Thanks for all your comments. It works for me now. My issue is even though I have a local logical drive map to the targer server's shared drive, and net use shows that the UNC name. The restore still failed to locate the bakcup file. The run cmd showed that I have no access to the shared drive. So, one I fix that, it works . Thanks guys.

  • Maurice Pelchat

    Mr or Mrs. 500

    Points: 545

    Be careful how you grant your permissions, there is share permissions, and there is ntfs permissions. I got this problem some day because I forgot share permissions.

  • Sandeep.Lad

    Old Hand

    Points: 319

    I am facing same issue. Can anyone help me ?

    Sandeep Lad

  • jinx8402

    SSC Enthusiast

    Points: 136

    I did not want to create a new post, so I am bumping this sort of old topic...

    I am trying to determine what would be better to restore a larger (96GB) database from a remote server as far as I/O on the production server.

    The backup file is located on the production server and I want to restore into a test box. My two options are:

    1. Copy the bak file to the test server and restore locally

    2. Restore using an UNC path

    Which would have the least impact on the Prod server (where the bak file is located)?

    I am new to the DBA field and want to make the best decision possible.

    Thanks for any help

  • James Horsley

    Hall of Fame

    Points: 3196

    I would copy the bak file over and restore locally - I doubt there is much difference in the new traffic to do this once - but it means you can restore the bak multiple times to the test box without further load. Copying the bak over the network will saturate the lan connection on the prod box - making all other clients slower - so if it is an option I would recommend copying to e.g. a USB drive on the server then walking that to the test box ... watch out for USB weirdnes though - if you test box is similar config make sure the drive works on that OK before plugging into prod

  • ALIF-662928

    SSCertifiable

    Points: 6054

    As James said above will be the ideal solution for your issue, if not then instead of restoring directly from network, you can copy a backup file to test and restore it there as it will be more fast restoring locally without any effect on the network.

  • rkapiljith

    Valued Member

    Points: 55

    To check your computer name do this step...

    From the windows start button>>Run>>Type "msinfo32.exe"

    This will show the details of System Information,From the "System Summary" check the detail of "System Name" (xxxxxxx) which is your computer name.

    So your UNC path would be like:

    \\xxxxxxx\Kapils_Backup\WSS_Content_Farm_AA.bak

    This entry should be given for two sections after clicking "Add" button ie: for "Selected path" & "File name".

    Now the files from that shared location would be added to restore the DB.Dont worry about n/w paths not displaying...Your files are accessible check it...Have a good day.

    Regards,

    Kapiljith.R

  • Victor Alvarez-1138365

    SSC-Addicted

    Points: 494

    Hello ALIF,

    You can backup / restore your database to / from share folder, just defining the UNC name on the BACKUP/RESTORE statement.

    The BOL said:

    If you are using a network server with a Uniform Naming Convention (UNC) name or using a redirected drive letter, specify a device type of disk.

    you can try the following:

    backup database MyDB

    to disk = '\\shareserver\backup\backup.bak'

    or

    restore database MyDB

    from disk = '\\shareserver\backup\backup.bak'

    With Regards,

  • rksqldba

    Valued Member

    Points: 65

    Hey don`t use the entire path in the file name text box. use the path \\computername\foldername in the text box called SELECTED PATH. The use the file name backupfile.bak in the FILE NAME text box.

  • jdneilso

    Old Hand

    Points: 300

    When restoring a database, mapping the unc directly did not work for me. I got a permission error.

    I created a share where the backup file exists, then used the unc directly to the share:

    Example:

    so instead of

    \\111.111.111.111\f$\SQL\BACKUPDIR

    I created a share called BCKSHARE on SQL\BACKUPDIR

    Then I used

    \\111.111.111.11\sharename

    This worked for me.

  • GJR saurabh_singh

    SSC Enthusiast

    Points: 122

    I had the same issue.

    Database backup was on another server (server X). I tried restoring that backup on server A.

    I was able to access backup folder (not the shared folder but actual folder using \\serverX\v:\mssql\backup) using my windows login (which were also used as windows authentication to login sql serverA)

    Specifying the address gave me error that SQL Server was not able to access the location specified or there is issue with access rights.

    Then I tried using the shared folder address ie \\ServerX\backup - still got the same message

    Then I gave the account under which MSSQL service was running - READ rights on shared folder in ServerX. After this, when I gave shared folder address to restore database, it went on smoothly

  • ARC211

    Ten Centuries

    Points: 1283

    Creating a share where the file exists, then giving the account MSSQLSERVER rights to the folder worked like a charm... Thanks all

Viewing 12 posts - 16 through 27 (of 27 total)

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