Moving the Prism Deploy Channel Database from one SQL Server to Another SQL Server

Summary:

There may be situations that require you to move a Prism Channel database from one SQL server instance to a different one. For example, you may have started out using MSDE (Microsoft Database Engine) for your Prism Deploy Channel database. However, your company plans to install SQL Server 2005 on new hardware, so you’ve decided to move your Prism Channel database to the new SQL Server. This Tech Note makes the assumption that you are not moving your Prism Channel Server to the new hardware, just the database.

Notes:

Method:

1. On the Prism Server machine, launch the Prism Deploy setup program and choose Modify.
   
   
   
2. When you get to the “Select Roles” screen, unselect the Channel Server item and click Next >. It is necessary to uninstall the Channel Server component in order to use a different SQL server. Later on in this process (Step 17, you will reinstall the Channel Server component).
   
   
   
3. On your current SQL Server, back up your existing Channel database(s). Prism Channel databases often use the same name as the Channel name with “_PD” appended to the end. This Tech Note assumes the Channel is named “Production” and the channel database is named “Production_PD”.
4. You can use SQL Server Management Studio, 3^rd party SQL management tools, or MSDE command-line options (osql.exe) to perform the backup. Our screenshots show the steps in Enterprise Manager; the command-line options for MSDE can be seen in the Note at the bottom of this article.
   
   
   
5. Click the Add... button to add a destination for the backup.
   
6. Give the backup a name and click OK. (Make sure that the location and folder structure already exist.)
   
   
   
7. Click OK on the screen from step 5 to start the backup.
8. Move the backup file to your new SQL Server (preferably into the same folder structure). IMPORTANT: If your new SQL Server is running SQL 2005 AND it is not on the same system as the Prism Channel Server, you must first install the 2005 Backward Compatibility components on the Prism Channel server (NOT on the SQL 2005 Server). See the Related Articles section below for a link to a KB article on that subject.)
9. On the new SQL Server, launch your SQL management tool to restore the database.
   
   
   
10. Change the “Restore as database” to the correct name of the database, choose the “From device” radio button, and click the Select Devices… button.
    
    
    
11. Under “Source for restore” chose “From Device” and click the “…” button.
    
    
    
12. Select the backup file that you created in step 8 and click OK.
13. Click through two more OKs.
14. After the restore completes, you will get the message:
    
    
    
15. If you have more than one Channel, repeat steps 4 through 14 for each channel that you wish to move.
16. On the Prism Server machine, relaunch the Prism setup program.
17. Again, choose Modify for the “Select Roles” dialog, but this time select the Channel Server to readd it.
18. When you get to the dialog to select a SQL server, select the new SQL Server that you moved your channel(s) to.
    
    
    
19. After the setup completes, right click on the Prism Channel Manager in the system tray () and choose Connect Channel…
20. Choose the authentication method and click Next ->.
21. The Channel(s) you restored should appear in the drop-down list. Select the channel that you moved and click Next -> and then Finish. Repeat for each channel that you moved.
    
    
    
22. Launch the Prism Console (if the Console is set to load the last channel automatically, you will get a message about a channel not existing, simply click OK).
23. From the Open Channel dialog click the Find… button.
24. Type in “<this>” or the name of the Prism Server and click Next -> to see all channels that have been connected to the Prism Deploy Channel Server.
    
    
    
25. Select that channel that you moved and click Finish.
    
    
    
26. Repeat steps 23 through 25 for each Channel that you moved.
27. If necessary, re-add your license pack files or license keys by opening the Prism Console and selecting File | Channel Properties | Licenses (tab).

NOTE: If you are using osql.exe (provided with all version of SQL) instead of Enterprise Manager, the following steps can be substituted:

Steps 7 through 11 can be substituted with the following commands:

1. osql –S %computername%\sw_deploy_server –E

2.  Within osql type:

a. BACKUP DATABASE Production_PD TO DISK = 'C:\Production_PD.bak'

b. GO

Steps 13 through 18 can be substituted with the following commands:

1. osql –S %computername%\sw_deploy_server –E

2. Within osql type:

a. RESTORE DATABASE Production_PD FROM DISK = 'C:\Production_PD.bak'

b. GO

These steps assume that you are using the default MSDE instance that is created via the Prism Deploy installation. If you manually installed MSDE or SQL Server before installing Prism Deploy, please replace %computername%\sw_deploy_server with the name of the instance that you are using.