locked
Moving from SQLExpress to SQL Server 2012 RRS feed

  • Question

  • User-1104215994 posted

    Hello,

    I have an ASP.NET web <g class="gr_ gr_36 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="36" data-gr-id="36">api</g> application. It is working on my development machine which has SQLExpress. When I use express, it creates <g class="gr_ gr_173 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="173" data-gr-id="173">MDF</g> file. I wonder how I can migrate from express to prod which has SQL Server 2012?

    Best Regards.

    Thursday, September 12, 2019 8:38 AM

Answers

  • User-821857111 posted

    Usually, the steps are

    1. Create a full backup of the existing database (will generate a .bak file)
    2. Copy the bak file to the target server (FTP, XCOPY, depends on the location of the target server)
    3. Create a new database on the target server
    4. Restore the new (empty) database from the .bak file, ensuring that the WITH REPLACE option is ticked on the Options tab

    It makes no difference that the original database was created on SQL Express. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 12, 2019 8:49 AM
  • User753101303 posted

    If using 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 12, 2019 12:28 PM

All replies

  • User-821857111 posted

    Usually, the steps are

    1. Create a full backup of the existing database (will generate a .bak file)
    2. Copy the bak file to the target server (FTP, XCOPY, depends on the location of the target server)
    3. Create a new database on the target server
    4. Restore the new (empty) database from the .bak file, ensuring that the WITH REPLACE option is ticked on the Options tab

    It makes no difference that the original database was created on SQL Express. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 12, 2019 8:49 AM
  • User-1104215994 posted

    I should NOT add the <g class="gr_ gr_4 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="4" data-gr-id="4">mdf</g> file in the production connection string <g class="gr_ gr_5 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="5" data-gr-id="5">right</g>?

    Thursday, September 12, 2019 10:47 AM
  • User-821857111 posted

    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:

    https://www.connectionstrings.com/sqlconnection/

    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.

    Thursday, September 12, 2019 10:53 AM
  • User-1104215994 posted

    Thank you, I will try and let you know.

    Thursday, September 12, 2019 10:58 AM
  • User753101303 posted

    Hi,

    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...

    Thursday, September 12, 2019 11:54 AM
  • User-1104215994 posted

    I generated the script (tasks-> generate scripts) from MSSMS and if I create those on the production server is OK right?

    Thursday, September 12, 2019 12:16 PM
  • User753101303 posted

    If using 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 12, 2019 12:28 PM
  • User-1104215994 posted

    If <g class="gr_ gr_21 gr-alert gr_tiny gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="21" data-gr-id="21">I</g> backup, can I exclude data? I just want the tables.

    Thursday, September 12, 2019 12:38 PM
  • User753101303 posted

    Ah ok, should be fine anyway. Just do that...

    Thursday, September 12, 2019 12:41 PM
  • User77042963 posted

    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.

    Monday, September 16, 2019 5:20 PM