Friday , July 19 2024

Update Your Database with Upgrade Data-tier Application

Before you read this article, I recommend that you read the article “What is Data-Tier Application” to avoid confusion.

In the article I mentioned above, we moved the schema structure of the database named TestDB to a new instance with Extract / Deploy methods without data. In this article, we will transfer the changes that occur in the schema structure of the source database to the database we transferred to the new instance.

Add a table to the TestDB database on the first instance (I created a table named UpgradeDataTierNewTable). Then, perform the extract operation as in the previous article.

Then, go to the new instance and right click on the database that we transferred with the name TestDBNewDACAfter and click Upgrade Data-tier Application….

In the next screen, select the file we extracted as follows and click next.

The next screen checks the changes. Since we have not made any changes to the table structure, you should see a screen like the one below.

If you select “Rollback on failure” on the next screen, if an error occurs during the upgrade, it will rollback the actions taken. If you do not check this and an error occurs during the upgrade, you may need to restore the database in the new instance. Select Rollback on failure and click Next.

In the Action section on the next screen, we see that the table that we just created in the first instance will be created in the second instance.

Operation:Create, Type: SqlTable, Object: [dbo].[UpgradeDataTierNewTable]

Since I have  created a few more object in the first database you see other objects in the below screenshot.

Click Next, Next and Finish to complete the process. Upgrading your shema structure with DAC may result data loss if a data modification has been made after performing migration with DAC on the new instance. If this risk is not important to you (usually because it is a development process, the data inside is not important.) Upgrade with DAC is easy and fast.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *