Thursday 22 March 2012

DB Restore from Network Drive

 

 

I am a mail form one of my DBA friend to request about:

"Please advice that how I can restore the database from backup file placed on another server. Because while restoring any database system shows local drive as well as network drives attached."

 

To provide this solutions first we look at the, what MS says about it.

In MS Words

"For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running"

There are 2 options to solve this problem

Option-1

Just Copy the Backup file from network server to local drive of the SQL Server and then restore it.  Good one … Quick Solutions.

 

Option-2

Make a network drive and use this drive to restore database.

Only flipside is that this network drive mapping will remain till next SQL Server Service restart.

We can create network drive by 2 options

Option-A

Right click the "My network places" and Select the" Map network Drive" and provide drive name and UNC path to create network drive.

 Option-B

Use xp_cmdshell extended stored procedure to make network drive.

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Systax:

exec master..xp_cmdshell 'NET USE Y: \\MACHINE\SHARENAME <password> /USER:<user>'

 

EXEC xp_cmdshell 'NET USE K: \\Srv-1\Back pass123 /USER:DOM\joydeeep'

 

Hope you like this

 

Posted by: MR. JOYDEEP DAS

 

 

27 comments:

  1. Hi, UNC path to the backup file should work as well. Regards

    ReplyDelete
  2. i solved issue with option B,Thanks

    ReplyDelete
  3. Really good.. Option B is quite simple

    ReplyDelete
  4. Thanks All.
    Please follow : http://www.sqlknowledgebank.blogspot.in/

    ReplyDelete
  5. Thanks! saved my day!

    ReplyDelete
  6. Many thanks for your valuable post

    ReplyDelete
  7. thank u so much....it is really very helpfull for me..

    ReplyDelete
  8. I used OPTION B.. It is very simple.. great help.
    Thank you

    ReplyDelete
  9. I used OPTION B.. It is very simple.. great help.
    Thank you

    ReplyDelete