Sapbasis

SAP Note 151603 Note Language: English

Copying an SQL Server database
Version: 48 Validity:
Valid Since 15.08.2011

Summary
Symptom
You want to copy an SQL Server database within a homogenous system copy. This procedure has been released for all SAP applications that are based on an SAP database (BW, CRM).

Other terms
Homogenous system copy, BW, migration, sp_attach_db, sp_detach_dbReason and Prerequisites
This note describes the system copy procedure using the SQL Server system stored procedures sp_detach_db and sp_attach_db or the command 'CREATE DATABASE FOR ATTACH'. Alternatively, you can restore the database.

Solution
The following example describes the system copy procedure of the productive PRD SAP system to the Quality assurance system (QAS).

Step 1:
1.Preparations

For Java or ABAP + Java systems that are based on NetWeaver technology lower than Version 7.10, you must first export parts of the Java source system. For this, you must use the delivered SAP installation master DVD of your installed SAP release. The export of the Java part into a file is then created using the option 'system copy'. Establish whether you use a schema system ornot. In the SAP system, choose the menu option "System" -> "Status" and check the value of the field "Owner". In a schema system (MCOD), the owner has the value , while the value is 'dbo' in a non-schema system. Install the SQL Server and the SAP system on the target system. For SQL Server 2000, you are required to take Note 600027 into account. Prepare the source database using the homogenoussystem copy guide. You will find the guide on SAP Service Marketplace at: http://service.sap.com/instguides. You can also use this procedure to perform an SQL Server upgrade by appending the database to an SQL Server that has a higher version. You can append an SQL Server 2000 database to an SQL Server Version 2000, 2005, and 2008. You can append an SQL Server 2005 database to SQL Server Versions 2005and 2008; you can append an SQL Server 2008 database only to an SQL Server 2008. As part of the append process, the database is converted to the new version. Ensure that the log file(s) of the database can grow automatically and that sufficient disk space exists for a growing log file. Once the database has been converted, you cannot append it to the old SQL Server version again.
Page 1 of 92.

3.

4.

10.04.2012

SAP Note 151603 -

Copying an SQL Server database

For this reason, you must ensure that all prerequisites (SQL Server collation, SQL data types, SAP Basis Support Packages, and so on) are fulfilled for the new database version BEFORE you append the database to the higher version. Follow the instructions for database migration in your SAP release. 5. Delete theautomatically generated stored procedures. Using the SQL Server Query Analyzer or the SQL Server Management Studio, execute the following commands in the SAP database: Schema system: use go setuser '' go exec sap_droproc Non-schema system: use go exec sap_droproc These commands delete all temporary stored procedures in the database. They are created again automatically if necessary while the SAPsystem is operating. 6. If your system was configured for Performance Warehouse Monitoring by Solution Manager, you must read Note 1388700, since important post-processing steps are required after the database copy.

Step 2:

Determine the file structure of the source database

Determine the file structure of the source database using the Server Query Analyzer. Connect to the database to becopied and execute the command sp_helpfile. This command provides the physical name (file name) and the logical name (name) of all database files. Example: use PRD exec sp_helpfile Result: name fileid filename filegroup size -------- ------ ------------------------------------------PRDDATA1 1 d:\PRDDATA1\PRDDATA1.mdf PRIMARY 11214912 KB PRDLOG1 2 e:\PRDLOG1\PRDLOG1.ldf NULL 287816 KB PRDDATA2 3...