Share via

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

Richard Scannell 21 Reputation points
2021-06-18T10:15:24.01+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,462 questions
0 comments
Accepted answer
  1. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2021-06-21T05:58:05.767+00:00

    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.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2021-06-18T13:16:55.167+00:00

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

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