Posted: Sun Jan 20, 2008 12:48 am Post subject: Backup any table data
No Problem with lost table data anymore! An easy approach to backup table data.
Marcus Pospiech
Introduction
Some time ago I made a change in a very significat table in our production enviroment, I'd made a little mistake and all the data in that table was gone. After that I got the idea to write a little program to prevent me and the rest of the team from similiar problems in the future.
What can it do?
With this little program you can backup any table data, doesn't matter which table strucure is it.
To create a backup of one or more tables insert the table name(s), optionally you can enter a project and a description - just for documentation issues.
The different functions:
- Create a backup
Create a backup of any table. This Backup can be started manualy or can be done automatically with SAP Job Scheduling and a variant e.g. on daily basis.
The param "Number of saved Backups" means, how many backups will be stored. If you choose e.g. 10 the last 10 backups will be saved.
- view last Backup
Only possible for one table, view the data of the last backup in a Standard ALV Grid.
- Overview of all Backups
Only possible for one table, list all backup versions with date and time in a table. It's actual not implemented to view another than the last backup.
- Restore of last Backup
Only possible for one table, makes only a insert(!) of the backup data to the table, so if you wish to make a full restore, delete first the table data in the original table. It's actually not implemented to restore another than the last backup.
How does it work?
It's just an generic approach to get table data, the trick is to save the data in a Cluster table. Instead of a normal table, this gives us the flexibility to save data of any table structure.
Implement the Coding in 3 easy steps
1. Create an Include Programm with se38
YRDW_INCL_ALV_GRID
Code:
** ALV Darstellung
TYPES: BEGIN OF l_ty_keyfields,
fieldname TYPE dd03l-fieldname,
position TYPE dd03l-position,
END OF l_ty_keyfields.
DATA: ok_code LIKE sy-ucomm,
popans.
DATA: ref_docking TYPE REF TO cl_gui_docking_container,
ref_alv TYPE REF TO cl_gui_alv_grid,
gs_layout TYPE lvc_s_layo.
data: l_t_keyfields TYPE l_ty_keyfields OCCURS 0 WITH HEADER LINE,
l_key1 TYPE fieldname,
l_key2 TYPE fieldname,
l_key3 TYPE fieldname,
l_key4 TYPE fieldname,
l_key5 TYPE fieldname,
l_key6 TYPE fieldname,
l_key7 TYPE fieldname,
l_key8 TYPE fieldname,
l_key9 TYPE fieldname,
l_key10 TYPE fieldname,
l_key11 TYPE fieldname,
l_key12 TYPE fieldname,
l_key13 TYPE fieldname,
l_key14 TYPE fieldname,
l_key15 TYPE fieldname,
l_key16 TYPE fieldname,
l_before TYPE i,
l_after type i.
*&---------------------------------------------------------------------*
*& Include YRDW_ALV_GRID *
*&---------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
SET PF-STATUS 'ST100'.
SET TITLEBAR 'T100'.
ENDMODULE. "status_0100 OUTPUT
IF sy-subrc <> 0.
MESSAGE a000(rfw) WITH text-aer.
ENDIF.
ref_alv->set_mode( 0 ).
*§1.Set status of all cells to editable using the layout structure.
IF p_edit IS NOT INITIAL.
gs_layout-edit = 'X'.
ENDIF.
*§2.Use SET_READY_FOR_INPUT to allow editing initially.
* (state "editable and ready for input").
IF p_edit IS NOT INITIAL.
CALL METHOD ref_alv->set_ready_for_input
EXPORTING
i_ready_for_input = 1.
ENDIF.
ENDIF.
ENDMODULE. "init_controls_0100 OUTPUT
CASE popans.
WHEN 'J'.
LEAVE PROGRAM.
WHEN 'N'.
CLEAR ok_code.
ENDCASE.
ENDMODULE. "leave_programm INPUT
*---------------------------------------------------------------------*
* MODULE user_command_0100 INPUT
*---------------------------------------------------------------------*
*
*---------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
CASE ok_code.
WHEN 'BACK'.
CLEAR popans.
* call function 'POPUP_TO_CONFIRM_STEP'
* exporting
** DEFAULTOPTION = 'Y'
* textline1 = text-dml
* textline2 = text-rbk
* titel = text-bak
** START_COLUMN = 25
** START_ROW = 6
* cancel_display = ' '
* importing
* answer = popans.
*
* case popans.
* when 'J'.
LEAVE TO SCREEN 0.
* endcase.
WHEN 'SAVE'.
CALL METHOD ref_alv->check_changed_data.
SELECT fieldname position INTO TABLE l_t_keyfields FROM dd03l
WHERE tabname = p_tname AND as4local = 'A' AND keyflag = 'X'
AND ( comptype = 'E' OR comptype = '' )
ORDER BY position.
LOOP AT l_t_keyfields.
CASE sy-tabix.
WHEN 1.
l_key1 = l_t_keyfields-fieldname.
WHEN 2.
l_key2 = l_t_keyfields-fieldname.
WHEN 3.
l_key3 = l_t_keyfields-fieldname.
WHEN 4.
l_key4 = l_t_keyfields-fieldname.
WHEN 5.
l_key5 = l_t_keyfields-fieldname.
WHEN 6.
l_key6 = l_t_keyfields-fieldname.
WHEN 7.
l_key7 = l_t_keyfields-fieldname.
WHEN 8.
l_key8 = l_t_keyfields-fieldname.
WHEN 9.
l_key9 = l_t_keyfields-fieldname.
WHEN 10.
l_key10 = l_t_keyfields-fieldname.
WHEN 11.
l_key11 = l_t_keyfields-fieldname.
WHEN 12.
l_key12 = l_t_keyfields-fieldname.
WHEN 13.
l_key13 = l_t_keyfields-fieldname.
WHEN 14.
l_key14 = l_t_keyfields-fieldname.
WHEN 15.
l_key15 = l_t_keyfields-fieldname.
WHEN 16.
l_key16 = l_t_keyfields-fieldname.
ENDCASE.
ENDLOOP.
IF l_after EQ l_before.
DELETE FROM (p_tname).
INSERT (p_tname) FROM TABLE <ft_tabcontent>.
ELSE.
CALL FUNCTION 'POPUP_TO_INFORM'
EXPORTING
titel = ''
txt1 = text-007
txt2 = ''.
ENDIF.
WHEN OTHERS.
ENDCASE.
ENDMODULE. "user_command_0100 INPUT
2. Create a Cluster table with se16
table name: YRDW_TBACKUP
3. Create the main Program with se38
report: YRDW_TAB_BACKUP
Code:
************************************************************************
* YRDW_TAB_BACKUP
* +------------------------------------------------------------------+ *
* Short description in english (or in german) *
* ============================================ *
* Mit Hilfe dieses Programms kann jedbeliebige Datenbanktabelle gesichert werden.
* Die Daten werden in einem Cluster abgelegt und können anschließend reproduziert werden,
* es ist auch möglich mehrere Stände abzuspeichern.
************************************************************************
REPORT yrdw_tab_backup .
FIELD-SYMBOLS: <ft_tabcontent> TYPE ANY TABLE.
DATA: ls_tbackup TYPE yrdw_tbackup,
lt_tbackup TYPE TABLE OF yrdw_tbackup.
TYPE-POOLS: rssg.
DATA ref_itab TYPE REF TO data.
DATA objid TYPE rssg_uni_idc25.
DATA: p_tname TYPE dd02l-tabname,
p_edit TYPE flag.
SELECTION-SCREEN BEGIN OF BLOCK input1
WITH FRAME TITLE text-001.
SELECTION-SCREEN SKIP 1.
PARAMETERS: rb_back RADIOBUTTON GROUP one,
rb_restl RADIOBUTTON GROUP one,
rb_resto RADIOBUTTON GROUP one,
rb_rest RADIOBUTTON GROUP one.
SELECTION-SCREEN SKIP 1.
*parameters p_tname type dd02l-tabname obligatory.
SELECT-OPTIONS: st_tab FOR p_tname NO INTERVALS. " obligatory.
PARAMETERS p_bnum TYPE i.
SELECTION-SCREEN SKIP 1.
SELECTION-SCREEN END OF BLOCK input1.
SELECTION-SCREEN BEGIN OF BLOCK input2
WITH FRAME TITLE text-002.
SELECTION-SCREEN SKIP 1.
PARAMETERS p_proj TYPE CHAR_LG_60.
PARAMETERS p_desc(80) TYPE c.
SELECTION-SCREEN END OF BLOCK input2.
SELECTION-SCREEN BEGIN OF BLOCK input3
WITH FRAME TITLE text-003.
SELECTION-SCREEN SKIP 1.
SELECTION-SCREEN END OF BLOCK input3.
IF st_tab IS INITIAL.
IF sy-batch IS INITIAL.
WRITE: 'Sie müssen zuerst mind. eine Tabelle angeben'.
ENDIF.
ELSE.
DATA: ls_tab LIKE LINE OF st_tab.
***************************************************************
* Backup erstellen
***************************************************************
IF rb_back NE space.
DELETE ADJACENT DUPLICATES FROM st_tab.
LOOP AT st_tab INTO ls_tab WHERE sign EQ 'I' AND high EQ ''.
p_tname = ls_tab-low.
* Daten lesen
CREATE DATA ref_itab TYPE STANDARD TABLE OF (p_tname)
WITH NON-UNIQUE DEFAULT KEY.
ASSIGN ref_itab->* TO <ft_tabcontent>.
SELECT * FROM (p_tname) INTO TABLE <ft_tabcontent>.
* Kopfdaten des Clusters setzen
CALL FUNCTION 'RSS_SYSTEM_GET_UNIQUE_ID'
IMPORTING
e_uni_idc25 = objid.
* Cluster daten schreiben
EXPORT <ft_tabcontent> FROM <ft_tabcontent>
TO DATABASE yrdw_tbackup(tb)
ID ls_tbackup-objid FROM ls_tbackup.
IF sy-subrc EQ 0.
IF sy-batch IS INITIAL.
* write: /, 'Backup wurde für Tabelle ', p_tname, 'erfolgreich erstellt'.
ENDIF.
ENDIF.
* Löschen alter Backups
IF p_bnum IS NOT INITIAL.
*** Wie viele Backups existieren ?
SELECT * FROM yrdw_tbackup INTO TABLE lt_tbackup WHERE tab EQ p_tname AND srtf2 EQ 0 ORDER BY datum DESCENDING zeit DESCENDING.
*** Backups nur dann Löschen wenn mehr da als erlaubt
IF LINES( lt_tbackup ) > p_bnum.
LOOP AT lt_tbackup INTO ls_tbackup.
IF sy-tabix > p_bnum.
DELETE FROM yrdw_tbackup WHERE objid = ls_tbackup-objid.
ENDIF.
ENDLOOP.
ENDIF.
ENDIF.
ENDLOOP.
ENDIF.
*****************************************************************
*** Folgende Funktionen nur für eine Tabelle gültig
*****************************************************************
IF LINES( st_tab ) NE 1.
IF rb_back EQ space.
IF sy-batch IS INITIAL.
WRITE: /, 'Funktion ist nur mit einer Tabelle ausführbar'.
WRITE: /, 'Ausschliesslich die Funktion Backup erstellen ist für mehrere Tabellen verfügbar'.
ENDIF.
ENDIF.
ELSE.
LOOP AT st_tab INTO ls_tab WHERE sign EQ 'I' AND high EQ ''.
p_tname = ls_tab-low.
***************************************************************
* letztes Backup anschauen
***************************************************************
IF rb_restl NE space.
SELECT * FROM yrdw_tbackup INTO ls_tbackup WHERE tab EQ p_tname ORDER BY datum ASCENDING zeit ASCENDING .
ENDSELECT.
CREATE DATA ref_itab TYPE STANDARD TABLE OF (p_tname)
WITH NON-UNIQUE DEFAULT KEY.
ASSIGN ref_itab->* TO <ft_tabcontent>.
IMPORT <ft_tabcontent> TO <ft_tabcontent> FROM DATABASE yrdw_tbackup(tb)
ID ls_tbackup-objid ACCEPTING PADDING.
***************************************************************
* letztes Backup an tabelle appenden
***************************************************************
IF rb_rest NE space.
SELECT * FROM yrdw_tbackup INTO ls_tbackup WHERE tab EQ p_tname ORDER BY datum ASCENDING zeit ASCENDING .
ENDSELECT.
CREATE DATA ref_itab TYPE STANDARD TABLE OF (p_tname)
WITH NON-UNIQUE DEFAULT KEY.
ASSIGN ref_itab->* TO <ft_tabcontent>.
IMPORT <ft_tabcontent> TO <ft_tabcontent> FROM DATABASE yrdw_tbackup(tb)
ID ls_tbackup-objid ACCEPTING PADDING.
INSERT (p_tname) FROM TABLE <ft_tabcontent> ACCEPTING DUPLICATE KEYS.
IF sy-subrc EQ 0.
WRITE: /, ls_tbackup-lines, ' Zeilen wurden erfolgreich der Tabelle ', p_tname, ' hinzugefügt'.
ELSE.
WRITE: 'Fehler beim einfügen des Backups'.
ENDIF.
CALL SCREEN 100.
ENDIF.
***************************************************************
* Overview aller Backups zu Tabelle anschauen
***************************************************************
IF rb_resto NE space.
SELECT * FROM yrdw_tbackup INTO TABLE lt_tbackup WHERE tab EQ p_tname ORDER BY datum ASCENDING zeit ASCENDING .
* endselect.
p_tname = 'yrdw_tbackup'.
CREATE DATA ref_itab TYPE STANDARD TABLE OF (p_tname)
WITH NON-UNIQUE DEFAULT KEY.
ASSIGN ref_itab->* TO <ft_tabcontent>.
MOVE lt_tbackup TO <ft_tabcontent>.
CALL SCREEN 100.
ENDIF.
ENDLOOP.
ENDIF.
ENDIF.
* Include für ALV GRID
INCLUDE yrdw_incl_alv_grid.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
