KBA-01280: Moving the Spitfire Database to another SQL Server

Purpose & Scope:

You can move the backend data for a Spitfire site from one SQL Server to another. The Spitfire database is not a stand-alone database. It is linked with either your Microsoft Dynamics SL databases or with your Spitfire External Data database. Therefore, when you relocate your Spitfire database to another SQL Server, you need to move the linked database too. Your Spitfire document database and the Dynamics SL databases (external database for non-integrated sites) must be on the same instance of SQL server.

There are two major techniques that can be used to move databases:

1. You can detach the database from the original server and attach the database to the new server.
2. You can restore a backup from the original server on the new server — effectively making a copy on the new server.

In either case, when you attach or restore, you want to keep the names of the databases the same.  The procedure for renaming the database adds steps not documented here.

Procedure:

To move the Spitfire database to a new SQL Server using detach/attach

1. On the current SQL Server:
   • Right-click on your Spitfire Database and choose Properties.  Use the property tabs and make note of the physical file names for the MDF, NDF and LDF files.
   • Detach your Spitfire database.
2. Repeat step one for the linked database(s).
3. Use Windows Explorer to move the detached MDF, LDF, and NDF files to the appropriate locations on the new server.
4. On the new SQL Server, attach the Spitfire database and its linked database(s).
   • The dialog will prompt you for the physical files.
   • Make the owner of the databases ‘sa‘.
5. Continue with instructions to reconfigure the site below.

To move the Spitfire database to a new SQL Server using backup/restore

1. On the current SQL Server, back up your Spitfire database and its linked database(s). Be sure it‘s a Full Backup.
2. WARNING: Unless you take the databases offline or detach them, they are still ‘live‘.  Right click on each database to take them offline.
3. If necessary, copy the backup files created in step 1 to a location accessible to the new server.
4. On the new SQL Server, restore the Spitfire database and its linked database(s).  Pay attention to the physical location of the MDF, LDF (and optional NDF) files. The defaults may not be appropriate on the new server.
5. Continue with instructions to reconfigure the site below.

To Reconfigure the Site

1. If integrated to Microsoft Dynamics SL:
   • Open the Domain table in the Microsoft Dynamics SL table and change the name of the Server (domain.ServerName);
   • Use the Dynamics SL Database Maintenance Tool to ‘Synchronize all Ownership and Security‘;
2. Open the Spitfire Configuration Tool (ICTool.exe).
   1. If you have multiple Spitfire installations, be sure to open the correct Configuration file (File | Open).
   2. On the Server tab, change the name of the SQL Server to the new SQL Server.
   3. On the SQL tab, be sure that the database names are correct and that the green checkmark appears behind each of the databases.
   4. On the Finish tab, click the Update Configuration button. Spitfire will create the Spitfire and SpitfireRO logins with the correct SIDs and update all the views and stored procedures as appropriate.

Additional Comments:

Note: If the name of the Microsoft Dynamics SL Application database changes, be sure to update the Database name in both the Domain (domain.DatabaseName) and Company (company.DatabaseName) tables in the Microsoft Dynamics SL System database. Then use Microsoft Dynamics SL Database Maintenance to update the Views in the Microsoft Dynamics SL databases. Spitfire‘s Configuration Tool (ICTool) reads the Microsoft Dynamics SL views to link the Microsoft Dynamics SL Application database name you entered on the SQL tab to the appropriate Microsoft Dynamics SL System database.  If the views aren‘t updated, the old System database name will appear on the SQL tab. Although you may be able to type over the System database name, Spitfire will continue to check the views and reset the System database to the one in the Microsoft Dynamics views.

Databases and Backups from SQL 2005 cannot be attached or restored on SQL 2000.

See KBA-01010 for information about relocating your Spitfire database files.

See KBA-01680 for standard ICTool update steps.

---

KBA-01280; Last updated: September 20, 2017 at 12:04 pm;
Keywords:  SQL Server;server upgrade;attach database