Backup and Recovery

Backup & Recovery

Difference between Roles and Groups

• Roles have server level permission which are assigned to logins
• Groups have database level access ,permission ,users added to these groups

Backup & Recovery Strategies

Backup Procedure we use two types : dump database (or) dump transaction

Recovery Procedure we use two types : load database (or) load transaction

dumpdb (differential backup)

Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database. Dumps and loads are performed through Backup Server.

The target platform of a load database operation need not be the same platform as the source platform where the dump database operation occurred. dump database and load database are performed from either a big endian platform to a little endian platform, or from a little endian platform to a big endian platform.

• A small company that has minimal transaction go for whole database dump here data and log can be mixed for database
• It is most important as a DBA to take db dumps at regular point of interval ,so that it will be helpful to recover the db at the maximum
• The difference points where there is failure condition like server has been shutdown improperly and db has got corrupted here we need to recover database
• When dump database is given it takes the full dbdump which is data and log mixed
• Whenever we take a full db dump the log will not be truncated

Syntax dump database <dbname> to <physical path >

Example dump database syb01 to “/$ Sybase/dumps/db_name.dat”

dump transaction

• Mostly used for large companies which has many transactions for example Banking sector → here data and log should be separated
• When we issue this command the dboption truncate log on check point should not be enabled then this command works
• Here only the transaction log dump happens when we take backup

Syntax dump transaction <dbname> to <physical path >

Example dump transaction syb01 to “/$ Sybase/dumps/db_name_transactiondump”

Failure conditions when these commands fail

1. Check whether backup servers may not be running
2. Need to check the permission on the directory where the dumps are taken place (i.e. write permission )
3. There might be done minimally logged operation
4. The file system may not have space to dump

• For 3^rd Failure 1^st we need to take full dbdump and the transaction dump
• When a full database dump is running we will not be able to take transactional dump

Compressed Backups

• When we use this option while taking dump the db size will be compressed and the backups has been taken
• Compression levels
  • 0 → No Compression
  • 1 → Default Compression
  • 9 → MAX Compression(Not recommended because leads to corruption of database )

When we use this option the performance of dump and load will be high because the database needs to be compressed and take the backups

When we use this option the server performance will be low while dumping/loading database because the server needs to compress database size and then take the backup.

Similarly the database dump needs to be uncompressed and load it back (here server performance will be low).

Syntax & Example

1. dump database <dbname> to “device name”

dump database pubs2 to “opt/sybase/dump/pubs2.dat”

2. dump database <dbname> to “compress::level::/..../.../.../.../.cmp”

dump database pubse2 to “compress::2:: /opt/sybase/dump/pubs.dat”

3. dump database <dbname> to “compress::level::/..../.../.../.../.cmp”

stripe on “compress::level::/..../.../.../.../.cmp2”

stripe on “compress::level::/..../.../.../.../.cmp3”

dump database pubse2 to “compress::2:: /opt/sybase/dump/pubs.dat”

stripe on “compress::2::/opt/sybase/dump/pubs.dat.cmp2”

stripe on “compress::2::/opt/sybase/dump/pubs.dat.cmp3”

Stripe on and Multiple Stripes

When we use this option the dbdump can be taken on multiple files and devices

• use default compression level
• Maximum no of stripes is 1024 stripes

Syntax

dump database dbname to “<physical path>”

stripe on “/path/s1”

stripe on “/path/s2”

go{For without Compression}

A full dbdump has 3 phases

• Phase 1 → Flush all data pages
• Phase 2 → Scans the data pages
• Phase 3 → Flushes log segment pages

*** Transactional dump files only log pages

Transactional Dumps:

Dump Tran with truncate only

We can use with truncate only option inorder to clear the log without taking the transaction log dump .we will use thisoption when the transaction log is full and the db option truncate log on chhkpoint is enabled

Example Error 1105 → gives dbname /segment name ,throws an that log got filled

Checkpoint :: It writes what all transactions are been committed to physical devices

Dump tran with no log

This option is used in order to clear the transactional log .when we use this option it will clear both inactive and active transaction's.

It is not recommended by sybase to use this option multiple no of times because this may lead to corruption of the data .

Dump Tran with no truncate

This option is used for up-to the minute of recovery when a database is completed and needs to be recovered. In order to use this option it is must important that the data and the log should be on separate devices.

When option truncate_log & no_log are not working and if it is temporary database then go for

Select lct_admin(“abort”,0,2) → 0 represents All Transactions

→ 2 represents TEMPDB

Load Database :

When ever we want to recover a database or we want to load the refresh production data onto the development or the UAT servers

We will use the Load db command

Minimum servers required for a setup

• Production server
• Pre-production server
• Development server
• UAT

Database refresh

Taking the latest dumps from production and loading it onto the development / UAT / Pre production is called db refresh .

Precautions before loading the db

• check whether loading database is not production server.
• The database size should match with the production server database size.
• Take BCP of systables I.E sysusers and sysalterantes in order to retain the permission which is having before the load.
• No users are connected to the database.

Syntax

load database <dbname> from “path of the dump”

load database <dbname> from “path of the dump”

load transaction

After issuing those commands sybase server makes database offline so we need to change to online

Online database <dbname>

creating a database with for load option

Syntax

Create database <dbname>

-----------------------------

-----------------------------

for load

go