Destructive testing

Database movement operations are a suite of self-service actions that can be used as part of data application lifecycle management (DataALM). In some situations, destructive testing must be done on an environment. In this context, destructive testing means that the environment is rendered no longer useful for continued testing. Destructive testing is typical in an implementation lifecycle during Conference Room Pilots. This tutorial shows how to use database movement operations to facilitate destructive testing.

In this tutorial, you will learn two approaches:

• Use a database backup asset.
• Use point-in-time restore.

As an example of this scenario, a customer wants to do a Conference Room Pilot and wants to start with an environment that has no transactions (that is, no sales orders or purchase orders). The customer will be traveling from physical warehouse to physical warehouse throughout the geographic region to do the same pilot, and wants the environment to be "reset" before each pilot is done.

Prerequisites

To complete this tutorial, you must have a standard user acceptance testing (UAT) environment deployed in your project.

Using a database backup

If you've prepared a database backup (.bacpac) file that is already at the starting point for the test, the easiest approach is to upload the backup file to the Database backup section in your LCS project's Asset Library. It can then be imported to your target environment as described here.

To import a database that is prepared from a developer environment to a standard user acceptance test (UAT), or a database previously exported from a UAT environment, follow the steps outlined below:

1. Go to your target sandbox Environment Details page, and select the Maintain > Move database menu option.
2. Select Import database and choose your source database backup (.bacpac format) file from the Asset Library.
3. Note the warnings. Review the list of data elements that are cleaned up from the backup file.
4. The import operation will begin immediately.

Note

All users except the Admin user and other internal service user accounts will be unavailable after import. Therefore, the Admin user can delete or obfuscate data before other users are allowed back into the system.

To import a database to a developer environment after you've downloaded a database backup (.bacpac) file, you can begin the manual import operation on your Tier 1 environment. When you import the database, we recommend that you follow these guidelines:

• Keep a copy of the existing AxDB database, so that you can revert to it later if needed.
• Import the new database under a new name, such as AxDB_fromProd.

To ensure the best performance, copy the *.bacpac file to the local computer that you're importing from. Download sqlpackage .NET Core for Windows from Get sqlpackage .NET Core for Windows. Open a Command Prompt window, and run the following commands from the sqlpackage .NET Core folder.

SqlPackage.exe /a:import /sf:D:\Exportedbacpac\my.bacpac /tsn:localhost /tdn:<target database name> /p:CommandTimeout=1200

Here is an explanation of the parameters:

• tsn (target server name) – The name of the Microsoft SQL Server instance to import into.
• tdn (target database name) – The name of the database to import into. The database should not already exist.
• sf (source file) – The path and name of the file to import from.

Note

During import, the user name and password aren't required. By default, SQL Server uses Microsoft Windows authentication for the user who is currently signed in.

For information about how to complete the manual import operations into a Tier 1 environment, see Import the database.

Database backup pros and cons

The advantage of using backup file assets is that you can keep importing the same file to get back to the starting point for the test.

The disadvantage is that if many configurations (for example, batch jobs) must be set after the import is completed but before users can begin, more effort will be required before each destructive testing session.

Using point-in-time restore

If you didn't start with a database backup (.bacpac) file but instead have the UAT environment in a known good state, you can just record the date and time in your time zone. You can then begin the destructive testing. Then, when the testing is completed, you can restore the environment to the previous time by using the following steps.

To restore the database of a standard user acceptance test (UAT) environment to a previous point-in-time, follow the steps outlined below:

1. Go to your target sandbox Environment Details page, and select the Maintain > Move database menu option.
2. Select the Point-in-time restore option and choose a point-in-time.
3. Note the warnings. Review the list of data elements that are not copied over from the previous point-in-time.
4. The restore operation will begin immediately.

Important

Restoring the database in production to a previous point-in-time is not a common lifecycle operation and could result in the following issues:

• Large downtime for the production environment. Point-in-time restore (PITR) may take multiple hours, depending on the database size.
• SQL data loss. SQL data loss would depend on how far back the PITR request is made.
• Breaking of the SQL database chain of available restore points.

To restore the database of a production environment to a previous point-in-time, follow the steps outlined below:

1. Go to your target production Environment Details page, and select the Maintain > Move database menu option.
2. Select the Point-in-time restore option and choose a point-in-time.
3. Note the warnings. Review the list of data elements that are not copied over from the previous point-in-time.
4. The restore operation will begin immediately.

Point-in-time restore pros and cons

The advantage of using point-in-time-restore is that you can avoid dealing with database backup (.bacpac) files and can reduce the time between destructive testing sessions.

The disadvantage is that, because of current limitations of point-in-time restore, you must record a new restore date and time in your time zone after the restore is completed. Because point-in-time restore always creates a new database, the original date and time that were used won't be available as a restore point on the new database.