When working with MSSQL databases, you may find that you need to export databases to a network share directly from SQL Server Management Studio (SMSS) interface. Although SMSS does not contain this function natively, we will show you a solution!
Let’s start with the export and import of MSSQL databases
To use this guide, we want to make sure that you know how to export and import your databases as .bak files. This is both how you back up an MSSQL database and how you move a database between two servers. This process is called internally in SQL Server Management Studio (SSMS) backing up or restoring a database, depending on which direction you are heading.
Exporting an MSSQL database by backup
To start exporting your database, open SSMS, connect to your server, and navigate to your database.
You can back up the database by right clicking on the desired databases and going to Tasks> Back up.
Backup.” width=”640″ height=”465″ onload=”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);” onerror=”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);”/>
This opens the Back Up Database menu where you can select your database to back up, your backup type, file destination, and many other options. For the purposes of this article, we don’t need to change any additional or advanced settings just yet, so just consider the menu and its layout.
When creating an MSSQL backup, your database will be exported as a .bak file. In the destination section of the menu above, you can add or remove locations to back up your database. You can add one or more locations for 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 restore
To restore a database, we will follow a similar process but in reverse order. Instead of exporting the database at a .bak file, we will import the database of 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 destination location along with any other advanced options you might need.
Selection of […] The icon under the Source> Device menu allows you to select the .bak file you want to import. Click on Add and you can navigate to the .bak file you want to restore. 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 share or a mapped drive, you may have difficulty.
Why can’t I export directly to a mapped drive?
The reason this guide exists is because Microsoft does not natively include a feature to export a database directly to a network share or mapped drive. When you export your database for backups or to import it to another server, you cannot select network drives from the menu or manually enter a remote location.
If you try to access the drive by selecting the […] in the Select a backup destination menu, your networked Z: drive will not be listed and only local drives are displayed.
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 a backup destination menu.
But when you click OK on the final export, an error message similar to the following appears:
As you can see, this can be a problem if you need to back up databases to a remote location due to lack of space or for security reasons. Typically, you should then export the database locally, copy it to the remote server, and then import it to the destination SQL server.
How to get around this missing feature?
To do this, a series of scripts must be run to add the network 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 in the SQL command line. To do this, just right-click on the drive in File Explorer and select “Disconnect” or open the command prompt, then enter:
net use Z: /delete
Your network share is the drive letter of Z .
Now that you’ve deleted the existing share, 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 on Execute and you should see the following window:
This script configures the command shell for SQL so that you can run your own scripts directly from the SQL compatible command shell.
Once you have run the above command, create a new query and Execute the 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 destination server and update the administrative user password. Please note that to create these shares you almost certainly need administrator rights.
You can now verify that your network drive is configured correctly in SQL by running the following query:
EXEC XP_CMDSHELL 'Dir V:'
You have enabled the command interpreter, 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 adding a destination location to backup.
Congratulations! You have now configured a network share in SQL that persists even if you close and reopen SSMS. You can now back up or restore directly to this location, and you no longer have to worry about backing up databases locally, only to move them to a remote server immediately after.