Thursday, February 08, 2007

Automatically Dump the Database for Sybase

Here's a quick and dirty way of automatically backing up your Sybase database. Personally, we save a dump for each day of the week.

Ensure that you have permissions to write to the destination directory.

Create the SQL script
#vi /home/esofthub/dump.sql
1>use master
2>go
1>dump database databasename to '/raid/sybdumps/dump.dat'
2>go
:wq! (saves and quits vi)

Create the shell script
#vi /home/esofthub/dump.sh
#!/usr/bin/sh
isql -U login -P password -e < /home/esofthub/dump.sql > dump.out
:wq! (saves and quits vi)

Add entry to the crontab
#crontab -e
0 5 * * * /home/esofthub/dump.sh
:wq! (saves and quits vi)

This crontab entry invokes the dump.sh script every day at 0500.

2 comments:

Anonymous said...

This is fine but what about if I want to take database dumps every day to the same folder keeping the old dumps as is and without overwriting them. I guess you need to concatenate the %date parameter to the dump file name....right??

Can you help in this??

Many thanks

Imad
Systems & Database Administrator

esofthub said...

We do db dumps everyday in my workplace. We append a customized variation of the date command's output to the filename. Obviously, there are many ways of addressing this topic.

Refer to the find link on this blog to delete older dumps.