Upgrade Guide › Upgrading Databases › Database Upgrade Procedure › SQL Server: Post-Upgrade Table Change › Run the Modify Table Script

Run the Modify Table Script

Before updating the Wgn3EventParticipant table, it is useful to understand the various steps in the update procedure.

When you are ready to update Wgn3EventParticipant:

1. Run one of the following commands

   Run the command as a user with the sysadmin role (for example, sa). This must be the same user that you used to run the pre-flight check (see step 1c in section 3, Run the Pre-Flight check):

   • This command performs the entire table update as a single procedure:

     EXEC wgn_post_upgrade '<dir>'
     

   • This command runs part one of the table update, stopping at the point where all participant records have been copied to a file and Wgn3EventParticipant is ready to be truncated:

     EXEC wgn_post_upgrade_1 '<dir>'
     

   Where <dir> is the target directory where the participant records will be copied to. In both cases, this must be the same folder that you specified in the pre‑flight check script (step 1 in Run the pre-flight check).

   In technical terms, the script now performs the following tasks.

2. Preparing the database

   The script now takes preparatory steps to restrict access to the table and to enable the participant address columns to be redefined. In particular:

   • Access to the database is revoked for the CA DLP primary user.
   • The database is switched from ‘multi user’ to ‘single user’ mode.
   • Relevant foreign key constraints and indexes on the Wgn3EventParticipant table are dropped or disabled.
3. Copying participant records to the file system
   1. First, the script changes the database from ‘single user’ to ‘restricted user’ mode. This enables the bcp utility to connect to the database.
   2. Next, it runs a bcp command to copy data out of the table to the bcp_pf_ep_data.txt file in the target directory. Progress is recorded in the bcp_ep_out.log log file.
4. If you ran the ‘part one’ script in step 1, that script terminates at this point, allowing you to confirm that data has been copied out successfully and to perform other administrative tasks. For example, you may want to back up bcp_pf_ep_data.txt.

   You must now run the ‘part two’ script, using the same user that you used to run the ‘part one’ script:

   EXEC wgn_post_upgrade_2 '<dir>'
   

   Where <dir> is the same directory that you specified in step 1.

5. Truncating the table

   This is a critical step. The script now truncates the Wgn3EventParticipant table, leaving it empty.

6. Setting the AddressUID columns to NOT NULL

   The script now makes the participant address columns mandatory.

7. Copying participant records back into the table
   1. First, the script sets the database recovery model to BULK_LOGGED mode. This eliminates the need to write Before and After images of each record to a log file during the copy operation.
   2. It then runs a bcp command to copy participant records from bcp_pf_ep_data.txt back into the Wgn3EventParticipant table. Progress is recorded in the bcp_ep_in.log log file.
   3. It verifies that the copy operation was successful, checking that the number of table rows copied out equals the number of rows copied in.
8. Reversing the previous preparatory steps

   Finally, the script reverses the earlier preparatory steps (see step 2). Specifically, it:

   • Sets the database from ‘restricted user’ mode back to ‘single user’ mode and then rebuilds any dropped indexes and re‑enables any dropped foreign key constraints.
   • Sets the database from ‘single user’ mode back to ‘multi user’ mode.
   • Resets recovery model back to whatever it was before (FULL or SIMPLE).
   • Restores the ability of the CA DLP primary user to connect to the database.

More information:

Run the Pre-Flight Check