How to move the Enterprise Reporter database to a new SQL Server (4269823)

These instructions assume that you have the necessary SQL permissions to access SQL Server Management Studio on both the current and new SQL Server to backup and detach the database, as well as the appropriate Windows account permissions to copy the database files from one server to another, and to stop and start services on the Enterprise Reporter server.

1. Determine the current location of the SQL Server databases.  This information can be found in the Configuration Manager of Enterprise Reporter under Information in the System section (see attached image DB_Location.jpg).  There will be either one database: dbReporter
2. Log into the Enterprise Reporter server and stop the services - Quest Enterprise Reporter Access Explorer, Quest Enterprise Reporter Node and Quest Enterprise Reporter Server. (Quest Enterprise Reporter Node service may not be listed on the Enterprise Reporter server.  If it is not listed here you do not need to find and stop remote nodes)
3. Temporarily set the services to Disabled
4. Connect to the SQL Server where the database is currently located using SQL Server Management Studio
5. Backup all available Enterprise Reporter databases
6. Set the database offline
7. Detach the database
8. Copy both the .mdf and .LDF files for the database to the new SQL Server and place in the Data folder of the SQL Server install. e.g. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA for a 64-bit install or C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA for a 32-bit install of Microsoft SQL Server 2008 R2
9. Ensure the service account used for Enterprise Reporter has the appropriate access on the new SQL Server using SQL Server Management Studio
10. Attach the database using SQL Server Management Studio
11. Run the Database Wizard from the Enterprise Reporter All Programs menu on the Enterprise Reporter server
12. Choose Select/Upgrade Database from the options available and click Next
13. Browse to or type in the name of the new SQL Server and make sure the Enterprise Reporter database name is correct, e.g. dbReporter, and click Next
14. Accept the defaults on the following Security Groups screen and click Next
15. Click OK to the Database Maintenance Wizard popup that appears regarding replication
16. Click Finish to initiate the database configuration.  Click OK once this is complete and then click Close once you are returned to the Main Menu of the Database Wizard
17. On the Enterprise Reporter server change the startup type for the services (as altered in step 1) back to Automatic and restart the services
18. Open the Configuration Manager to check that the database move is successful.  Confirm the location of the database by selecting Information in the System section
19. Test the setup by running a discovery so that information is saved to the database in its new location, as well as by running a report to show information from the new discovery collection