How to transfer ASP.NET MVC Database from LocalDb

Question

I created a new ASP.NET MVC 5 project in Visual Studio 2013 (Express for Web) and by default, the project uses LocalDb as its database, but how do you transfer or migrate the database to SQL Server?

I want to use SQL Server for the database instead of LocalDb. But how?

Answer 1

It sounds like you may want to move the data from your local database to sql server. If so, the easiest way to do this would be to back up your local database and then restore it on the server.

To back up: https://msdn.microsoft.com/en-us/library/ms187510.aspx#SSMSProcedure

To restore: https://msdn.microsoft.com/en-us/library/ms177429.aspx

EDIT:

If you need to install an instance of SQL Server: https://msdn.microsoft.com/en-us/library/ms143219.aspx

Answer 2

In relation to OverLords answer, it worked perfectly for me thanks!

If anyone is struggling with the connection string use:

    <add name="CONNECTIONSTRINGNAME" connectionString='data source= DATABASE SOURCE initial catalog=&quot;DATABASE NAME &quot;;user id=&quot;USERID&quot;;password=PASSWORD;MultipleActiveResultSets=True;' providerName="System.Data.SqlClient" />

Answer 3

Change the connectionString in your web.config

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-KlikRX-20141203034323.mdf;Initial Catalog=aspnet-Test-20141203034323;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

to your own database connectionString, for example :

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=7.7.7.7\sql;Initial Catalog=TestDB;User ID=sa;Password=sa" />
  </connectionStrings>

Answer 4

You can't back up you LocalDB like that. There is no SSMS interface for doing a backup there. You will have to make a copy of the localDB's MDF file and attach that to SQL Server Express (or higher). Then you can either move the files or do a backup-restore.

Answer 5

I had the same problem and just solved this...so the main point is default connection string...which you need to modify correctly otherwise it is pointless..and impossible to connect properly. So copy all you aspnetroles...users table to online database( they should look the same as in your local database). You can compare schema(local db) with real db. It is quit well explained by "Overlord" -> Explanation

But after lets now correctly modify defaultconnection string That is my default string before modification:

 <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-track_spa-20180502025513.mdf;Initial Catalog=aspnet-track_spa-20180502025513;Integrated Security=True" providerName="System.Data.SqlClient" />

That is my modified default string after modification:

<add name="DefaultConnection" connectionString="Data Source=servername,portnumber;Initial Catalog=AttendanceTrak;Integrated Security=False;User Id=****;Password=*****;Encrypt=True;TrustServerCertificate=False;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

servername - should be your server. portnumber - should be your server port

It took me ages to finally get it working properly...but this small trick with default string just made it! Hops this helps

Answer 6

Got it!

Based on @warheat1990's answer, you just have to change the connection string. But @warheat1990's answer had a little too much change. So here's my original (LocalDb) connection string:

<add name="DefaultConnection"
     connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-my_project-20150318100658.mdf;Initial Catalog=my_project-20150318100658;Integrated Security=True"
     providerName="System.Data.SqlClient"/>

To connect it to SQL Server instead of LocalDB, I modified the connection string into:

<add name="DefaultConnection"
     connectionString="Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=my_project;Integrated Security=True"
     providerName="System.Data.SqlClient"/>

Thanks to @warheat1990 for the idea of simply changing the Web.config. My first thoughts were to identify and use the feature that VS supplies, if theres any. Because Microsoft doesnt have a concise documentation on how to do this.