Couple of weeks ago, I overcome situation where I need to restore SQL server database from network drive to my SQL server. An easy option was to copy the database to local drive on the SQL Server and do the normal restore operation; but that was costly in term of time and space as it was a huge database size.. by default MS SQL server doesn’t allow you to restore the database from network drive unless you enabled a couple of features in SQL Server; to do that you need to run the below SQL statement:
EXEC sp_configure 'XP_CMDSHELL' , 1
GO
EXEC xp_Cmdshell 'net use p: \\ServerName\SharedFolderName'
GO
EXEC xp_fixeddrives 1
GO
to make sure the above statements configured the network drive correctly, you should see the drive name and estimated size for it as a result of executing the last SP (xp_fixeddrives). if you didn’t see the list of drives available on the server, then there something went wrong and those steps didn’t completed successfully
Completing these steps will let a drive called P: showing on your normal restore window and you can explore and locate your backup file (.Bak). also I’ve overcome a situation where the drive isn’t showing on my restore window and I can’t explore neither to locate the .bak file; even after running the above statement, so I had to delete the P drive and re-define it again.. I deleted it using the below statement then rerun the above statement and things worked fine with me
EXEC XP_CMDSHELL 'net use P: /delete'
