Summary
This page describes a process to restore databases and avoid the following problems:
1.Avoiding the dreaded database collation error
2.Ensuring that you have access to the database once it has been restored
Discussion
Process
1. Find the collation order of the database right clicking "Properties" on the database and look for the Collation name value.
2. Check the collation order on the tempdb database to ensure that they are the same
3. If they are different then you can you need to install a new database server instance and set the collation order of this new instance to be the same as the database you wish to restore. I suggest that the name of the new database instance be the name of the collation order, so that you can easily identify it and use it again for future reference.
4. If the database has been installed on a database instance other than the default then you should make the following changes:
a. Modify the registry key
HKLM\Software\ManageSoft Corp\ManageSoft\Reporter\CurrentVersion\DatabaseConnectionString so that Server = <ServerName>\<InstanceName> e.g. DatabaseConnectionString =
b.Update the web.config under <program files>\ManageSoft\Reporter\Web so that the following values are changed:
<add key="ManageSoft.DataAccess.server" value="<serverName>\<InstanceName>"/>
5. Open up SQL Query analyser, select the ManageSoft database and then run the following SQL scripts from <program files>\ManageSoft\Reporter
a.Groups.sql
b.Perms.sql
6.Open up a command prompt and from <program files>\ManageSoft\Reporter run the following:
polmerge -RaV5 > polmerge.log
a. Review the polmerge.log file to ensure that there are no collation or other errors that will stop you from accessing the ManageSoft database.
7. From SQL query analyser run the following query against the ManageSoft database:
a. SELECT * FROM [Group] WHERE GroupCN LIKE 'MGS Admin%'
b. Note the list of GroupIDs that are returned
c. exec RightSetAdminRightsByGroupID <MGS Administrators GroupID>
d. exec GrantCurrentUserFullAccess (for Compliance Manager database only)
8. A similar process may be done for MGS Report Users, but use the stored procedure RightSetReportRightsByGroupID
This will then allow the MGS Administrators security group access to the ManageSoft database. You should now be able to perform normal ManageSoft operations on this database and install other ManageSoft software on top of it.
Comments