SAP BPC 7 APPSet Restore SQL 2005

[Former Member]

Hi Everyone,

I have SAP BPC 7 setup in a multi-server environment, and it's working well. I created a dev environment, installed SAP in the same fashion, and now when I try to restore an app set through server manager in dev, it keeps putting the SQL DB on C instead of where it should go on I:\ .

The original app set was using this same drive configuration, and that backup/restore works fine -

I've gotten this to work before, but I can't seem to get the restore to go somewhere else this time...

Any thoughts appreciated -

Edward

[Former Member]

To what Bruno mentioned, I have to say that having this (since it's the dev version of our prod) in a virtual environment has made this possible to test. I can snapshot before any 'attempt' etc, and recover back in mere seconds - in testing several other hairy SAP buggies in our environment, this has been even more useful-

[Former Member]

Hi,

More testing today so let's share

There is 3 registry key in SQL Server 2005 :

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\DefaultData

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\DefaultLog

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Setup\SQLDataRoot

Key #1 and key #2 are the one you configure in SQL server Manager and they correspond to the *.MDF directory and *.LDF directory.

Key #3 is the one you configure during SQL Server installation.

SAP Server manager will use key #3 when you restore an appset and also during the installation process of the ApShell.

Key #1 and Key #2 are used by BPC setup to create the AppServer database.

So to conclude : Even if you change Key #3 value in order to change the directory where you want to create your DB files, you will not be able to put *.MDF files on a different folder (directory) thant *.LDF files. Which means that after each AppSet restoration you will have to manually move at least the *.LDF or *.MDF file. I think this can be scripted with a T-SQL command... But you will have to do it.

On a side note, i tried to delete Key #3 (was hoping that it will use key #1 and #2) => This is not working. The AppSet restore will fail (in fact this is SQL Server who will throw an error).

Will ask my customer to open a case on SAP support, maybe it could be "fixed" one day.

[Former Member]

Hi,

i faced a similar issues, there is a OSS note for this. 1449815. It talks about restore the appset manually, but it does not say how, it asks to open a OSS message but i am sure that will take days. Any suggestions.

regards

Yogesh

[Former Member]

Thanks Sebastian,

This is the third time I have ran into this issue, from 5.0 - 7.0, and I agree with your findings, but in the past I have been able to modify the registry keys for DataDefault, and LogDefault to point to the (I and L drives in this case) alternate drive, but in this scenario, that is not working.

In the appset that was backed up, and hence trying to be restored here, the drives are laid out the same, C, I, L so if the app set was backed up in this drive scenario, it should be able to be restored..I thought

Right now, since this is an attempt to build a dev env copy of a prod env, I expanded the C: drive on the SQL server, and restored the app set. It's hung up at the OLAP db portion for some odd reason, but my plan was to once it was in - change it to the I drive by moving the db through detach/re-attach, since SAP shouldn't really care at that level (all pure assumptions).

I awarded partial points for such diligent testing though - much appreciated -

I'll follow up tom-

TIA _

Edward

[Former Member]

I tried that first, both changing the SQL server properties via the GUI for DATA and LOGS, and making sure the Reg entries pointed to the right location - i'll try another reboot in-case I was not thinking clearly

[i045436]

The problem is comming from SQL settings. You can try to restore manually the backup from appset SQL folder to see where it will be created.

[Former Member]

>

> Did you designate that data path (i:) during the installation of SQL server or did you change it after SQL was installed? I have seen instances where the registry keeps the path that was chosen during the installation of SQL, regardless of what is configured after the installation, and BPC was using that path during the appset restore process.

I had the opportunity to work on this topic today. I could not confirm it at 100%, but BPC use the directory you setup during SQL Server installation (advanced settings / Database Engine / Data folder). If you change the parameters in SQL Server Management studio after the installation (E:\SQL\DATA\ for *.MDF files and F:\SQL\LOG\ for *.LDF files), BPC Server manager will not read these values and use the one you configured during SQL Server installation.

I did two installation of SQL Server today :

1. Standard install, BIN and DATA are localted in C:\Programs Files\SQL Server\

2. Customized, BIN are in C:\. DATA was in E:\SQL\DATA

In first case, all restored appset were created in C:\. In second case, everything in E:\.

Changing *.MDF and *.LDF files location after SQL Server installation did not change anything.

By the way, I think that SAP changed something because in prior release, the appset restore process used the path stored in *.BAK file.

[former_member192799]

yes that seem to be consistent with my observations in past versions. I think I had been able to change some info in the registry to change that path. However i don't remember what the key is.

(Edward, Keep in mind that changing the registry is not without risks...)

so.. in my opinion... 2 courses of action:

- finding the registry setting and changing it, and testing to verify that it resolved the issue.

- or reinstalling everything including SQL with the proper paths during installation of SQL.

(uninstalling BPC server, uninstalling SQL server, reinstalling SQL server and reinstalling BPC server)

Best regards

Bruno

[former_member192799]

Hi Edward,

did you designate that data path (i:) during the installation of SQL server or did you change it after SQL was installed? I have seen instances where the registry keeps the path that was chosen during the installation of SQL, regardless of what is configured after the installation, and BPC was using that path during the appset restore process.

best regards,

Bruno

[i045436]

HI,

You have to check the SQL database settings, there there is speified the place for data files and log files. I think you have C drive there. The Server Manager is doing a simple restore form bak and it is using the settings from SQL Server.

Hope this can help you,

Mihaela