on 11-16-2015 5:29 AM
Dear all,
I have one doubt. we are taking backup of our sybase database using isql editor with command
isql -Usapsa -SSID -Ppassword -X
use KEP
go
dumpdatabase SID to "/db/....dmp"
go
with this command is everything will backed up or else can i take backup separately for transaction logs tempdb etc..i am new to sybase please
guide me regarding on this. Is it possible to completely restore the database with the above taken database backup using the above commands.
Best regards,
venkat.
Full database dump includes the transactions up to the point the database backup runs.
Dump transaction releases transaction log space used by committed transactions.
"truncating transaction log" takes place only via "dump transaction" command.
If dump transaction is not run the available transaction logs space keeps reducing till it gets full and then no further transaction can take place for that database.
Bottom line
dump database is full database backup, but does not release transaction log space
dump transaction is needed to restore transactions after the last full backup up.
HTH
Avinash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Venkata,
additionally to the others accurate comments: the backuped databae is *only* the one you speficied on the dump command, that is, you will have a dump command done for each database.
Besides that, the tempdb is not required to be dumped, neither its transaction log. Tempdb is recreated each time you restart your ASE. The "tempdb" name stands for "temporary database", and contains temporary tables internally generated and session-specific. So a dump won't be useful.
You may check those SAP Notes:
1585981 - SYB: Ensuring Recoverability for SAP ASE
1588316 - SYB: Configure automatic database and log backups
you'll see there that it is recommended to use a dump configurations, then the dump database or dump transaction will use them (and with an easier command syntax ).
1801984 - SYB: Automated management of long running transactions this explains how to create a threshold for transaction log to prevent it to be fulfilled.
Best regards,
Victoria.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Venkata,
You will this mentioned in the notes listed by Victoria. But if you need the archive logs for point in time restore you will have enable this to be backed up.
use master
go
sp_dboption SID, 'trunc. log on chkpt',false
go
Then you will need to do your dump configurations with your specific backup locations specific and naming conventions specified i.e.
Take note to substitute SID with your sid. And to amend stripe dir location as applicable on your system.
1> sp_config_dump @config_name='SIDDB',
2> @stripe_dir ='D:\Backup\Database',
3> @compression = '101',
4> @verify = 'header'
5> go
The change is completed. The option is dynamic and ASE need not be rebooted for
the change to take effect.
(return status = 0)
1> sp_config_dump @config_name='SIDLOG',
2> @stripe_dir ='B:\Log_Archives',
3> @compression = '101',
4> @verify = 'header'
5> go
On your production systems also set:
'full logging for all', 'true'
'enforce dump tran sequence', 'true'
Also as mentioned by 1801984 you can look at a document I created, this process triggers archive log backups in the database by using thresholds specificied on page limits.
http://scn.sap.com/docs/DOC-65767
In addition to this if you ATM setup and autoexstension you also need to run dumps of the databases as follows:
<DBSID> database (SAP database)
master database
sybsystemprocs database (a restore is required when automatic database expansion has been set
up)
Also check recommendation in note especially in relation with the database parameters.
1611715 - SYB: How to restore an SAP ASE database server
As victoria mentioned for each of the databases you need backed up you will have to create a seperate job in dbacockpit schedule.
Johan
Hello Sudheer,
With the above command everything will be backed up. Until unless during this backup duration no transactions are happening.
Usually transactional backups are useful when backup is old one(may be one old) and current transactions are backed up using transactional backup. In this case you will need both complete backup as well as transactional backup.
With above explained scenario you will be able to restore complete system.
Regards
Anand
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.