When working with MSSQL databases, you may need to export databases via a network share directly from the SQLSS Management (SMSS) interface. While SMSS does not include this feature, we will show you a work around!
Let us first cover the export and import of MSSQL databases
As a basic necessity for using this guide, we want to make sure you know how to export and import your databases as .bak files. How to back up an MSSQL database and how to move a database between two servers. This process is called internally within SSMS (SQL Server Management Studio) as backing up or restoring a database, depending on which direction you are going.
Export an MSSQL database with backup
To start exporting your database, open SSMS, log in to your server and navigate to your database.
You can back up the database by right-clicking on the desired databases and navigating to Tasks> Backup.
When you create an MSSQL backup, your database is exported as a .bak file. Under the destination section in the menu above, you can add or remove locations to back up your database to. You can add one or more locations to the destination location of your .bak file.
Click OK and your database will be backed up to the selected location.
Import an MSSQL database by restoring
To restore a database, we will follow a similar process but in reverse order. Instead of exporting the database to a .bak file we import the database from .back file.
To import your database, right-click on your server instead of the database and select Restore Database.
This will take you to a menu similar to the backup menu, where you can select the file you are importing from, under the device setting, and its destination, as well as any other advanced options you may need.
Select […] icon under the Source> Device menu, you can select the .bak file you want to import. Click add and you can navigate to the .bak file you want to recover. Click OK and your database will be imported to the current SQL server!
At this point, if you try to import or export to a network resource or mapped device, you may encounter some issues.
Why can’t I export directly to a mapped device?
The reason for this guide is that Microsoft does not include a feature for exporting a database directly to a network resource or mapped device. When you export your database for backups or to import to another server, you can not select any network devices from the menu or manually enter a remote location.
If you are trying to navigate to the device, select […] icon in the Select backup destination menu, your networked Z: device will not be listed and only the local devices will be displayed.
Many people try to manually enter the path to their network device. If you directly enter the path to your network sharing device, for example Z: mybackup.bak, you can click OK in the Select backup destination menu.
But when you click OK on the final export, an error message like this appears:
As you can see, this can be a problem if you need to back up databases to a remote location for lack of space or for security reasons. Typically, you need to export the database locally, copy it to the remote server, and then import it to the destination SQL server.
How can we get around this missing feature?
To do this, a series of scripts must be run to add the network device to the MSSQL selectable menu. The first thing to do is delete your current network share, as you will restore it during the SQL command line process. To do so, simply right-click on the device in File Explorer and select “Disconnect” or open the command prompt and enter:
net use Z: /delete
Your network share is the unit letter for WITH .
Now that you have deleted the existing resource, open SSMS and create a new query with the following commands:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell',1 GO RECONFIGURE GO
Click Drive and you should see the following window:
What this script does is configure the SQL command shell so that you can run your own scripts directly from the SQL compliant command shell.
After running the above command, create a new query and Drive following command:
EXEC XP_CMDSHELL 'net use Z: \192.168.1.1C$ /user:Administrator '
Replace 192.168.1.1 with the IP address of your remote server and update so that the password of the administrative user is. Note that you almost certainly need administrator privileges to create these resources.
You can now verify that your network device is properly configured in SQL by performing the following query:
EXEC XP_CMDSHELL 'Dir V:'
You have enabled the command shell, created a network share in SQL, and verified its existence. You can now export and import backups directly from this location. You can see this by going to your Database> Tasks> Backup where you can see that the Z: drive is available when you add a destination to be backed up.
Congratulations! You have now configured a network share within SQL that remains even if you close and open SSMS again. You can back up or restore directly to this location now and no longer have to worry about backing up databases locally, just having to move them to a remote server immediately afterwards.