How to: Move an Agility Database from one MS SQL Database Server to another

Anirban Bose

December 08, 2020 18:33

If moving the database only

For the Lifecycle application

Important

There are a few important things to know about moving the database to another machine.

When you run the installer and select "Upgrade" then choose a different SQL Server, then the installer will look on that new server for a DB that is the same name of the one that the instance is using already. If there is not one there that has the same name already, the installer will create a new DB, which is something you probably don't want. So there are two processes that you can follow and both will work as long as you do these step by step and do not shortcut anything.

Option 1 (if the DB and Instance are going to keep the same name)

First, make a backup of the database on the original server. Take that .bak file over to the new SQL Server and Restore it*. It is important that you use the same name as the original database name.
Now, run the Agility Installer for the build and edition you are running now.
Select Upgrade.
The next page is where you select the instance that you are moving. Click that instance and then click Next.
Select the authentication mode and click Next.
The next page is the important one. This is where you enter the NEW SQL Server information.
Enter your SQL Server info here and when you click Next it will look for the DB on that server that has the name of the Original database. This is why you MUST name it the same. If you do not, then it will create a new database.
Step through the rest of the install wizard and let it run. Once it finishes, your instance will point to the new database location.

Option 2 (if you are renaming ANY part of the instance, like the URL, DB name, or anything like that)

First, Make a backup of the existing instance's SQL Server database. Keep it in a safe place.
Now, run the Agility Installer.
Select Uninstall and remove that instance. KEEP the database.
Now, restore your DB backup to the new SQL Server location. Name it what you want. We recommend the same name as before but in this option, it does not matter.
Once that DB is restored, run the installer again for the Edition and build that you want to install. Note that this is a good time to Upgrade your instance if you are on an unsupported build.
Select Install a New Instance.
Name the instance the new name, or use the name on the existing instance.
Select the authentication mode and click Next.
The next page is the important one. This is where you enter the NEW SQL Server information.
Enter your SQL Server info here and when you click Next it will ask you for the database. Select "Attach to an existing database" and then choose the database that you restored in the dropdown.
Step through the rest of the install wizard and let it run. Once it finishes, your instance will point to the new database location.

Warning

Do not use SQL Server’s ‘Copy Database…’ or other DTS-based db replication tasks, as they may not accurately replicate all database structures. To ensure the integrity of the database, you must use a full Backup (‘Backup type: Full’) and Restore.