Skip to main content

Migrating data to a new database location

This topic describes moving a single node's data to another Microsoft SQL Server database.

If you have a replicated instance and plan on moving data to a different SQL Server version, all nodes must be done simultaneously and to the same version of SQL Server, as replication requires the database versions to be perfectly equal.

Some common use cases for this:

  • The database is on a dated operating system (OS) and must be migrated to a new OS.

  • The database itself is outdated, and data needs to be migrated to a more recent SQL Server.

Example: Manually backing up and restoring database on MSSQL

A database backup on a known good server (source) can be applied or migrated to another (destination) server. Details may differ on your environment.

Requirements

Preparation

Before executing the steps below, schedule instance downtime/maintenance. This is to prevent the destination from being outdated because source data was updated due to being running.

Back up on the source

To back up a database on the source server:

  1. Open Microsoft SQL Server Management Studio.

  2. Log in as database administrator.

  3. Expand <server> > Databases.

  4. Right-click on the instance database.

  5. Select Tasks > Back Up...

  6. Leave BackupType as Full.

  7. Specify the Backup Destination. Ensure the destination has proper read/write access for the SQL services set.

    If not using the default, note the default backup directory path, as you may need to respecify it back to the default stored backs after performing this backup.

  8. Click OK.

  9. Wait for backing up to complete and confirmation window to pop up.

  10. Navigate to the specified backup destination and confirm that the .bak file of your database is there.

Restore on destination

To restore the database on the destination server:

  1. Restore database:

    1. Open Microsoft SQL Sever Management Studio.

    2. Log in as database administrator.

    3. Expand <server>.

    4. Right-click on the instance database to apply the backup.

    5. Select Tasks > Restore Database > Database...

    6. Select Device and click ...

    7. Click Add, specify the .bak file to restore from, and click OK.

    8. Select the Options tab.

    9. Select Overwrite the existing database (WITH REPLACE).

    10. Click OK.

    11. Wait for restore to complete and confirmation window to pop up.

  2. Configure SQL logins:

    1. Expand the database restored.

    2. Expand Security.

    3. Expand Users.

    4. Right-click on the database owner, which typically will have the same name as the database.

    5. Select Delete, and No when asked if you want to delete the schema.

      Warning

      Do not delete the schema.

    6. Click OK.

    7. Wait for the delete to be completed.

    8. Expand Security under <server>.

    9. Expand Logins.

    10. Right-click on the database and select Properties.

    11. Select the User Mapping tab.

    12. Select the restored database.

    13. Click on the elipsis ( ... ) to browse and select the schema.

    14. Select the following Database role membership for: <schema>:

      • db_datareader

      • db_datawriter

      • db_ddladmin

      • db_owner

    15. Click OK.

    16. Confirm database security user is added back.

  3. Repeat for each database being migrated.

Post backup

After executing the steps listed above:

  • Back up the old Database Service (iddb) configuration.

  • Verify access to the new database from the node that will be connecting to it.

  • Use the iddbadm utility to update where the Bravura Security Fabric database is situated. If on a multi-node install, all nodes must be updated.

Troubleshooting database migration

Before running iddbadm, ensure that the new values you're about to set are correct and the database is reachable; see Troubleshooting MS SQL Installation.

After running iddbadm or changing the registry settings that it updates, restart the Database service (iddb) and verify in idmsuite.log that it starts correctly and doesn't throw errors or warnings.

In this section: