Restore QAS from PRD backup

You can script it like this:

RESTORE DATABASE QAS
FROM Tape0, Tape1, Tape2
WITH NORECOVERY,
FILE = 4,

MOVE 'PRDDATA1' TO 'H:\DATA1\QASDATA1.MDF',
MOVE 'PRDDATA2' TO 'H:\DATA2\QASDATA2.NDF',
MOVE 'PRDDATA3' TO 'H:\DATA3\QASDATA3.NDF',
MOVE 'PRDDATA4' TO 'H:\DATA4\QASDATA4.NDF'

RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251240.TRN' WITH NORECOVERY;
RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251410.TRN' WITH NORECOVERY;
RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251425.TRN' WITH NORECOVERY;
RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251440.TRN' WITH NORECOVERY;
RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251455.TRN' WITH NORECOVERY;
RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251510.TRN' WITH NORECOVERY;
RESTORE LOG TS2 FROM DISK = 'Q:\FROM_PRD\PRD_tlog_200801251525.TRN' WITH RECOVERY;


-- "Tape0, Tape1, Tape2" is the device that the backup is being restored from. In this case, three tape drives in parallel.

-- The MOVE statements tell SQL Server where the original DB files from PRD will now be located on the QAS server.

-- The Q:\ drive here is a location that contains the transaction logs copied over from the PRD system.

-- Always make sure you use the NORECOVERY option in every step before you are finished applying logs, and use the RECOVERY option with the last log you want to apply. Once you use the RECOVERY option, no more logs can be applied and a new CHECKPOINT is set in the DB, so make sure you get it right the first time.

ST03 Performance monitoring in ECC 6.0

Starting in ECC 6.0, SAP changed the programs used to populate performance data tables for transaction ST03. The following programs are now being used: SWNCCOLL, SWNCTOTAL and SWNCREORG. Make sure that you have scheduled jobs that run these programs with the following frequency:

• SWNCCOLL - Hourly, 7 days a week
• SWNCTOTAL - Once daily, 7 days a week
• SWNCREORG - Every 3 hours, 7 days a week

SQLServer Reorg Necessary?

If you are coming from an Oracle environment, the term "reorg" is a big deal. In an Oracle DB, if you have done any archiving, a full reorg of the affected tables would have to be performed in order to utilize the space freed up by archiving. This is not so in SQLServer 2000 and above (and I believe SQL 7 as well). SQLServer will automatically utilize the free space in a datafile at the block level so that reorgs are not necessary to regain free space.

Now, if you are performing a reorg in order to increase performance, you should consider ordering your data in such a way that SQLServer spreads the data across all datafiles evenly. All datafiles would then have a somewhat equal portion of High and Low I\O rows, thus optimizing overall performance.

It is possible to manage performance at the table level and assigning tables to specific datafiles that you have placed on a faster array, but this level of granularity is likely more work than it is worth.

Mass Locking/Unlocking Users in SAP

Create the following report and execute it with the a list of users you do not want locked in the sapmnt\IGNORE_USERS_.txt and it will generate a list of locked users in sapmnt\LOCKED_USERS_.txt:

REPORT ZLOCKUSR.

TABLES: USR02.

PARAMETERS: FUNCT_(1) TYPE C DEFAULT SPACE.

PARAMETERS: IGNOR_(65) TYPE C DEFAULT
'\\SAPSID\sapmnt\ignore_users_CLI.txt',
LOCKD_(65) TYPE C DEFAULT
'\\SAPSID\sapmnt\locked_users_CLI.txt'.

DATA: YULOCK TYPE X VALUE '80', "Gesperrt durch Falschanmeld.
      YUSLOC TYPE X VALUE '40'. "Gesperrt durch Administrator

DATA: MESSAGE_TITLE(40),
      MESSAGE_TEXT(60),
      NUM_LOCKED TYPE I.

DATA: USER LIKE USR02-BNAME.

DATA: BEGIN OF IGNORE_TAB OCCURS 0,
BNAME LIKE USR02-BNAME,
END OF IGNORE_TAB.

DATA: LOCKED LIKE USR02-BNAME,
UNLOCKED LIKE USR02-BNAME,
ANSWER TYPE C.

INITIALIZATION.
REPLACE 'SAPSID' WITH SY-HOST(5) INTO IGNOR_.
REPLACE 'SAPSID' WITH SY-HOST(5) INTO LOCKD_.
REPLACE 'CLI' WITH SY-MANDT INTO IGNOR_.
REPLACE 'CLI' WITH SY-MANDT INTO LOCKD_.

START-OF-SELECTION.
PERFORM MAIN.


USER = 'ZUSER'.


*&---------------------------------------------------------------------*
*& Form MAIN
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM MAIN.
PERFORM CHECK_FUNCTION.
* perform status_message.
ENDFORM. " MAIN
*&---------------------------------------------------------------------*
*& Form CHECK_FUNCTION
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM CHECK_FUNCTION.
CASE FUNCT_.
WHEN 'L'.
PERFORM LOCK_USERS.
WHEN 'U'.
PERFORM UNLOCK_USERS.
WHEN 'l'.
PERFORM LOCK_USERS.
WHEN 'u'.
PERFORM UNLOCK_USERS.
WHEN OTHERS.
PERFORM STATUS_MESSAGE.
ENDCASE.
ENDFORM. " CHECK_FUNCTION
*&---------------------------------------------------------------------*
*& Form LOCK_USERS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM LOCK_USERS.
MESSAGE_TITLE = 'Warning!'.
MESSAGE_TEXT = 'Users not in file will be locked.'.

CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
EXPORTING
DEFAULTOPTION = 'Y'
TEXTLINE1 = MESSAGE_TEXT
TEXTLINE2 = 'OK to proceed?'
TITEL = MESSAGE_TITLE
IMPORTING
ANSWER = ANSWER
EXCEPTIONS
OTHERS = 1.
IF ANSWER = 'J'.
NUM_LOCKED = 0.
CLEAR IGNORE_TAB.
REFRESH IGNORE_TAB.
OPEN DATASET LOCKD_ FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
OPEN DATASET IGNOR_ FOR INPUT IN TEXT MODE ENCODING DEFAULT.
READ DATASET IGNOR_ INTO IGNORE_TAB.
IF SY-SUBRC = 0.
DO.
APPEND IGNORE_TAB.
READ DATASET IGNOR_ INTO IGNORE_TAB.
IF SY-SUBRC NE 0.
EXIT.
ENDIF.
ENDDO.
ENDIF.
SORT IGNORE_TAB.
SELECT * FROM USR02 WHERE BNAME NE 'SAP*'
AND BNAME NE 'DDIC'
AND BNAME NE 'SAPCPIC'.
READ TABLE IGNORE_TAB WITH KEY USR02-BNAME BINARY SEARCH.
IF SY-SUBRC NE 0.
IF USR02-UFLAG = YULOCK AND USR02-UFLAG = YUSLOC.
PERFORM LOCK_USER(SAPMS01J) USING USR02-BNAME.
NUM_LOCKED = NUM_LOCKED + 1.
LOCKED = USR02-BNAME.
TRANSFER LOCKED TO LOCKD_.
ENDIF.
ENDIF.
ENDSELECT.
CLOSE DATASET LOCKD_.
MESSAGE_TITLE = 'Information'.
MESSAGE_TEXT = ' Users Locked'.
MOVE NUM_LOCKED TO MESSAGE_TEXT(5).
CALL FUNCTION 'POPUP_TO_INFORM'
EXPORTING
TITEL = MESSAGE_TITLE
TXT1 = MESSAGE_TEXT
TXT2 = SPACE
EXCEPTIONS
OTHERS = 1.
ELSE.
EXIT.
ENDIF.
ENDFORM. " LOCK_USERS

*&---------------------------------------------------------------------*
*& Form UNLOCK_USERS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM UNLOCK_USERS.
REFRESH IGNORE_TAB.
CLEAR IGNORE_TAB.
MESSAGE_TITLE = 'Warning!'.
MESSAGE_TEXT = 'Users in file will be unlocked.'.

CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
EXPORTING
DEFAULTOPTION = 'Y'
TEXTLINE1 = MESSAGE_TEXT
TEXTLINE2 = 'OK to proceed?'
TITEL = MESSAGE_TITLE
IMPORTING
ANSWER = ANSWER
EXCEPTIONS
OTHERS = 1.
IF ANSWER = 'J'.
OPEN DATASET LOCKD_ FOR INPUT IN TEXT MODE ENCODING DEFAULT.
READ DATASET LOCKD_ INTO IGNORE_TAB.
IF SY-SUBRC = 0.
DO.
APPEND IGNORE_TAB.
READ DATASET LOCKD_ INTO IGNORE_TAB.
IF SY-SUBRC NE 0.
EXIT.
ENDIF.
ENDDO.
ENDIF.
SORT IGNORE_TAB.
LOOP AT IGNORE_TAB.
SELECT SINGLE * FROM USR02 WHERE BNAME = IGNORE_TAB-BNAME.
IF SY-SUBRC EQ 0.
* if usr02-uflag o yusloc.
PERFORM UNLOCK_USER(SAPMS01J) USING USR02-BNAME.
NUM_LOCKED = NUM_LOCKED + 1.
* endif.
ENDIF.
CLOSE DATASET LOCKD_.
ENDLOOP.
MESSAGE_TITLE = 'Information'.
MESSAGE_TEXT = ' Users UnLocked'.
MOVE NUM_LOCKED TO MESSAGE_TEXT(5).
CALL FUNCTION 'POPUP_TO_INFORM'
EXPORTING
TITEL = MESSAGE_TITLE
TXT1 = MESSAGE_TEXT
TXT2 = SPACE
EXCEPTIONS
OTHERS = 1.
ENDIF.
ENDFORM. " UNLOCK_USERS

*&---------------------------------------------------------------------*
*& Form EXIT_MESSAGE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM EXIT_MESSAGE.

ENDFORM. " EXIT_MESSAGE
*&---------------------------------------------------------------------*
*& Form STATUS_MESSAGE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM STATUS_MESSAGE.
CLEAR MESSAGE_TEXT.
MESSAGE_TITLE = 'Information'.
MESSAGE_TEXT = 'Please enter U (unlock) or L (lock)'.
CALL FUNCTION 'POPUP_TO_INFORM'
EXPORTING
TITEL = MESSAGE_TITLE
TXT1 = MESSAGE_TEXT
TXT2 = SPACE
EXCEPTIONS
OTHERS = 1.

ENDFORM. " STATUS_MESSAGE