How to move Demand Planner database to another server

Sometimes you need to move your existing Demand Planner database to another server. If you do this just using backup/restore, you will run into the issue while synchronizing the Demand Planner. The error you get is:

Could not find ‘server name’ in sys.sysservers

This is because Demand Planner uses fully qualified object names in some stored procedures and functions, what results in original server name to be used.

Follow these steps to change the server name in existing functions and stored procedures:

1. Open SQL Server Management Studio and expand Databases

2. Right-click the Demand Planner database and select Tasks –> Generate Scripts

3. Click Next on the welcome screen

4. Select the Demand Planner database on Select Database screen and click Next.
Do not check the “Script all objects in the selected database” check box!

5. On the Choose Screen Options screen change value for Script Drop to True and click Next

6. On the Select Object Types screen select Stored procedures, Functions and Views and click Next

7. On the Choose Stored Procedures screen click Select All and then Next. Repeat this also to select all functions and views

8. On the Output Option screen keep the default selection and click Finish

9. On the summary page click Finish

10. In the query window press CTRL+H to open Find and replace window and type name of the original server into Find what field and name of the new server into Replace with field and click Replace All.

11. Press F5 to execute the script when all occurrences are replaced.

Now you should be able to synchronize Demand Planner.

Martin F