restoring a specefic folder deleted by error

system · May 8, 2012, 9:09pm

Hi,

I have never thaught that this could happen, one of my users deleted by error a folder that contians more thant 50 reports. The problem is that if I make a restore my other users will be impacted (and there are many other users). Do you think it’s possible to make a restore of a certain folder.

Regards,

zizou (BOB member since 2008-07-31)

system · May 8, 2012, 9:45pm

hi

if you have oracle database then i would suggest you to do schema level restore for the particular user. For that you need to send a request to the DBA.

if not please post your environment.

Thanks
Owais Khan
owais@ritnoa.com
www.ritnoa.com

osrootofos (BOB member since 2012-02-10)

system · May 8, 2012, 9:59pm

Thank you for your reply we are on BO XI 3.1 and our database is SQL. Server.

zizou (BOB member since 2008-07-31)

system · May 9, 2012, 1:57am

hi

i came across a solution and would like to share it as it is with the name of the member who wrote the post hope you fine it useful.

"I found a way to restore reports without necessarily having to restore the CMS.

Last week, a user accidentally deleted one of her “Favorites” folders. It had 8 reports in it. The only way we knew of getting these back is to use our Development environment to restore the CMS and FileStore and use it to create a picture of Production from the previous weekend. Since we are in a clustered environment, this would involve restarting and stopping services on both Dev and Prod…a big headache!

This is how we got the reports back:

Discovered the folder and report names that were deleted via the auditor tables. You can also find the reports if the user remembers the name of the folder they deleted.
Restored the Weekend CMS table “CMS_InfoObjects5” to a temporary location.
Ran the following SQL against this temporary table:
select objectid, parentid, ownerid, lastmodifytime, upper(left(objectname,len(objectname)-2)) ObjectName,
typeid, type, si_cuid
from
(
SELECT
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(
cast(objname as varchar(2000)),‘S’,‘v’),‘M’,‘s’),‘A’,‘m’),’)’,‘a’),’+’,‘b’),‘C’,‘n’)
,’-’,‘c’),’/’,‘d’),‘O’,‘t’),‘E’,‘o’),‘1’,‘e’),‘3’,‘f’),‘G’,‘p’),‘5’,‘g’),‘7’,‘h’),‘W’,‘x’)
,‘U’,‘w’),‘Q’,‘u’),‘I’,‘q’),‘9’,‘i’),’:’,‘i’),’;’,‘j’),‘K’,‘r’),’=’,‘k’),’?’,‘l’),’[’,‘y’)
,’]’,‘z’),’!@’,’ ‘),‘B~S’,’&’),’!BO’,’.’),‘B|C"’,’(’),’!B|D’,’)’),‘M|Z’,’-’),‘M}L’,’,’),‘M|N’,’_’)
,‘M}Z’,’:’),’!B{B’,’’’’),’|<','0'),'|>’,‘1’),’|@','2'),'|B’,‘3’),’|D','4'),'|F’,‘5’)
,’|H','6'),'|J’,‘7’),’|L','8'),'|N’,‘9’),’{’,’’),’!’,’’),’"’,’’),’@’,’’)
ObjectName,
case
when TypeID = 262 then ‘Webi Report’
when TypeID = 314 then ‘Deski Report’
when TypeID = 283 then ‘PDF’
when TypeID = 267 then ‘Text’
when TypeID = 323 then ‘Excel’
when TypeID = 266 then ‘Universe’
when TypeID = 278 then ‘Publication’
when TypeID = 299 then ‘Connection’
when TypeID = 19 then ‘User type 19’
when TypeID = 18 then ‘User type 18’
when TypeID = 47 then ‘User type 47’
when TypeID = 48 then ‘User type 48’
when TypeID = 8 then ‘Shortcut’
when TypeID = 1 then ‘Folder’
when TypeID = 20 then ‘Groups’
when TypeID = 13 then ‘Server’
when TypeID = 16 then ‘BO Server’
when TypeID = 21 then ‘Event’
when TypeID = 24 then ‘License Key’
else ‘Other’
end Type,

FROM bo_xi_admin.[CMS_InfoObjects5]
) BORepository
where ObjectName like ‘(Name of Folder)%’

This will give you an objectid for the folder, then you can rerun this sql but replace the where clause with “ParentId = (objectid of folder)”.

This will give you a list of the reports in that folder along with their objectid. This objectid is actually the name of the subfolder containing the .WID file.

Had the FileStore directory restored to a temporary location.
Searched through the Temporary FileStore location for the report object ids (again, the subfolders).
Copied each .wid file to my CPU.
In Infoview, created a temporary report which I called “TempWID”.
Once created, went into CMS Administrator to find path of the “TempWID” .wid file.
This temporary WID file you created will have a name of something like cdz3Ty56poo33390.wid. I renamed this to cdz3Ty56poo33390.wid_bak, copied in the first .WID file that I wanted restored and renamed it to cdz3Ty56poo33390.wid.
Opened up Infoview and opened up the TempWID. It should open up the restored report. I then saved this report off to my favorites. After I used this method to restore the 8 files, I sent them to her Business Objects Inbox.

This worked so well, that we now keep a table of objectnames, ids and parentids so that we won’t have to restore the CMS table in the future. Also, we’re still on R2 so I’m not sure if the above SQL will work for R3.

Lisa Yates
General Cable
Highland Heights, KY
Business Objects Architect"

Thanks
Owais Khan
owais@ritnoa.com
www.ritnoa.com

osrootofos (BOB member since 2012-02-10)

system · January 16, 2014, 1:15pm

Hi all, apparently the character inserted in the SQL set is different for BO 40.

All this (below) must be replace for some other set of Hexadecimal characters:
)),‘S’,‘v’),‘M’,‘s’),‘A’,‘m’),’)’,‘a’),’+’,‘b’),‘C’,‘n’)
,’-’,‘c’),’/’,‘d’),‘O’,‘t’),‘E’,‘o’),‘1’,‘e’),‘3’,‘f’),‘G’,‘p’),‘5’,‘g’),‘7’,‘h’),‘W’,‘x’)
,‘U’,‘w’),‘Q’,‘u’),‘I’,‘q’),‘9’,‘i’),’:’,‘i’),’;’,‘j’),‘K’,‘r’),’=’,‘k’),’?’,‘l’),’[’,‘y’)
,’]’,‘z’),’!@’,’ ‘),‘B~S’,’&’),’!BO’,’.’),‘B|C"’,’(’),’!B|D’,’)’),‘M|Z’,’-’),‘M}L’,’,’),‘M|N’,’_’) ,‘M}Z’,’:’),’!B B’,’’’’),’|<','0'),'|>’,‘1’),’|@','2'),'|B’,‘3’),’|D','4'),'|F’,‘5’)
,’|H','6'),'|J’,‘7’),’|L','8'),'|N’,‘9’),’{’,’’),’!’,’’),’"’,’’),’@’,’’)

Any ideas how tor recover docs in BO 40?

Thanks in advance
Reinaldo

reinaldonunez (BOB member since 2004-10-01)

system · January 16, 2014, 4:35pm

Sometimes people setup what is referred to as a recovery server/system. It’s essentially a separate server with the BOBJ software installed on it. You can then restore your backup from the Prod system to the recovery server and then use a number of different techniques (import wizard, .biar) to move a subset (individual folder) of your content from the recovery server to the Prod server.

clarence (BOB member since 2005-11-18)

system · January 16, 2014, 6:45pm

Thanks Clarence, we could do that or try to recover the folder with BIAR file in the same server, but instead choosing not replacing anything in the target.

The issue is backup were set weekly for the development folder and the deletion did happen after the last backup was taken.

Again, thanks for your reply, though

reinaldonunez (BOB member since 2004-10-01)

system · January 13, 2015, 6:45pm

Thanks to the above query from Lisa Yates; very useful! I found in my version (4.0 SP5) that the first character translation worked better than the newer one (the only difference were apostrophes), but some of the object types were no longer valid. I modified the query slightly and thought I would post it here. I don’t guarantee the object types are correct though; if anyone has any improvements please post. I figured out more objects types but there are so many I ended up ignoring the ones I didn’t find useful.

SELECT 
	Type, 
	UPPER(LEFT(ObjectName,LEN(ObjectName)-2)) AS ObjectName, 
	ObjectID, 
	ParentID, 
	CAST(SUBSTRING(LastModified,1,4)+'-'+SUBSTRING(LastModified,6,2)+'-'+SUBSTRING(LastModified,9,5)+':'+
		SUBSTRING(LastModified,15,2)+':'+SUBSTRING(LastModified,18,2)+'.'+SUBSTRING(LastModified,21,3) AS DATETIME) 
		AS LastModified
FROM ( 
 SELECT 
	ObjectID,
	ParentID,
	case 
		when TypeID = 1 then 'Folder' 
		when TypeID = 2 then 'Crystal Report' 
		when TypeID = 14 then 'FRS'
		when TypeID = 16 then 'Server'
		when TypeID = 18 then 'Personal Folder'
		when TypeID = 19 then 'User' -- what are 47,48?
		when TypeID = 20 then 'Group' 
		when TypeID = 57 then 'Access Level'
		when TypeID = 322 then 'Webi Report'
		when TypeID = 346 then 'Universe'
		else 'Other' 
	end AS Type, 
	replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( 
	replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( 
	replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( 
	replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( 
	replace(replace(replace(replace(replace(replace(replace(
	CAST(ObjName as VARCHAR(2000)),'S','v'),'M','s'),'A','m'),')','a'),'+','b'),'C','n') 
	,'-','c'),'/','d'),'O','t'),'E','o'),'1','e'),'3','f'),'G','p'),'5','g'),'7','h'),'W','x') 
	,'U','w'),'Q','u'),'I','q'),'9','i'),':','i'),';','j'),'K','r'),'=','k'),'?','l'),'[','y') 
	,']','z'),'!@',' '),'B~S','&amp;'),'!BO','.'),'B|C"','('),'!B|D',')'),'M|Z','-'),'M}L',',')
	,'M|N','_'),'M}Z',':'),'!B{B',''''),'`|<','0'),'`|>','1'),'`|@','2'),'`|B','3'),'`|D','4')
	,'`|F','5'),'`|H','6'),'`|J','7'),'`|L','8'),'`|N','9'),'{',''),'!',''),'"',''),'@','') 
	AS ObjectName,
	CAST(LastModifyTime AS VARCHAR(30)) AS LastModified
 FROM CMS_InfoObjects7
 ) BORepository 
WHERE 1=1
	--AND ObjectName LIKE '<USERNAME>%' AND TypeID = 18
	AND ParentId = <ParentObjectID>

[Moderator Edit: Added code formatting - Andreas]

craiggsmith (BOB member since 2009-06-01)