Monday, July 17, 2017

Restore Database Backup Over a Network Drive

How do you restore a Microsoft SQL database backup over a network drive without having to download to local? Since the database server is run as a service under an account that often without network access permission, it wouldn't be possible to be able to select the network drive/path when you are performing a restore. Nonetheless, we are not left without workaround.

To restore database backup over a network, you will need to run the following SQL statements using your SQL Management Studio.

--enable to show advanced options
EXEC sp_configure 'show advanced options', 1
GO
--apply changes for show advanced options
RECONFIGURE
GO
--enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--apply changes for xp_cmdshell
RECONFIGURE
GO
--replace network path, username and password accordingly
EXEC xp_cmdshell 'NET USE Z: \\SERVER\Path password /USER:Domain\Username'
GO

Once the above are executed, you should be able to choose Z:\ network drive to restore your database backup file. I hope it helps.

No comments:

Post a Comment

Do provide your constructive comment. I appreciate that.