{"id":84659,"date":"2020-10-08T16:00:46","date_gmt":"2020-10-08T13:00:46","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/"},"modified":"2020-10-08T16:00:46","modified_gmt":"2020-10-08T13:00:46","slug":"exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/","title":{"rendered":"#Exporting and Importing MSSQL Databases over a Network Share \u2013 CloudSavvy IT"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a291c39d92f1\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a291c39d92f1\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/#First_Lets_Cover_Exporting_and_Importing_MSSQL_Databases\" >First, Let\u2019s Cover Exporting and Importing MSSQL Databases<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/#Exporting_an_MSSQL_Database_by_Backup\" >Exporting an MSSQL Database by Backup<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/#Importing_an_MSSQL_Database_by_Restoring\" >Importing an MSSQL Database by Restoring<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/#Why_Cant_I_Export_Directly_to_a_Mapped_Drive\" >Why Can\u2019t I Export Directly to a Mapped Drive?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/exporting-and-importing-mssql-databases-over-a-network-share-cloudsavvy-it\/#How_Do_We_Get_Around_This_Missing_Functionality\" >How Do We Get Around This Missing Functionality?<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#Exporting and Importing MSSQL Databases over a Network Share \u2013 CloudSavvy IT&#8221;<\/strong><\/p>\n<div id=\"article-content-area\">\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7223\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/882826db74a0fed920068fe819cda9b3\/p\/uploads\/2019\/10\/05c795da.png\" alt=\"\" width=\"700\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>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\u2019t contain this function natively, we are going to show you a work around!<\/p>\n<h2><span class=\"ez-toc-section\" id=\"First_Lets_Cover_Exporting_and_Importing_MSSQL_Databases\"><\/span>First, Let\u2019s Cover Exporting and Importing MSSQL Databases<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>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.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Exporting_an_MSSQL_Database_by_Backup\"><\/span>Exporting an MSSQL Database by Backup<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To start exporting your database, open up SSMS, login to your server, and navigate to your database.<\/p>\n<p>You can backup the database by right-clicking the desired databases and navigating to Tasks &gt; Backup.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2214 size-large\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/cb8b932586c5a24eff63177a8f8d986c\/p\/uploads\/2019\/10\/6899b70f-1024x744.png\" alt=\"Run a backup for the database by right-clicking the desired databases and navigating to Tasks &gt; Backup.\" width=\"640\" height=\"465\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>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\u2019t need to modify any additional or advanced settings at this time, so just be aware of the menu and its layout.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2215 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/d5a0c50301a70fa0bf158fdfc2cf65c0\/p\/uploads\/2019\/10\/f55cdf93.png\" alt=\"Here, you can select options to select options to backup.\" width=\"911\" height=\"668\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>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.<\/p>\n<p>Click OK and your database will be backed up to the selected location.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Importing_an_MSSQL_Database_by_Restoring\"><\/span>Importing an MSSQL Database by Restoring<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To restore a database, we are going to follow a similar process but in reverse. Instead of exporting the database <em>to<\/em> a .bak file, we will be importing the database <em>from<\/em> the .back file.<\/p>\n<p>To import your database, right-click on your server instead of the database, and select Restore Database.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2216 size-large\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/977878055b9ab773355c6c66049579ba\/p\/uploads\/2019\/10\/bd1806e8-1024x744.png\" alt=\"Import your database by right-clicking on your server and selecting Restore Database.\" width=\"640\" height=\"465\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2217 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/4607d1de9877b6f37fd96f26ebefcfb1\/p\/uploads\/2019\/10\/73693c7e.png\" alt=\"Select the file you want to import from, under the Device setting, along with its location destination and any other necessary advanced options.\" width=\"864\" height=\"748\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Selecting the <em>[\u2026] <\/em>icon under the Source &gt; Device menu enables you select the .bak file that you want to import. Click <em>Add <\/em>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!<\/p>\n<p>At this point, if you are trying to import or export to a network share or m<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ed drive, you may encounter some difficulty.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Why_Cant_I_Export_Directly_to_a_Mapped_Drive\"><\/span>Why Can\u2019t I Export Directly to a Mapped Drive?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2073 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/11ae78b36ebb92869f3b380e0eeefac2\/p\/uploads\/2019\/09\/8181810a-2.png\" alt=\"Selection of the network location where the backup should be stored.\" width=\"845\" height=\"718\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you try to navigate to the drive by selecting the <em>[\u2026]<\/em> icon on the Select Backup Destination menu,\u00a0 your networked Z: drive won\u2019t be listed and only the local drives are shown.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2079 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/b9790aba236b38f0eccc7c0118d9641c\/p\/uploads\/2019\/09\/6120677b-1.png\" alt=\"Local drives to select from on the Select Backup Destination menu.\" width=\"911\" height=\"668\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Many try to manually enter the path to their network drive<em>. <\/em>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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2075 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/0331083da276cfb59ee2645a6362e998\/p\/uploads\/2019\/09\/e750de9c-1.png\" alt=\"You can directly enter the path to your network share drive on the Select Backup Destination menu.\" width=\"911\" height=\"668\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>But when you click OK on the final export, an error message similar to this one\u00a0 pops up:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2077 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/971f0584ce47aec8da727100712b81bd\/p\/uploads\/2019\/09\/91fc1045-1.png\" alt=\"If a backup cannot find the right path to store the backup, a failure message witll be displayed at final export.\" width=\"910\" height=\"667\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>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.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"How_Do_We_Get_Around_This_Missing_Functionality\"><\/span>How Do We Get Around This Missing Functionality?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In order to do this,\u00a0 a <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/watch-movies-tv-seriess\/\" data-internallinksmanager029f6b8e52c=\"8\" title=\"Watch Movies &amp; TV Series\" target=\"_blank\" rel=\"noopener\">series<\/a> 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 \u201cDisconnect\u201d or open command prompt, and enter:<\/p>\n<blockquote>\n<pre>net use Z: \/delete<\/pre>\n<\/blockquote>\n<p>Your network share is the drive letter of\u00a0<em>Z<\/em>\u00a0.<\/p>\n<p>Now that you have deleted the existing share, open SSMS and create a New Query with the following commands:<\/p>\n<blockquote>\n<pre>EXEC sp_configure 'show advanced options', 1;&#13;\nGO&#13;\nRECONFIGURE;&#13;\nGO&#13;\n&#13;\nEXEC sp_configure 'xp_cmdshell',1&#13;\nGO&#13;\nRECONFIGURE&#13;\nGO<\/pre>\n<\/blockquote>\n<p>Click<em> Execute<\/em> and you should see the following window:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgchk9 alignnone wp-image-2080 size-full\" src=\"https:\/\/www.cloudsavvyit.com\/thumbcache\/0\/0\/7c978a2816b0497fc5f54a96ec8ae1f6\/p\/uploads\/2019\/09\/7becf128.png\" alt=\"Executing a deletion of of the current network share.\" width=\"837\" height=\"616\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>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.<\/p>\n<p>Once you have ran the above command, create a new query and <em>Execute<\/em> the following command:<\/p>\n<blockquote>\n<pre>EXEC XP_CMDSHELL 'net use Z: \\192.168.1.1C$ \/user:Administrator '<\/pre>\n<\/blockquote>\n<p>Replace 192.168.1.1 with the IP address of your remote destination server and update to be the administrative user\u2019s password. Please note that to create these shares, you almost certainly need administrator rights.<\/p>\n<p>You can now verify that you\u2019re networked drive is configured correctly within SQL by executing the following query:<\/p>\n<blockquote>\n<pre>EXEC XP_CMDSHELL 'Dir V:'<\/pre>\n<\/blockquote>\n<p>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 &gt; Tasks &gt; Backup where you can see that the Z: drive is available when adding a destination location to which to backup.<\/p>\n<p>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 im<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/social-mediaa\/\" data-internallinksmanager029f6b8e52c=\"1\" title=\"Social Media\" target=\"_blank\" rel=\"noopener\">media<\/a>tely thereafter.\n<\/p><\/div>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener noreferrer\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.cloudsavvyit.com\/2072\/exporting-and-importing-mssql-databases-over-a-network-share\/\" target=\"_blank\" rel=\"noopener noreferrer\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#Exporting and Importing MSSQL Databases over a Network Share \u2013 CloudSavvy IT&#8221; 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\u2019t contain this function natively, we are going to show you a work around!&#8230;<\/p>\n","protected":false},"author":1,"featured_media":84660,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2019\/10\/05c795da.png","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-84659","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/84659","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=84659"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/84659\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/84660"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=84659"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=84659"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=84659"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}