How to Move Your InSQL 7.1 Runtime Database from One Machine to Another

All Tech Notes and KBCD documents and software are provided "as is" without warranty of any kind. See the

Terms of Use for more information.

Topic#: 000951

Created: May 12, 2001

From time to time it is necessary to move an installation of InSQL™ from one machine to

another. This may be the process of converting a test or development system to production,

or simply copying an existing application to another machine for the purpose of redundancy.

For whatever the reason, this Tech Note is intended to guide you through the process.

Note: For the purpose of this Tech Note, the “source” system is referred to as

the Test system and the “destination” system is referred to as the Production

system.

Backing up the Database on the Test System

The following step-by-step procedure describes how the database is backed up on the test

system.

1. Start the MS SQL Server Enterprise Manager (Figure 1).

FIGURE 1: THE SQL SERVER ENTERPRISE MANAGER

2. Expand the tree on the left side of the screen until you get to the Databases\Runtime.

3. Right-click on the Runtime Database and select All Tasks\Backup Database (Figure 2).

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 1 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

FIGURE 2: THE ALL TASKS\BACKUP DATABASE SELECTION

4. When the SQL Server Backup - Runtime dialog box (Figure 3) appears, make a note of

the Destination path for the backup file (default = ...\MSSQL7\BACKUP\Runtime.bak).

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 2 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

FIGURE 3: THE SQL SERVER BACKUP - RUNTIME DIALOG BOX

5. In the Overwrite section, choose the Overwrite existing media radio button and click

OK.

6. Copy the file from the path in step 4 to the Production machine.

Note: You cannot restore a backup from a mapped drive.

This concludes the section on the Test System. The next section will deal with restoring the

backup on the Production system.

Restoring the Database on the Production System

1. Stop InSQL and any other programs that might be accessing the Runtime database on

the Production machine.

2. Start the MS SQL Query Analyzer and paste the following scripts into the Query

window, then execute them.

Note: When logging into the Query Analyzer, be sure to connect to either

"Local" or the machine name.

EXEC sp_dboption 'Runtime', 'single user','true'

GO

EXEC sp_renamedb 'Runtime', 'Runtime_old'

3. Start the MS SQL Server Enterprise Manager.

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 3 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

4. Right-click on the Database folder and select All Tasks\Restore Database (Figure 4).

FIGURE 4: THE ALL TASKS\RESTORE DATABASE SELECTION

􀁻 The Restore database dialog box (Figure 5) appears.

FIGURE 5: THE RESTORE DATABASE DIALOG BOX

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 4 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

5. From the Restore as database: drop-down menu, select "Runtime."

6. In the Restore: section, select the From device radio button.

7. Click on the Select Devices... button in the Parameters section, then click on the

Restore backup set and Database - complete radio buttons.

􀁻 The Choose Restore Devices dialog box (Figure 6) will appear.

FIGURE 6: THE CHOOSE RESTORE DEVICES DIALOG BOX

8. Click on the Add button and then browse to the location where you saved your

database backup from the Test machine.

9. Click the OK button.

􀁻 The Choose Restore Destination dialog box (Figure 7) appears.

FIGURE 7: THE CHOOSE RESTORE DESTINATION DIALOG BOX

10. Click OK on the Choose Restore Destination window.

􀁻 The Choose Restore Devices dialog box (Figure 8) will return.

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 5 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

FIGURE 8: THE CHOOSE RESTORE DEVICES DIALOG BOX

11. Click the OK button.

􀁻 The Restore database window (Figure 9) appears.

FIGURE 9: THE RESTORE DATABASE DIALOG BOX

12. On the Restore database window, select the Options tab.

13. If necessary, modify the path for the Data Device in the "Move to physical file name"

column.

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 6 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

14. Click OK.

􀁻 The restore process should begin.

15. Assuming the restore completes successfully, you should run the following script from

the Query Analyzer to ensure that all of the wwusers/logins are properly connected.

USE Runtime

EXEC sp_change_users_login 'Auto_Fix', 'wwAdmin'

go

EXEC sp_defaultdb 'wwAdmin', 'Runtime'

go

EXEC sp_change_users_login 'Auto_Fix', 'wwUser'

go

EXEC sp_defaultdb 'wwUser', 'Runtime'

go

EXEC sp_change_users_login 'Auto_Fix', 'wwPower'

go

EXEC sp_defaultdb 'wwPower', 'Runtime'

go

EXEC sp_changedbowner wwdbo, true

go

􀁻 For more information on this subject, see Tech Note 204.

At this point, assuming that the computer name is the same on both the Test machine and

the Production machine, you should be able to start InSQL and it should be up and running.

In the event that you have different computer names on the Test and Production systems,

please refer to Tech Note 155; it has all the information you would need to change the

computer name in the Runtime database.

Updating the Node Name and Data Paths

Once the system has been moved, the Runtime configuration settings must be modified to

reflect the node name where the new installation resides. Also, if the drive or path where

the HistoryBlocks are stored has changed from the old node, these configuration settings

must be modified as well.

1. Copy and modify the following Transact-SQL statements for the IndustrialSQL Server

to run on the new node.

􀁻 For more information on the content of the tables that are referenced, see the

on-line IndustrialSQL Server Reference Guide.

a. The ComputerName field in the StorageNode table contains the node

name where the IndustrialSQL Server data is logged.

UPDATE StorageNode

SET ComputerName = 'NewNodeName'

WHERE ComputerName = 'OldNodeName'

b. The ComputerName field in the ServerList table contains the node

name where the list of available I/O Servers is stored.

UPDATE ServerList

SET ComputerName = 'NewNodeName'

WHERE ComputerName = 'OldNodeName'

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 7 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

c. The MachineName field in the InTouchNode table contains the names

of all nodes from which the InTouch™ tagname databases have been

imported. You only need to modify this table if the IndustrialSQL Server

tags were imported from an InTouch application that was local to the

IndustrialSQL Server on the old computer.

UPDATE InTouchNode

SET MachineName = 'NewNodeName'

WHERE MachineName = 'OldNodeName'

2. If all of the I/O sources, that were running locally to the IndustrialSQL Server on the

old node, are still running locally on the new node, run the following query to update

the node name.

UPDATE IOServer

SET ComputerName = 'NewNodeName'

WHERE ComputerName = 'OldNodeName'

OR,

If you changed the I/O source topology when the system was moved, and the

I/O sources are running on different nodes than they were on the old system,

then the node names need to be updated for the I/O sources. (For example, the

I/O sources may have been running on a separate node, but the topology was

changed so that they run locally on the same node as the IndustrialSQL

Server.)

Run the following query:

SELECT * FROM IOServer

Use the results to determine the appropriate node names for the I/O sources.

Then run the following query once the system topic, IOServerKey = 1, and

again for each I/O source whose node has changed. Substitute "x" with the

appropriate IOServerKey number.

UPDATE IOServer

SET ComputerName = 'NewNodeName'

WHERE IOServerKey = x

3. Run the following statements only if the drive or path selected for the IndustrialSQL

Server data logging was changed with the new installation. Before making any

modifications to the StorageLocation paths, run the SELECT statement below and use

Windows Explorer to check for the paths listed.

SELECT * FROM StorageLocation

If necessary, use the statements below to modify any paths that are different

on this node. The drive letter "x" should be replaced with the drive letter that is

appropriate for your installation.

UPDATE StorageLocation

SET path = 'x:\InSQL\DATA\Circular'

WHERE StorageType = 1

UPDATE StorageLocation

SET path = 'x:\InSQL\DATA\Buffer'

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 8 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006

WHERE StorageType = 3

UPDATE StorageLocation

SET path = 'x:\InSQL\DATA\Permanent'

WHERE StorageType = 4

Note: Modify the path for StorageType = 2 only if the IndustrialSQL Server

system was configured to use an alternate drive or node for overflow data.

The existing entry, "rr:\InSQL\Data\Overflow," is an invalid directory, thus

disabling the alternate storage mechanism. For more information about

alternate storage, see the IndustrialSQL Server Administrator’s Guide,

Chapter 1, under the section titled "Data Storage."

S. Sandler

The Tech Note is published occasionally by Wonderware Technical Support. Publisher:

Wonderware Corporation, 100 Technology Drive, Irvine CA 92618. There is also technical

information on our software products at www.wonderware.com/support/mmi, our

WonderFax fax-on-demand system at 949-450-5050, and the Comprehensive Support

Knowledge Base.

For technical support questions, send an e-mail to support@wonderware.com.

back to top

© 2006 Wonderware Corporation. All rights reserved. Wonderware is a registered trademark of the Wonderware

Corporation in the United States of America and/or other countries. All other companies and product names are

trademarks or registered trademarks of their respective holders. Terms of Use.

How to Move Your InSQL 7.1 Runtime Database from One Machine to Another Page 9 of 9

http://www.wonderware.com/Support/MMI/comprehensive/kbcd/html/t000951.htm 29/03/2006