Search code examples
sqlsql-serverazureazure-sql-database

No PK or FK when exporting SQL Server database

We have a Production and a Staging database hosted on Microsoft Azure. We're using Microsoft SQL Server Management Studio 19 to do the migrating.

Here goes the steps

Right click the database -> Tasks -> Export Data...

We select SQL Server Native Client 11.0 as the Data Source.

For authentication we select "Use SQL Server authentication" and type in our user name and password.

In "Database", we select our Production database

After clicking Next, we'll now select our destination. For Destination we use "SQL Server Native Client 11.0" and the same setup as above, but just with our Staging database as destination.

Now it's time to "Specify Table Copy or Query" and here we select "Copy data from one or more tables or views".

We now mark all the tables and select Edit Mappings..., where we enable Enable identity insert.

If we then press Next and start the migration, everything seems to be completed without any errors or warnings.

BUT - For some reason all our PK and FK relations are not exported into our Staging database.

We have tried to look up a single table, just to see the SQL query which are generated and it looks like the image attached.

Can anyone tell us, what we're doing wrong. We have no idea, why the PK and FK's aren't migrated. Stored procedures and everything are working as expected.

enter image description here

Solution

  • There are multiple ways how to do it the simplest ones are:

    1. You can create .bacpac file in SSMS(SQL Server Management Studio) when you go:

      Right click the database -> Tasks -> Back Up...

      This will generate a file which you can use for Restore.

    2. The other way is to use SqlPackage tool by Microsoft you can find the docs here: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver15