-
Title
How to move MessageStats Database from SQL express to SQL 2005 -
Description
Instructions for moving MessageStats Database from SQL express to SQL 2005
-
Resolution
Backup the MessageStats database, and restore it from a backup to SQL server 2005:
1. Start SQL Server Management Studio on the destination machine and connect to SQL Server.
2. Expand the database node
3.Right click on the MessageStats database select tasks | Back up
4. Ensure that the backup type is full and the database is messagestats and click OK
5. Copy over the .bak file from the SQL Express Server to the SQL 2005 under \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
Restore the MessageStats database from a backup to SQL 2005:
1. Start SQL Server Management Studio on the destination machine and connect to SQL Server.
2. Right-click on the Database node and select New Database, calling it MessageStats and click Ok to create
3. Right-click on the Databases node and select Restore Database...
4. Select MessageStats in the To database: field under Destination for restore.
5. Copy over the .bak file from the SQL 2000 Server to the SQL 2005 under \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
6. Under the Source for restore, select From device, click the ellipsis button (...), click the Add button, located the .bak file which was copied over from the SQL 2000 Server, select the .bak file and click Ok twice.
7. Under Select the backup sets to restore: put a check mark in the Restore Box beside the restore.
7. Select the Options on the left of the window under Select a page and put a check mark in the box for Overwrite the existing database.
6. Click OK from the Restore database window to restore the database.
Permissions:
Security Groups are needed to assign rights to the database. Either Local or Global Groups can be used or new ones can be created called MessageStats Admin and MessageStats Web. If there are no groups configured for MessageStats, please follow the instructions below for either creating new groups or using existing groups.
1. Launch Computer Management and expand System Tools | Local Users and Groups| Groups to create Local or launch Active Directory Users and Computers to create Global.
2. Create two groups called MessageStats Admin (for accounts performing a gathering) and MessageStats Web (for accounts running reports). Already created groups can be used if necessary for example, Users (for Reports access) and Administrators (for gatherings access).
3. Once the groups are created, launch SQL Enterprise Manager and expand Security | Logins and create a login for ServerName\MessageStats Admin and ServerName\MessageStats Web (or already created groups that have been selected).
4. When creating the MessageStats Admin login, set the following for database access: public, ddladmin and MessageStats_Admin.
5. When creating the MessageStats Web login, set the following for database access: public and MessageStats_Web.
IIS Security:
To ensure that security is properly setup on the IIS Server, open Regedit and edit the HKLM\Software\Quest Software\MessageStats\Client DB and change the SQLConnect value to reflect the new SQL Server Computer Name. If you are using the .UDL file, please open it on the IIS Server where the SQLConnect value indicates and change the SQL Server Computer Name to the new SQL 2005 Server.
Switching the Console to use new SQL 2005 Database:
When launching the Console, you will have to go into the Properties of the Scheduler Server which is located under the MessageStats node by right clicking. Once in the Properties on the Database Tab under the Locate the Database on the following SQL Server and click the ellipsis button (...) and enter in the name of the SQL 2005 Server. Test the connection and click Ok.