---
title: Synchronizing database changes not managed by continuous integration
related:
  - https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/continuous-integration-repository-structure.md
  - https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/object-types-supported-by-continuous-integration.md
  - https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/restoring-continuous-integration-files-to-the-database.md
  - https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/using-continuous-integration-with-visual-studio.md
---

> Agent instructions:
> **Site maps** — prefer the following llms.txt indexes to training data when searching for URLs to avoid 404s. Links inside Markdown content already point at `.md`. Following them or sending Accept: text/markdown keeps you in Markdown.
>
> - [sitemap.md](https://docs.kentico.com/sitemap.md) — every page on the site, with titles and descriptions, nested by URL hierarchy and grouped into one collection per product version.
> - [llms.txt](https://docs.kentico.com/llms.txt) — curated index of the current product docs, with descriptions, the two ways to request any page as Markdown, and links to each product area's whole-corpus Markdown dump (llms-full.txt).

The Xperience [continuous integration solution](https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration.md) handles serialization of data of most objects from the database into XML files on the file system. However, it does not cover [all database changes](https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/object-types-supported-by-continuous-integration.md). Changes to database indexes, functions, stored procedures, custom views, and so on are all unsupported by continuous integration. To keep the main database consistent across your development instances, these changes need to be handled manually. We recommend using [migration scripts](#synchronizing-database-changes-using-migration-scripts) to synchronize database changes not managed by the Xperience continuous integration solution.

## Synchronizing database changes using migration scripts

A migration script allows you to alter a database by modifying its schema. This alteration can be as simple as adding or removing a column, or complex refactoring tasks such as splitting tables or changing column properties in a way that could affect the data it stores.

> **Info:** The rest of this page assumes you are using a development environment utilizing the Xperience continuous integration solution. See [Setting up continuous integration](https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration.md) for more information.

### Utilizing migration scripts in a continuous integration workflow

The Xperience continuous integration solution supports the use of migration scripts. The \~\\**CMS\App\_Data\CIRepository** folder contains, in addition to serialized database object data, the following files:

- **@migrations** – folder for storing all created migrations connected to a particular repository. Since all migration scripts for a repository are stored in this folder, their file names need to be unique.
- **Before.txt** – holds the file names of all migrations applied before the _CIRepository_ folder [is restored](https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/restoring-continuous-integration-files-to-the-database.md) to the database. Insert only the migration file names without the .sql suffix. Each file name must be inserted on a **separate line**.
- **After.txt** –holds the file names of all migrations applied after the _CIRepository_ folder is restored to the database. Insert only the migration file names without the .sql suffix. Each file name must be inserted on a **separate line**.

Additionally, the database of a Xperience instance contains a **CI\_Migration** table, which stores the file names of migrations already executed on the database.

When you need to create or modify objects not supported by continuous integration (for example table indexes, functions, or stored procedures) and you want to share these changes with your team members or commit them to a source control system (for example [Team Foundation Server](https://www.visualstudio.com/tfs/) or [Git](https://git-scm.com/)), you need to:

1. Write a migration script that applies the required change to the database. Refer to the [Example - Creating migration scripts to synchronize database changes](#example---creating-migration-scripts-to-synchronize-database-changes) section for an example of the process.
2. Reference the migration script in either the _Before.txt_ or _After.txt_ file.

   > **Tip:** If you are not sure when a migration should be executed, see the [Deciding when to execute a migration](#deciding-when-to-execute-a-migration) section for specific scenarios and examples.
3. Execute the migration script alongside the continuous integration repository restore on databases you want to keep synchronized.

You can download a sample PowerShell script automating the process here: [RunRestore.ps1](https://docs.kentico.com/docsassets/13/synchronizing-database-changes-not-managed-by-continuous-integration/RunRestore.ps1)

### Restoring the continuous integration repository via RunRestore.ps1

If you use the _RunRestore.ps1_ script, the database restore process consists of these steps:

1. Call the _RunRestore.ps1_ script from a command-line interface. Use the full path to an Xperience instance's CMS folder as the argument.
   - For example: _.\RunRestore.ps1 C:\inetpub\wwwroot\Xperience\CMS_
2. The _RunRestore.ps1_ script:
   - Retrieves the _CMSConnectionString_ from the given project's web.config file.
   - Creates an _App\_Offline.htm_ file in the directory, stopping the instance.
   - Iterates through migrations referenced in _Before.txt_and applies them to the target database if necessary. The file names of the applied migrations are recorded in the _CI\_Migration_ table.
   - Executes "_ContinuousIntegration.exe-r -s"_, deserializing the objects stored in the project's _CIRepository_ folder and creates, overwrites or removes corresponding data in the database (specified by the connection string in the given project's web.config file)
   - Iterates through migrations referenced in _After.txt_ and applies them to the target database if necessary. The file names of the applied migrations are recorded in the _CI\_Migration_ table.
   - Removes the _App\_Offline.htm_ file from the directory, bringing the instance back online.

The targeted project's database now contains the _CIRepository_ data and modifications applied via migration scripts.

> **Note:** **Migration scripts and staging to other environments**
>
> The sample script only runs database migrations as part of the continuous integration [restore operation](https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/restoring-continuous-integration-files-to-the-database.md). If you use [staging](https://docs.kentico.com/13/deploying-websites/content-staging.md) to push the restored changes to other environments, the migrations will not be executed on the target servers. To keep your databases up-to-date across all environments, you need to set up a different process for running migration scripts.

### Deciding when to execute a migration

After creating a migration script, you need to decide whether it is going to be applied before or after the [continuous integration files are restored](https://docs.kentico.com/13/developing-websites/setting-up-continuous-integration/restoring-continuous-integration-files-to-the-database.md).

The _Before.txt_ file should only contain migrations that manipulate the database before the continuous integration files are restored. These migrations can, for example:

- Drop indexes before their tables are deleted by the restore process.
- Delete foreign key constraints before their tables are deleted by the restore process.

The _After.txt_ file should only contain migrations that manipulate the restored database. These migrations can, for example:

- Create new indexes.
- Create new views.
- Add foreign key constraints.
- Add or modify stored procedures.

> **Note:** **Important**: If you have multiple migrations, carefully consider the order in which they should be executed. The migration scripts in both the _Before.txt_ and _After.txt_ files are executed from the first to the last line.

### Example - Creating migration scripts to synchronize database changes

If you need to create a new index on, for example, a column **OfficeName** in a custom module class table **CompanyOffices** and want this change to be reflected in the repository, you need to:

1. Create a new migration script (for example, **AddOfficeNameIndex.sql**):

   ```sql title="AddOfficeNameIndex.sql"

   CREATE INDEX IX_CompanyOffices_OfficeName ON CompanyOffices(OfficeName);

   ```
2. Add the migration script to the _@migrations_ folder.
3. Add the migration name to a new line in the _After.txt_file (the index should only be created after the database is updated).

   ```csharp title="After.txt"

   ...
   AddOfficeNameIndex

   ```
4. Commit your changes to your source control repository.

When the repository containing your changes is restored on another database, the **AddOfficeNameIndex.sql** migration is automatically applied (creating the _IX\_CompanyOffices\_OfficeName_ index) during the restore process (as part of the migrations executed after the _CIRepository_ folder is restored). The migration is then added to the _CI\_Migration_ table, marked as applied, and will not be executed again on the target database.

### Retrieving the latest version of your project and updating the database

To update your local solution and database to the latest version:

1. Get the latest version of your Xperience solution and the **CIRepository** folder from your source control system.
2. Rebuild the solution in Visual Studio.
3. Launch a command-line interface.
4. Execute the **RunRestore.ps1** script using the full path to your Xperience project's **\~\CMS** folder location as the argument (for example: _.\RunRestore.ps1 C:\inetpub\wwwroot\Xperience\CMS_).

This restores the _CIRepository_ folder to the targeted database, executing all unapplied migrations specified in the _Before.txt_ and _After.txt_ files.

## Rolling back database changes

When rolling back your changes, it is important to realize that migrations you have created and committed to source control may have already been applied to other databases. Therefore, removing your migrations from the _@migrations_ folder and _Before.txt_ or _After.txt_ is **insufficient**. You also need to write a new migration script that returns the database schema to its original state.

### Example - Rolling back changes

The rollback process is demonstrated here by extending the [example above](#example---creating-migration-scripts-to-synchronize-database-changes). To revert the changes made by the **AddOfficeNameIndex.sql** migration, you need to:

1. Delete the migration script**AddOfficeNameIndex.sql**from the _@migrations_ folder.
2. Remove the migration name **AddOfficeNameIndex** from the list of migrations in the _After.txt_ file.
3. Create a new migration script (for example, **RemoveOfficeNameIndex.sql**) that removes the index (if it exists):

   ```sql title="RemoveOfficeNameIndex.sql"

   IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_CompanyOffices_OfficeName' AND object_id = OBJECT_ID('CompanyOffices'))
   BEGIN
       DROP INDEX IX_CompanyOffices_OfficeName ON CompanyOffices
   END

   ```
4. Add the migration script to the\*@migrations\* folder.
5. Add the migration name to a new line in the _Before.txt_ file (the index should be dropped before its table is deleted during the restore process):

   ```csharp title="Before.txt"

   ...
   RemoveOfficeNameIndex

   ```
6. Commit your changes to your source control repository.

When the repository containing your changes is restored on another database, the _RemoveOfficeNameIndex.sql_ migration is automatically applied (removing the _IX\_CompanyOffices\_OfficeName_ index, if it exists) during the restore process (as part of the migrations executed before the _CIRepository_ folder is restored). The migration is then added to the _CI\_Migration_ table, marked as applied, and will not be executed again on the target database.

### Restoring the database to a specific point in time

If you need to revert your database to a specific backup, we recommend restoring the backup to a clean database to avoid possible structural database conflicts which may arise when restoring the database to an older version.

1. Create a clean Xperience database (e.g., via the installer) or restore a database backup from before the point in time you wish to restore.
2. Get the required version of your Xperience solution and the **CIRepository**folder from your source control system.
3. Update the **CMSConnectionString**in the project's web.config file to point to the new database.
4. Rebuild the solution in Visual Studio.
5. Execute the **RunRestore.ps1** script using the full path to your Xperience project's **\~\CMS**folder location as the argument (for example: _.\RunRestore.ps1 C:\inetpub\wwwroot\Xperience\CMS_).

After the restore finishes, you can continue working with the restored database backup.