{"id":38606,"date":"2020-03-13T06:00:56","date_gmt":"2020-03-13T13:00:56","guid":{"rendered":"http:\/\/devblogs.microsoft.com\/premier-developer\/?p=38606"},"modified":"2020-03-31T19:40:16","modified_gmt":"2020-04-01T02:40:16","slug":"how-do-i-get-data-from-sql-ce-to-azure-db","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/how-do-i-get-data-from-sql-ce-to-azure-db\/","title":{"rendered":"How do I get data from SQL CE to Azure DB?"},"content":{"rendered":"

In this post, Premier Consultant Tim Omta<\/a> explains how to migrate from SQL CE to SQL Azure.<\/p>\n

\n

I was recently working with a customer that needed to migrate an old web site to Azure. It was an ASP.Net Razr Web Pages site was originally created several years ago using WebMatrix.<\/p>\n

WebMatix is now deprecated, but it allowed a developer to quickly create a web frontend and database backend. By default, the database was created as a local file database using SQL CE.<\/p>\n

Part of my job to migrate the site for them was to get the data migrated to an Azure DB instance.<\/p>\n

After a few web searches and poking around it didn\u2019t seem to be a common question with an easily laid out set of steps, so I documented the approach that allowed me to install an extension and use my favored SQL Server DB interface, SQL Server Management Studio (SSMS). The source for the extension is located in the author\u2019s GitHub site: https:\/\/github.com\/ErikEJ\/SqlCeToolbox<\/a><\/p>\n

Installing the extension into SSMS 18<\/h3>\n

This is a manual process, but worth it if you have a few databases to convert. Here is the installation process:<\/p>\n

First, navigate to this page: https:\/\/marketplace.visualstudio.com\/items?itemName=ErikEJ.SQLServerCompactSQLiteToolboxforSSMS<\/a><\/p>\n

    \n
  1. Click the Download button and save the extension, which is named SSMSToolbox.vsix.<\/li>\n
  2. Copy the vsix file to C:\\Program Files (x86)\\Microsoft SQL Server Management Studio 18\\Common7\\IDE\\Extensions<\/em>. That is the default installation directory for SSMS.<\/li>\n
  3. Rename SSMSToolbox.vsix to SSMSToolbox.zip.<\/li>\n
  4. Use an unzip utility or PowerShell\u2019s Expand-Archive command to decompress the zip file.<\/li>\n
  5. There should be a new folder in your Extensions directory named SSMSToolbox. Rename the SSMSToolbox folder to SQLCEToolbox.<\/li>\n
  6. The manual install of the extension is now complete.<\/li>\n<\/ol>\n

    Using the Extension<\/h3>\n

    Start SSMS 18 and click on the View menu. You\u2019ll see a new item named \u201cSQLLite\/SQL Server Compact Toolbox\u201d. Choose it from the View menu.<\/p>\n

    <\/p>\n

    This will open the Toolbox panel on the left of SSMS. Hover over the tool icons until you see the one with the tooltip \u201cAdd SQL CE 4.0 Connection\u201d and click it.<\/p>\n

    <\/p>\n

     <\/p>\n

    You\u2019ll be presented with a dialog that allows you to navigate to and select the SQL CE file that you want to convert. Fill out the details for your source DB file and click OK.<\/p>\n

    <\/p>\n

    You\u2019ll see your SQL CE database file show up in the toolbox panel and you\u2019ll be able to navigate the various tables it contains.<\/p>\n

    <\/p>\n

    Right click on your CE database file, choose Script Database, then choose Script Schema and Data for SQL Azure\u2026<\/p>\n

    <\/p>\n

    Once the script is complete, save the script, then open it (or paste it) in a query window connected to your Azure DB.<\/p>\n

    Run the script.<\/p>\n

    You’re migrated!<\/p>\n","protected":false},"excerpt":{"rendered":"

    In this post, Premier Consultant Tim Omta explains how to migrate from SQL CE to SQL Azure.<\/p>\n","protected":false},"author":582,"featured_media":38607,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[25,1],"tags":[161,308],"class_list":["post-38606","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-permierdev","tag-azure-sql","tag-premier"],"acf":[],"blog_post_summary":"

    In this post, Premier Consultant Tim Omta explains how to migrate from SQL CE to SQL Azure.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/38606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=38606"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/38606\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/38607"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=38606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=38606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=38606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}