Post reply

How can i Restore from Mapped drive?

  • July 5, 2005 at 7:41 pm

    #109927

    How can i restore from mapped drive?

    I have ServerA and ServerB.

    i want to restore serverA backup to ServerB.

    I mapped the backup folder from ServerA to ServerB.

    when i try restore from ServerB i am not able to see that mapped drive.

    when i click on from disk,i see only local drive none of my mapped drive.

    What could be the reason please help me?

    Thanks

  • July 5, 2005 at 8:10 pm

    #571068

    Andy,

    Firstly, you have to check the MSSQL service is started by domain account and then you can simply run this:

    RESTORE LOG EPINAV

    FROM DISK = '\\Server A\BkupFolder\bkupfile.bak'

    WITH RECOVERY

    If you are doing from EM, u can put the network path out there..I usually do it from Query Analyzer.

    HTH

    Regards,

    Dilip

  • July 5, 2005 at 8:11 pm

    #571069

    Oops!! It should be "Restore Database" not "restore log"..if u have subsequent transaction logs then u can use Restore Log after Restore Database..

    Sorry for the confusion

  • July 5, 2005 at 9:06 pm

    #571077

    Yes i'm using System administrator account.

    But why i can't see th emapped drive ?

    i tried UNC name too still i got error.

    if anyone have any idea please let me know.

    Thanks

  • July 5, 2005 at 9:19 pm

    #571080

    Can you ping the server? 

    What account is the SQL Server running under?  Is it running as Local Service or a domain account?  If it's running as a domain account, does that account have access to the mapped drive directory?

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • July 5, 2005 at 11:16 pm

    #571093

    You cannot see the mapped drive because drive mappings are defined on a per account basis, so unless the sql server service is running under the same NT account as you are logged in to the mapping us not available.  As for UNC names they absolutely should work, so you need to check all the basics such as permissions etc. If SQl server is running under LoaclSystem then it by definition is not allowed network access so you need to establish what account is being used for that service.

    Mike

  • July 5, 2005 at 11:29 pm

    #571094

    You cannot see the mapped drive because drive mappings are defined on a per account basis, so unless the sql server service is running under the same NT account as you are logged in to the mapping us not available.  As for UNC names they absolutely should work, so you need to check all the basics such as permissions etc. If SQl server is running under LoaclSystem then it by definition is not allowed network access so you need to establish what account is being used for that service.

    Mike

  • July 5, 2005 at 11:44 pm

    #571096

    You cannot use mapped drives in SQL Server, even if you are logged in using the same account.

    UNC names work if the SQL Server services are running under a domain account and that account has been granted access to the shared folder.

     

     

     

    --------------------
    Colt 45 - the original point and click interface

  • July 6, 2005 at 12:02 am

    #571099

    The reason is that the MSSQLSERVER service is running under a separate set of NT credentials - all services are related to an NT account. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on locally to the server doesn't it). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server doing the backup, not you. This is the same for backups done via SQL Executive/SQL Agent - they just pass the TSQL to SQL Server to run, so it's still MSSQLSERVER doing the backup/restore.

    For this reason the backup gui does not show you mapped drives or allow a UNC path to be typed in. You have to use raw TSQL commands to do the backup.

    The default set of NT credentials used by MSSQLSERVER is the Localsystem account. You can check what userid that MSSQLSERVER is running under by looking at control panel/services highlighting MSSQLSERVER and choosing the start-up option.

    The Localsystem account has no access to shares on the network as it isn't an authenticated network account. Therefore SQL Server running under this account cannot backup to a normal network share.

    So, if you want to backup to a network share you have two choices :-

    1. Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights.

    or

    2. Amend the following registry value on the TARGET server and add the sharename you want to dump to - the share does not then authenticate who is coming in and so a Localsystem account will work. The server service on the target server must be re-started before the change takes effect. Note that this effectively removes ALL security on that share, so you're letting anyone/anything have access. Which is probably not something you want to do with production business data.

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares

    Whichever method you use, you MUST also use a UNC name to reference the file required and not a drive letter.

    e.g. (6.5) DUMP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'

    (7.0/2000) BACKUP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'

     

    Hope this helps.

     

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • July 6, 2005 at 1:27 am

    #571119

    We have a SQL server that houses all our testing databases (there are a lot) and all our backups are held on a different server. We mapped a drive on the SQL server to the backup directory on the backup server. Then I created a SQL Agent job that runs every time SQL is started (or the server rebooted):

    xp_cmdshell "net use S: \\GUMSQLBACKUPS\BACKUP

    password /user:domainname\domainaccount /persistent:yes"

    The S: \\GUMSQLBACKUPS\BACKUP is our mapped backup folder, and obviously you would need to replace domainname\domainaccount with the correct details. The domain account is the account under which SQL runs.

    When I do a backup or restore via Enterprise Manager after the job has run I am able to see the S drive as a backup device location.

    Give it try, it works sweetly.

     

  • July 6, 2005 at 8:59 am

    #571289

    you can refer this kb article, its about backup but the same logic can be applied to restore too..

    http://support.microsoft.com/default.aspx?scid=kb;en-us;q207187

    Good luck 🙂

  • July 7, 2005 at 9:44 am

    #571908

    I have done it using t-sql commands via QA or job steps.

    From BOL:

    BACKUP DATABASE Northwind    TO DISK = '\\uncservername\Northwind.bak'RESTORE FILELISTONLY    FROM DISK = '\\uncservername\Northwind.bak'RESTORE DATABASE TestDB    FROM DISK = '\\uncservername\Northwind.bak'   WITH MOVE 'Northwind' TO '\\uncservername\test\testdb.mdf',   MOVE 'Northwind_log' TO '\\uncservername\test\testdb.ldf'GO
    

     
    

    can't be done via EM or UI.
    

     
    

    good luck..
    Don't count what you do, do what counts.
    SQL Draggon

  • July 7, 2005 at 9:52 am

    #571921

    You can type the full UNC path and file into the appropriate dialog in EM, you just can't browse for the file

    Also, can you edit your post so that is doesn't extend so far to the right ??

     

    --------------------
    Colt 45 - the original point and click interface

  • July 7, 2005 at 10:35 am

    #571953

    Thanks for ur advise, Itried that but after data restore database showing loading after so many hours,how can i bring to normal mode.

    Now thats the problem?

    Please advise....

    Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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