When working with MSSQL databases, you may find that you need to export databases over a network share directly from the SQL Server Management Studio (SMSS) interface. While SMSS doesn’t contain this function natively, we are going to show you a work around!
First, Let’s Cover Exporting and Importing MSSQL Databases
As a basic necessity to utilize this guide, we want to make sure you know how to export and import your databases as .bak files. This is both how you backup an MSSQL database and how you move a database between two servers. This process is referred to internally within SSMS (SQL Server Management Studio) as Backing Up or Restoring a database, depending on which direction you are going.
Exporting an MSSQL Database by Backup
To start exporting your database, open up SSMS, login to your server, and navigate to your database.
You can backup the database by right-clicking the desired databases and navigating to Tasks > Backup.
This opens the Backup Database menu where you can select your database to backup, your backup type, file destination, and numerous other options. For the sake of this article, we don’t need to modify any additional or advanced settings at this time, so just be aware of the menu and its layout.
When creating an MSSQL backup, your database will be exported as a .bak file. Under the destination section of the above menu, you can add or remove locations to backup your database to. You can add one or multiple locations for the destination location of your .bak file.
Click OK and your database will be backed up to the selected location.
Importing an MSSQL Database by Restoring
To restore a database, we are going to follow a similar process but in reverse. Instead of exporting the database to a .bak file, we will be importing the database from the .back file.
To import your database, right-click on your server instead of the database, and select Restore Database.
This brings 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 location destination as well as any other advanced options you may need.
Selecting the […] icon under the Source > Device menu enables you select the .bak file that you want to import. Click Add and you can navigate to the .bak file you wish to restore. Click OK and your database will be imported to the current SQL server!
At this point, if you are trying to import or export to a network share or mapped drive, you may encounter some difficulty.
Why Can’t I Export Directly to a Mapped Drive?
The reason that this guide exists is because Microsoft natively does not include a function to export a database directly to a network share or mapped drive. When you are exporting your database for backups or to import to another server, you cannot select any networked drives from the menu or manually enter a remote location.
If you try to navigate to the drive by selecting the […] icon on the Select Backup Destination menu, your networked Z: drive won’t be listed and only the local drives are shown.
Many try to manually enter the path to their network drive. If you directly enter the path to your network share drive, such as Z:mybackup.bak, you can click OK on the Select Backup Destination menu.
But when you click OK on the final export, an error message similar to this one pops up:
As you can see, this can be a problem if you need to backup databases to a remote location for lack of space or for security reasons. Typically, you would then have to export the database locally, copy it to the remote server, then import it to the destination SQL server.
How Do We Get Around This Missing Functionality?
In order to do this, a series of scripts must be run to add the networked drive to the MSSQL selectable menu. The first thing to do is to delete your current network share, as you are recreating it during the process within SQL command line. To do that, simply right-click on the drive in file-explorer and select “Disconnect” or open command prompt, and enter:
Your network share is the drive letter of Z .
Now that you have deleted the existing share, open SSMS and create a New Query with the following commands:
Click Execute and you should see the following window:
What this script does is configure the command shell for SQL to let you run your own scripts directly from the SQL compatible command shell.
Once you have ran the above command, create a new query and Execute the following command:
Replace 192.168.1.1 with the IP address of your remote destination server and update to be the administrative user’s password. Please note that to create these shares, you almost certainly need administrator rights.
You can now verify that you’re networked drive is configured correctly within SQL by executing the following query:
You have enabled the commandshell, created a network share within SQL, and verified its existence. You can now export and import backups directly from this location. You can see it by going to your Database > Tasks > Backup where you can see that the Z: drive is available when adding a destination location to which to backup.
Congratulations! You have now setup a network share within SQL that persists even if you close and reopen SSMS. You can backup or restore directly to this location now, and no longer need to worry about backing up databases locally, only to have to move them to a remote server immediately thereafter.
