Question: How to Map Network Drive as Fixed Drive in SQL Server?
Answer: The command to map any drive as a fixed drive is Windows command and you can run that in the SQL Server with the help of the extended stored procedure. Here is the script for the same. Open a new query window in the SSMS and run the following command and it will map your network drive as a local drive to windows and you can use that to take your backup or store data/log files in SQL Server.
EXEC xp_cmdshell ‘net use <driver letter> \\<servername>\<sharename> /user:<username> <password>’
You can write the above statement as follows:
EXEC xp_cmdshell 'net use z: \\192.168.1.1\shareddrive /user:YOURUSERNAME PASSWORD'
Once you run the above code it will map your shared folder share drive from the server 192.168.1.1 to the local system as a Z: drive. It is a very convenient and popular method to map your share drive to local drive. I strongly suggest you bookmark this blog post.
This question came up recently in my Comprehensive Database Performance Health Check while helping one of the clients to move their database from one server to another server. I love little problems like this one as they are indeed a lot of fun to resolve.
Here are a few other blog posts which you may find interesting:
- How to Copy Files in SQL Server? – Interview Question of the Week #257
- Can Admin Rename SA Account in SQL Server? – Interview Question of the Week #256
- Why Execution Plan Operator Read More Rows Than Available? – Interview Question of the Week #255
- What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254
- MemoryGrantInfo – What are Different Status of IsMemoryGrantFeedbackAdjusted? – Interview Question of the Week #253
- How to Disable Batch Mode in SQL Server? – Interview Question of the Week #252
- How to Determine Read Intensive and Write Intensive Tables in SQL Server? – Interview Question of the Week #251
You can follow me at twitter here: @pinaldave
Reference: Pinal Dave (https://blog.sqlauthority.com)
