Service Desk Practitioners Forum
cancel

SD5 backup and restore

Highlighted
HPOPV+
Honored Contributor.

SD5 backup and restore

Hi,

i have SD5 installed on a windows2003 server.
SD5 is working fine. I have backed up the Sql db on this server, I am trying to restor it to a test server, the restor is successful but now i cant connect to the SD console
Does anyone knows what isthe problem ?

Thx
2 REPLIES
Rytis Damalakas
Super Contributor.

Re: SD5 backup and restore

Hi,
Its because you have to change database owner manualy. Check for example stored procedures - your user won't have right to execute it. HP provided script for changing database owner. Another way to move database is to detach/attach database - when you attach database sql will ask to choose database owner

com.hp.ov.obs.impl.util.DeveloperException: JDBC error 208: [PC05-05]Invalid object name 'ifc_modules'., SQL state: S0002 for query: select mod_dbversion, mod_name, mod_acronym from ifc_modules where This is caused by the fact that Service Desk 5.0 no longer uses the dbo account as the owner of the database objects. It uses the account of a specified user.

As this user does not exist on the target system (the same name is not the same user OID wise) you need to change the ownership of the database objects to an existing user on the target system (for example dbo).

You can achieve this in the following way. After you did the restore, you go into the query analyzer of SQL server and connect to the database as dbo.

Then, run the following code:

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+ ltrim(u.name) + '.' + ltrim(s.name) + '''''' + ', @newowner = dbo'')'
FROM sysobjects s , sysusers u
WHERE s.uid = u.uid
AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND s.name not like 'INFORMATION%'
order by s.name

The result should look like this:
EXEC('sp_changeobjectowner @objname = ''sd50.cdm_addressdomains'', @newowner = dbo')
EXEC('sp_changeobjectowner @objname = ''sd50.cdm_bussinesslocationperorg'', @newowner = dbo')
EXEC('sp_changeobjectowner @objname = ''sd50.cdm_ci_orgs'', @newowner = dbo')
...

Then, copy and paste the results into the query analyzer and run these. After doing this, the objects are owned by dbo and any user with (default) access to the database should now be able to connect.

Now, restart Service Desk 5.0 via ovc -restart and everything should work.

Rytis
HPOPV+
Honored Contributor.

Re: SD5 backup and restore

Thx for your reply,
the first code was successful but when i run the resul EXEC(... i get some warning and errors (please find attched some of the error i get)
Is this normal ? if no what is the fix
thanks