Skip to Content
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.

image

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

image

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>

To report this post you need to login first.

17 Comments

You must be Logged on to comment or reply to a post.

  1. Alvaro Tejada Galindo
    Great Code Marcus! It’s always nice to read what other developers got to say. Gonna test as soon as possible -;) Also, congratulations for your first blog -:) Welcome to the club!

    Greetins,

    Blag.

    (0) 
      1. Aashish Anchlia

        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.

        (0) 
  2. Serdar Simsekler
    Hi Marcus

    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

    (0) 
  3. Oliver Heinzelmann
    Hi Marcus,
    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.

    (0) 
    1. Michael Koch Post author
      Hi, thank you all for your feedback.
      Can you please describe your problems with exact Error Message, sure i will help you ?

      Best Regards
      Marcus

      (0) 
      1. Oliver Heinzelmann
        Hi,
        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

        (0) 
        1. Michael Koch Post author
          Hi, thx for your reply,
          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

          (0) 
          1. Oliver Heinzelmann
            Hey 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

            (0) 
  4. Said Fadhil
    Hi,

    U have done good job and also this weblog fetching for everybody while lost the table data.

    Thanks,

    Srini Nookala
    BASIS Admin.

    (0) 
  5. Charles de Gruiter
    Hi Marcus,

    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

    (0) 
  6. Anonymous
    I just create a transport of the data and release it to the file system.  It servers as a backup in an emergency situation.
    (0) 

Leave a Reply