R/3 won't start after database restore or database copy

SAP Notes : 551915 R/3 won't start after database restore or database copy

 Summary

Symptom
R/3 won't start after a homogeneous system copy (using sp_detach and sp_attach) or database restore on a new SQL Server.

The following errors appear in the dev_w?? file(s) in the work directory:
=====================================================================
C  ExecuteAndFlush return code: 0x80040e14 Stmt:
  [if user_name() != 'prd' setuser 'prd']
C  ExecuteAndFlush: line 36329. hr: 0x80040e14 There is no such user or
   or group 'prd'.
C  HandleOledbError [ExecuteAndFlush,line 36329]: Error/Message: (err    4604, sev 0), There is no such user or group 'prd'.
C  setuser 'prd' failed -- connect terminated
=====================================================================

Other terms
user schema MCOD sp_attach sp_detach system copy

Reason and Prerequisites
There are two possible reasons for this kind of problem:

1. The original (source) system was running as a non-MCOD system, i.e. a system running with database user 'dbo'.  But the target system was installed with profile parameters which indicate an MCOD system.

2. The original system was an MCOD (multi-schema) system, or a system
which was installed as R/3 version 4.7 or later.
All the tables in such systems belong to user 'sid' (the system ID in
lower case).

Prerequisite:  A central application server installation for your
release must first be run on the target server.  This will create OS users DOMAIN\SAPServiceSID and DOMAIN\sidadm.
 

Solution
There are two ways to solve this kind of problem:
 

l Using the SAP Tools for MS SQL Server described in note 683447.  When you use these tools to perform the "Postprocessing" for the system migration (described in note 151603) you should not encounter such errors.   This tool can be used repeatedly to correct configuration problems from manual actions for example.

l If you are not able to download and run the tools of note 683447 for any reason, then you can use a manual method instead.  Scripts attached to this note are used in this case. This method is described below.

NOTE: Please follow the directions below only if you cannot use the tools of note 683447 to correct the problem.

Case 1: 'dbo' -> MCOD.
In this case you have two choices.  You can either run the target system as a non-MCOD ('dbo') system, or you can convert the database to MCOD.
  a) If you want to run the system as 'dbo', simply remove the profile parameter 'schema' (dbs/mss/schema or dbs/oledb/schema), from DEFAULT.PFL and the instance profiles.  Also make sure that the environment variable MSSQL_SCHEMA is removed from the environment of your sidadm and SAPServiceSID users.  Check that it is removed by using the SAP MMC, right-mouseclick on the application server and choose All Tasks -> View Environment.
If necessary, remove value HKEY_LOCAL_MACHINE\SOFTWARE\SAP\<SID>\Environment\MSSQL_SCHEMA from the registry.

  b) If you want to run the system as MCOD, you must run a script which re-assigns all the objects in the database to user '<sid>' where <sid> is your System ID (of the target system) converted to lowercase.  This procedure is described below in Case 2.  Use the script called user_change.sql and substitute the variables in the script as described in the header comment.  Use 'dbo' as the @OLDNAME@ and use the target <sid> as @NAME@.


Case 2: MCOD -> MCOD.
There are two cases each of which can be handled by a separate script attached to this note:
  1. The SID remained the same - use script user_restore.sql
  2. The SID changed - use script user_change.sql

Download and unzip the file attached to this note.  This zipfile contains both scripts.

Make sure that there is a local Windows account SAPMssXPUser, which is member of the local group "Administrators".

Make sure you have a good backup of the database (or the database files).

Check the registry key setting under
HKEY_LOCAL_MACHINE\SOFTWARE\SAP\<SID>\AdmUser
This key should contain a string with the value DOMAIN\sidadm. If this key doesn't exist then create it.

Connect to SQL server via Query Analyzer as an OS system administrator
user using Windows Authentication.
Open the script which fits your situation and replace variables as described in the header of the script (Use "replace all").  Check the spelling of the variables also with respect to upper/lowercase.

Execute the script.
Check that the script succeeded.  In case of errors you must correct them and re-run the script.

Then make sure your profile parameters are correct:
In DEFAULT.PFL
  SAPDBHOST=<dbservername>
If the SQL Server is a named instance, then set in addition:
  dbs/mss/server = <dbservername>\<instance_name>
<instance_name> should normally be the name of the SID but it doesn't have to be.
Also set
  dbs/mss/dbname = <SID>
  dbs/mss/schema = <sid>
The uppercase and lowercase system ID (SID) respectively.

In the environment of user DOMAIN\sidadm add or edit environment variables if necessary:
MSSQL_SCHEMA=<sid>
MSSQL_DBNAME=<SID>
MSSQL_SERVER=<sql_server_name>
DBMS_TYPE=mss
Using:
Start->Control Panel->System->Advanced->Environment Variables

Now R/3 should come up without problems.  Please also refer back to note 151603 which describes in general how to perform a homogeneous system copy on SQL Server.

To further troubleshoot connection problems you can (temporarily) set
  dbs/mss/verbose = 1
  dbs/mss/dbsl_profile = 1
In the instance profile (not DEFAULT.pfl).
This will produce additional information in the dev_w?? files, and it will produce additional files named dbsl_w? containing detailed traces from the database interface.  If this doesn't clarify the problem, then submit those files to SAP support.
Do not leave these parameters set because they produce very large trace files.