How to apply transaction log backup in Sybase

[Former Member]

Hi,

Ten days before i took an dump backup by using commnad dump database <SID> to 'path to backup/backup.dmp'.Backup done succesfully.

I want to restore on another server with data till today.

Can i use last full  backup which i took 10 days before  and  today do only transaction log backup and restore it?Like we do in Oracle by applying archive logs.

Regards

Adil

[Former Member]

Hi,

While starting restore its giving me error :-

1> load database SIDfrom "/backup/SID.dmp"

2> go

Backup Server session id is: 9. Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 6.28.1.1: Dumpfile name 'SID140470F597    ' section number 1

mounted on disk file '/backup/SID.dmp'

Msg 3105, Level 16, State 4:

Server 'SID', Line 1:

Data on dump will not fit into current database. Need 233472 Mbyte databas

How to solve this.:

Can i delete datafile of sapdata1,2,3,and as we do in Oracle.

Will it work here?

Regards

[Former Member]

Hi Adil,

In my opinion, this is a document which should be read by anyone who is dealing with Sybase ASE:

Getting Started with SAP Applications Using SAP Adaptive Server Enterprise

On this specific topic, there are few notes that will offer you basically a step-by-step guide to create a backup strategy and restoration procedure:

1585981 - SYB: Ensuring Recoverability for Sybase ASE - Please be carefull about parameters configuration, especially 'trunc log on chkpt', which I noticed is enabled in your case...

sp_ddlgen 'database', NRD command should help, but the best approach would be to use (it will offer you the dboptions, which are extremelly important):

load database NRD with listonly = 'CREATE_SQL'

go

, as stated by Mark and confirmed by Bret , according to:

1611715 - SYB: How to restore a Sybase ASE database server
(Windows)

1618817 - SYB: How to restore a Sybase ASE database server
(UNIX)

Based on the given information, you should now be able to solve your issue.

Kind Regards,

Ionut

[Former Member]

Hi Bret,

I follow these step:-

1.delete datafiles from /sybase/NRD/sapdata1/NRD_data_001.dat

repeat same for all four datafile.

isql -Usapsa  -SNRD

use master

go

2.drop database NRD

3.execute this command:-

disk init name='NRD_data_001', physname='/sybase/NRD/sapdata1/NRD_data_001', size ='90000M', directio='true'

disk init name='NRD_data_002', physname='/sybase/NRD/sapdata2/NRD_data_002', size ='80000M', directio='true'

disk init name='NRD_data_008', physname='/sybase/NRD/sapdata3/NRD_data_008', size ='90000M', directio='true'

disk init name='NRD_data_009', physname='/sybase/NRD/sapdata4/NRD_data_009', size ='90000M',

error giving:- NRD_data_001 already exsist.But i have already deleted it.

pls suggest

Regards

Adil

[Former Member]

Dear Bret,

Thnx for reply!!!

Here i am pasting o/p sp_helpdb of target system.

1> sp_helpdb

2> go

name

         db_size

         owner                dbid

         created

         durability

         lobcomplvl

         inrowlen

         status                                                                                                                                                                                                                                                                                                                                                                                                                                                              

NRD

           161792.0 MB

         sapsa                    4

         Feb 16, 2014

         full

                100

             2000

         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran

         log full, allow wide dol rows, deferred table allocation, page compr

         ion, deallocate first text page,full logging for all                                                                                                                                                                                                                                                      

master

              248.0 MB

         sa                       1

         Feb 16, 2014

         full

                  0

             NULL

         mixed log and data, allow wide dol rows                                                                                                                                                                                                                                                                                                                                                                                                                             

model

               24.0 MB

         sa                       3

         Feb 16, 2014

         full

                  0

             NULL

         mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                                                  

saptempdb

             2048.0 MB

         sa                       6

         Feb 20, 2014

         no_recovery

                  0

             NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, allow

         lls by default, abort tran on log full, mixed log and data, user cre

         d temp db, allow wide dol rows                                                                                                                                                                                                                                                                            

saptools

             2252.0 MB

         sapsa                    5

         Feb 16, 2014

         full

                  0

             NULL

         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran

         log full, allow wide dol rows,full logging for all                                                                                                                                                                                                                                                                                                                                     

sybmgmtdb

              148.0 MB

         sa                   31515

         Feb 16, 2014

         full

                  0

             NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                

sybsystemdb

               48.0 MB

         sa                   31513

         Feb 16, 2014

         full

                  0

             NULL

         mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                                                  

sybsystemprocs

              200.0 MB

         sa                   31514

         Feb 16, 2014

         full

                  0

             NULL

         trunc log on chkpt, mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                              

tempdb

             1048.0 MB

         sa                       2

         Feb 20, 2014

         no_recovery

                  0

             NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, allow

         lls by default, abort tran on log full, mixed log and data, allow wi

         dol rows                                                                                                                                                                                                                                                                                                  

(1 row affected)

[Former Member]

Hi ,

I m pasting o/p of source system also :-

2> go

name

         db_size

         owner                dbid

         created

         durability

         lobcomplvl

         inrowlen

         status                                                                                                                                                                                                                                                                                                                                                                                                                                                             ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NRD

           233472.0 MB

         sapsa                    4

         Jan 13, 2014

         full

                100

             2000

         ddl in tran, allow nulls by default, abort tran on log full, allow wide

          dol rows, deferred table allocation, page compression, deallocate firs

         t text page,full logging for all                                                                                                                                                                                                                                                                                      

master

              248.0 MB

         sa                       1

         Jan 13, 2014

         full

                  0

             NULL

         mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

model

               24.0 MB

         sa                       3

         Jan 13, 2014

         full

                  0

             NULL

         mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

saptempdb

             2048.0 MB

         sa                       6

         Jan 26, 2014

         no_recovery

                  0

             NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, allow nu

         lls by default, abort tran on log full, mixed log and data, user create

         d temp db, allow wide dol rows                                                                                                                                                                                                                                                                                        

saptools

             2252.0 MB

         sapsa                    5

         Jan 13, 2014

         full

                100

             2000

         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran on

         log full, allow wide dol rows,full logging for all                                                                                                                                                                                                                                                                                                                                                    

sybmgmtdb

              148.0 MB

         sa                   31515

         Jan 13, 2014

         full

                  0

             NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                  

sybsystemdb

               48.0 MB

         sa                   31513

         Jan 13, 2014

         full

                  0

             NULL

         mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

sybsystemprocs

              200.0 MB

         sa                   31514

         Jan 13, 2014

         full

                  0

             NULL

         trunc log on chkpt, mixed log and data                                                                                                                                                                                                                                                                                                                                                                                                                                                

tempdb

             1048.0 MB

         sa                       2

         Jan 26, 2014

         no_recovery

                  0

             NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, allow nu

         lls by default, abort tran on log full, mixed log and data, allow wide

         dol rows                                                                                                                                                                                                                                                                                                              

(1 row affected)

(return status = 0)

[former_member188958]

Well, your source output shows the size of  NRD as

NRD

           233472.0 MB

which matches the error you get when you try to load its dump into the destination

"Data on dump will not fit into current database. Need 233472 Mbyte database"

Your output on destination shows the NRD database is much smaller:

NRD

           161792.0 MB

So you either didn't create NRD on destination using the script generated by list_only=create_sql, or some of your devices don't have enough free space

on them to create the database as specified.  (a quirk of the CREATE and ALTER database commands is that if you ask for a certain amount of space and there is some space but not as much as you are asking for, ASE will allocate the available space but not raise an error - you have to be a little sharp-eyed to see that the size it reports back is smaller than what you actually asked for.).

To get the dump to load, the destination NRD will need to be enlarged to 244472 MB in size.

-bret

[former_member188958]

Typo on my last line there, that should be 233472 MB.

[Former Member]

Hi Bret,

So how i can start restoration.What i have to do now?How can i make size equivalent or bigger that source one.

Regards

[former_member188958]

In general, either use ALTER DATABASE to add more space to the database, or completely drop and rebuild it.

If you don't have enough room on the existing devices, you can create new devices using

DISK INIT or possibly enlarge existing devices using DISK RESIZE.