locked
Migrating a database from SQL Express to Azure SQL Server RRS feed

  • Question

  • This has been eluding me for months. I've tried creating a bacpac file and importing and got errors. I tried using the Microsoft Data Migration Assistant but it doesn't work with SQL Express

    Can anyone help me on the correct process to migrate a database from Express 2017 into an Azure SQL Database?

    Thanks in advance


    Debbie

    Thursday, January 9, 2020 2:04 PM

Answers

  • Perfect. I created the bacpac file and added it as S3 and it worked. Then I let Azure Scale it down to an S2. So all sorted

    Debbie

    • Marked as answer by Debbie Edwards Friday, January 10, 2020 11:45 AM
    Friday, January 10, 2020 11:45 AM

All replies

  • Hi 

    Have you followed those steps?

    https://developersource.wordpress.com/2017/05/04/import-bak-file-to-azure-sql-database/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 9, 2020 2:19 PM
  • Yes, this was the first one i tried and Im getting errors every time I try and do it. I can do it again, just to capture the errors Im getting but it hasn't worked for me yet

    I get an error on Importing package schema and data into database

    Could not import package.
    Warning SQL0: A project which specifies SQL Server 2017 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.
    Error SQL72014: .Net SqlClient Data Provider: Msg 40536, Level 16, State 32, Line 4 'COLUMNSTORE' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.
    Error SQL72045: Script execution error.  The executed script:
    CREATE CLUSTERED INDEX [IndFactResellerSalesXL_CCI]
        ON [dbo].[FactResellerSalesXL_CCI]([ProductKey]);

    CREATE CLUSTERED COLUMNSTORE INDEX [IndFactResellerSalesXL_CCI]
        ON [dbo].[FactResellerSalesXL_CCI] WITH (DROP_EXISTING = ON);

    And an Updating database error which is the same as that above


    It looks like it may work if you move it to S3 (I was moving to S2) but S3 is twice the price of S2 and this database isn't used that often. Id rather move it to a cheaper store. Would it be possible to move to S3 and then get Azure to change it to S2?

    Any ideas on how I get round this. can I change the script in any way before exporting to the bacpac?  Or is this hiding lots of other incompatibility errors?


    Debbie



    Friday, January 10, 2020 9:08 AM
  • Yes, this was the first one i tried and Im getting errors every time I try and do it. I can do it again, just to capture the errors Im getting but it hasn.t worked for me yet


    Please share the error message or screnshot

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, January 10, 2020 9:56 AM
  • I did in the post above

    Debbie

    Friday, January 10, 2020 11:31 AM
  • Perfect. I created the bacpac file and added it as S3 and it worked. Then I let Azure Scale it down to an S2. So all sorted

    Debbie

    • Marked as answer by Debbie Edwards Friday, January 10, 2020 11:45 AM
    Friday, January 10, 2020 11:45 AM