Last post Sep 16, 2019 05:20 PM by limno
Sep 12, 2019 08:38 AM|cenk1536|LINK
I have an ASP.NET web api application. It is working on my development machine which has SQLExpress. When I use express, it creates MDF file. I wonder how I can migrate from express to prod which has SQL Server 2012?
Sep 12, 2019 08:49 AM|Mikesdotnetting|LINK
Usually, the steps are
It makes no difference that the original database was created on SQL Express.
Sep 12, 2019 10:47 AM|cenk1536|LINK
I should NOT add the mdf file in the production connection string right?
Sep 12, 2019 10:53 AM|Mikesdotnetting|LINK
I presume that you are talking about a connection string with AttachDbFilename in it? If so, No.
The connection string that you use will depend on whether the SQL Server is on the same machine as the web site. If it is, you can use either standard security or trusted connection. Otherwise you will need to use standard security:
Ideally, you shouldn't connect using the sa account. You should create another user who only has permissions to do the minimum required by the application, typically dbdatareader and dbdatawriter.
Sep 12, 2019 10:58 AM|cenk1536|LINK
Thank you, I will try and let you know.
Sep 12, 2019 11:54 AM|PatriceSc|LINK
Yes this is just a convenience for developers (it allows to automatically "attach" a db file to the local SQL Server engine without any action so that "it just works").
For a production SQL Server "engine" you (or rather a DBA ?) add explicitly the database(s) you need and your app just connect to an already configured database. Your app doesn't care about the MDF file location and is not supposed to be able to reach this
physical location anyway...
Sep 12, 2019 12:16 PM|cenk1536|LINK
I generated the script (tasks-> generate scripts) from MSSMS and if I create those on the production server is OK right?
Sep 12, 2019 12:28 PM|PatriceSc|LINK
https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-to-other-servers?view=sql-server-2017 this is a way to do this.
I still prefer the backup and restore approach whenever possible (ie you can place the backup file at a location where the target server can reach it).
Sep 12, 2019 12:38 PM|cenk1536|LINK
If I backup, can I exclude data? I just want the tables.
Sep 12, 2019 12:41 PM|PatriceSc|LINK
Ah ok, should be fine anyway. Just do that...
Sep 16, 2019 05:20 PM|limno|LINK
Another good option you can do through SSMS:
You can deploy your database through a data-tier application. In your case without data use dacpac. If you need both data and schema, use bacpac.