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
-
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
—-
-
MODULE clear_ok_code OUTPUT
—-
*
—-
MODULE clear_ok_code OUTPUT.
CLEAR ok_code.
ENDMODULE. “clear_ok_code OUTPUT
—-
-
MODULE init_controls_0100 OUTPUT
—-
*
—-
MODULE init_controls_0100 OUTPUT.
IF ref_docking IS INITIAL.
CREATE OBJECT ref_docking
EXPORTING
extension = 2000
EXCEPTIONS
OTHERS = 6.
IF sy-subrc 0.
MESSAGE a033(rfw).
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
—-
-
MODULE leave_programm INPUT
—-
*
—-
MODULE leave_programm INPUT.
CLEAR popans.
CALL FUNCTION ‘POPUP_TO_CONFIRM_STEP’
EXPORTING
-
DEFAULTOPTION = ‘Y’
textline1 = text-dml
textline2 = text-rcn
titel = text-cnc
-
START_COLUMN = 25
-
START_ROW = 6
cancel_display = ‘ ‘
IMPORTING
answer = popans.
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.
CLEAR: l_key1, l_key2, l_key3, l_key4, l_key5, l_key6,
l_key7, l_key8, l_key9, l_key10, l_key11, l_key12, l_key13,
l_key14, l_key15, l_key16.
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.
SORT
2. Create a Cluster table with se16
table name: YRDW_TBACKUP
3. Create the main Program with se38
table name: YRDW_TAB_BACKUP
************************************************************************
-
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: 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.
SELECTION-SCREEN PUSHBUTTON /1(20) text-006 USER-COMMAND onli.
-
REACTION ON PUSHBUTTON *
START-OF-SELECTION.
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 .
-
Kopfdaten des Clusters setzen
CALL FUNCTION ‘RSS_SYSTEM_GET_UNIQUE_ID’
IMPORTING
e_uni_idc25 = objid.
ls_tbackup-datum = sy-datum.
ls_tbackup-zeit = sy-uzeit.
ls_tbackup-tab = p_tname.
ls_tbackup-projekt = p_proj.
ls_tbackup-relid = ‘TB’.
ls_tbackup-objid = objid.
ls_tbackup-lines = LINES( ).
ls_tbackup-description = p_desc.
-
Cluster daten schreiben
EXPORT
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 FROM DATABASE yrdw_tbackup(tb)
ID ls_tbackup-objid ACCEPTING PADDING.
WRITE: /, ‘Letztes Backup der Tabelle: ‘, p_tname, ‘ist vom ‘, ls_tbackup-datum, ‘ ‘, ls_tbackup-zeit.
WRITE: /, ‘Das Backup enthält’, ls_tbackup-lines, ‘ Zeilen.’.
CALL SCREEN 100.
ENDIF.
***************************************************************
-
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 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 .
CALL SCREEN 100.
ENDIF.
ENDLOOP.
ENDIF.
ENDIF.
-
Include für ALV GRID
INCLUDE yrdw_incl_alv_grid.
</TEXTAREA>
Greetins,
Blag.
gives the dump saying screen 100 doesnt exist!!
plz help!!
gunjan
Paste the code for the report from here:parameters p_tname type dd02l-tabname obligatory.<br/>SELECT-OPTIONS: st_tab FOR p_tname NO INTERVALS. " obligatory.<br/>PARAMETERS p_bnum TYPE i.<br/>SELECTION-SCREEN SKIP 1.<br/>SELECTION-SCREEN END OF BLOCK input1.<br/><br/>SELECTION-SCREEN BEGIN OF BLOCK input2<br/> WITH FRAME TITLE text-002.<br/>SELECTION-SCREEN SKIP 1.<br/><br/><br/>PARAMETERS p_proj TYPE char_lg_60.<br/>PARAMETERS p_desc(80) TYPE c.<br/>SELECTION-SCREEN END OF BLOCK input2.<br/><br/><br/>SELECTION-SCREEN BEGIN OF BLOCK input3<br/> WITH FRAME TITLE text-003.<br/>SELECTION-SCREEN SKIP 1.<br/>SELECTION-SCREEN END OF BLOCK input3.<br/><br/>SELECTION-SCREEN PUSHBUTTON /1(20) text-006 USER-COMMAND onli.<br/><br/>* P_BNUM Number of Saved Backups<br/>** P_DESC Project Description<br/>** P_PROJ Project<br/>** RB_BACK Create Backup<br/>** RB_REST Restore Last<br/>** RB_RESTL View Last Backup<br/>** RB_RESTO Overview of Backups<br/>** ST_TAB Table Name<br/><br/><br/><br/>* REACTION ON PUSHBUTTON <br/>START-OF-SELECTION.<br/><br/> IF st_tab IS INITIAL.<br/> IF sy-batch IS INITIAL.<br/> WRITE: 'Provide a table name'.<br/> ENDIF.<br/> ELSE.<br/><br/><br/> DATA: ls_tab LIKE LINE OF st_tab.<br/><br/>*************************************************************<br/> TAKE Backup<br/>**************************************************************<br/> IF rb_back NE space.<br/> DELETE ADJACENT DUPLICATES FROM st_tab.<br/> LOOP AT st_tab INTO ls_tab WHERE sign EQ 'I' AND high EQ ''.<br/> p_tname = ls_tab-low.<br/>Data readDelete old Backups<br/> IF p_bnum IS NOT INITIAL.<br/><br/>**How do many Backups exist?<br/> SELECT * FROM yrdw_tbackup INTO TABLE lt_tbackup WHERE tab EQ p_tname AND srtf2 EQ 0 ORDER BY datum DESCENDING zeit DESCENDING.<br/><br/>*** DELETE Backups only if more there than permited backups<br/><br/><br/> IF LINES( lt_tbackup ) > p_bnum.<br/> LOOP AT lt_tbackup INTO ls_tbackup.<br/> IF sy-tabix > p_bnum.<br/> DELETE FROM yrdw_tbackup WHERE objid = ls_tbackup-objid.<br/> ENDIF.<br/> ENDLOOP.<br/> ENDIF.<br/><br/> ENDIF.<br/> ENDLOOP.<br/><br/><br/> ENDIF.<br/><br/>****************************************************************<br/>** The following functions only for a table valid<br/>****************************************************************<br/><br/> IF LINES( st_tab ) NE 1.<br/> IF rb_back EQ space.<br/> IF sy-batch IS INITIAL.<br/> WRITE: /, 'Function is executable only with a table'.<br/> WRITE: /, 'Functions other than Create Backup are available for several tables'.<br/> ENDIF.<br/> ENDIF.<br/> ELSE.<br/> LOOP AT st_tab INTO ls_tab WHERE sign EQ 'I' AND high EQ ''.<br/> p_tname = ls_tab-low.<br/>*************************************************************<br/> look at last BackupCALL SCREEN 100.<br/> ENDIF.<br/><br/>*************************************************************<br/> Backup Overview<br/>***************************************************************<br/> IF rb_resto NE space.<br/><br/> WRITE :/ 'Table: ', p_tname, /.<br/> ULINE.<br/> WRITE: / '| DATE/TIME | Lines |'.<br/> ULINE.<br/><br/> SELECT * FROM yrdw_tbackup INTO ls_tbackup WHERE tab EQ p_tname ORDER BY datum ASCENDING zeit ASCENDING .<br/> WRITE: / '|',ls_tbackup-datum, ls_tbackup-zeit, '|', ls_tbackup-lines, '|'.<br/> ENDSELECT.<br/><br/> ULINE.<br/> ENDIF.<br/><br/><br/>**************************************************************<br/> restore from last backup**************************************************************<br/>Overview of all Backups for a tableCALL SCREEN 100.<br/> ENDIF.<br/> ENDLOOP.<br/> ENDIF.<br/> ENDIF.<br/><br/> Include für ALV GRID<br/> INCLUDE yrdw_incl_alv_grid.
Your weblog is simply superb.Keep up ur good work.
Regards,
Abdul
It is really a nice idea. But accept the following as a modest addition to your idea:
Maybe a rare situation for some but for the cases where table structure may change:
1. An option might be added providing saving the table field structure with the backup data. There should be some standart DDIC-related FM for that or you can select from dd* tables. Or while restoring (and also displaying) you should read the table structure from the version history if required. That requires change of the restore algorithm which should now consider table structure change.
2. Alternatively, you might prevent restoring of tables whose structure is changed after last backup and handle displaying of data accordingly.
Regards
thank you for your nice feedback, i will think about implementing this feature.
Regards
Marcus
This utility is great. I tried implementing it.
Main program gives errors and so could not implement further.
Thanks,
Pratibha
which errors do you get ? Maybe we can solve it!
Regards Marcus
CREATE DATA ref_itab TYPE STANDARD TABLE OF (p_tname) WITH
NON-UNIQUE DEFAULT KEY.
gives an error
Unable to interpret 'TABLE'
Thanks,
Pratibha
realy great idea. I tried to implement your Code/Programm into my System. But the Compiler shows some Syntax Errors like "Field .... unknown" etc.
Maybe you can help me. You find my email @ my Buisness Card.
Thx.
Can you please describe your problems with exact Error Message, sure i will help you ?
Best Regards
Marcus
the first error i get is:
"Field "YRDV_9_PMTEST-INFOCUBE" is unknown."
When i comment this entry the next error is:
"The Type "YRDW_DE_PROJEKT" is unknown."
Oli
you can delete the line "ld_infocube like YRDV_9_PMTEST-INFOCUBE" it`s not needed and use type CHAR_LG_60 instead of YRDW_DE_PROJEKT.
I updated the Weblog Code.
Sorry for this, i checked the code for more own types- so it should work now.
Regards Marcus
GREAT!! now it works. The only thing that doesn't work is to view all Backups done. The Programm wants to call Screen 100 that doesnt exist. But that should be no Problem.
So thanks for that Programm. Its very usefull.
Oli
U have done good job and also this weblog fetching for everybody while lost the table data.
Thanks,
Srini Nookala
BASIS Admin.
Good idea!
I tried implementing the programs and table, but also got syntax-error on non-existing screen 100. Can you give some info on this screen?
You can also mail me on cdegruiter@zonnet.nl
Thanks, and keep up the good work
Charles de Gruiter