question

RichardScannell-0642 avatar image
0 Votes"
RichardScannell-0642 asked RichardScannell-0642 commented

How to upgrade design of SQL Server tables / columns & retain the data via generated script or Wizard

Is there a way to apply a new design template from one SQL Server Database to another SQL Server Database either through a wizard or automatically generated scripts, and keep the existing data ( as long as the tables / columns are still present) in the target database. The scenarios for this are :

1) Upgrade to Production Database after UAT / Dev work has been tested / accepted.
2) Ensure that your Dev / UAT database, containing test personal data ( "Joseph K Testing", "Darth Solo" etc ) is an accurate representation of the LIVE schema...

sql-server-generalwindows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi RichardScannell-0642,

Welcome to Microsoft Q&A.

We can export the schema and the data of a database to a BACPAC file, and then import it into another SQL Server database. We can right-click the database name->Tasks->Export Data-tier Application... in SSMS using the wizard to export BACPAC file for database. Then go to another SQL Server, right-click on Databases -> Import Data-tier Application to launch the wizard to import BACPAC file.

Please refer to Data-tier Applications and this article which might help.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered RichardScannell-0642 commented

You should use SSDT for your development and SQLPackage.exe for deployment. It will automatically handle schema changes and retain the values.

https://docs.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-ver15

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I like to add that SSDT is one option. There are several options on the market. One that comes to mind is Red Gate's SQL Compare.

0 Votes 0 ·