Dynamically specifying dump device name

[Larry Coon]

ASE 15.0.2 on Linux.

For years we've been dumping directly to tape. We're
now changing to disk dumps, so it'll complete faster
and we'll have handy access to the last few days' dumps
(and we'll use O/S backups to save these dumps to tape).

We run backups using shell scripts initiated by cron.

With tape backups, since the media changed daily, the
dump commands never had to change. It was always:

dump database db1 to "tapedevicename" with init
dump database db2 to "tapedevicename"
dump database db3 to "tapedevicename" with unload

But now that we're dumping to disk, with multiple
nights' backups coexisting, I need to make sure it
uses different file names every night. So today's
backups would be:

dump database db1 to "/nfsmnt/asebk/db1_20080117"
dump database db2 to "/nfsmnt/asebk/db2_20080117"
dump database db3 to "/nfsmnt/asebk/db3_20080117"

And tomorrow's would be:

dump database db1 to "/nfsmnt/asebk/db1_20080118"
dump database db2 to "/nfsmnt/asebk/db2_20080118"
dump database db3 to "/nfsmnt/asebk/db3_20080118"

(Or whatever naming convention I decide on.)

My default is to do this as a two-step process:
1. Dump the database using a standard name, eg:
dump database db1 to "/nfsmnt/asebk/db1"
2. Rename the file after the dump is completed.
(Since this would be done in the shell script, I
have more flexibility.)

However, I'd rather do it in one step if I can. Is
there a clever way to dynamically specify the name of
the dump device in a dump database command? Or is
there an alternative method that others use which I
haven't thought of?

Thanks for any advice.


Larry Coon
University of California

[Manish Negandhi]

Post by Larry Coon
However, I'd rather do it in one step if I can. Is
there a clever way to dynamically specify the name of
the dump device in a dump database command? Or is
there an alternative method that others use which I
haven't thought of?

Yes, that's possible, all you need is a mechanism to generate file
name dynamically and combine it with isql. Here is how you can do it
in a Unix script, try it out !


DATE=`date | nawk '{print $2$3$4$6}'`
isql -Usa -S$servername -P$password << EOF > yourfilename.out
dump database dbname to '/folder1/folder2/dbname_$DATE.dump'
go
EOF

[Manish Negandhi]

However, I'd rather do it in one step if I can.  Is
there a clever way to dynamically specify the name of
the dump device in a dump database command?  Or is
there an alternative method that others use which I
haven't thought of?

Yes, that's possible, all you need is a mechanism to generate file
name dynamically and combine it with isql. Here is how you can do it
in a Unix script, try it out !

DATE=`date | nawk '{print $2$3$4$6}'`
isql -Usa -S$servername -P$password << EOF > yourfilename.out
dump database dbname to '/folder1/folder2/dbname_$DATE.dump'
go
EOF

-HTH
Manish Negandhi
[TeamSybase Intern]

[Sherlock, Kevin]

Larry,
just write a shell script to do what you want:

Eg:
--------- cut here ----------
#!/bin/ksh
dump_dir="/nfsmnt/asebk"
date_ext=$( date +%Y%m%d )
for dbname in db1 db2 db3
do
# stripe1="${dbname}_s1" ; stripe2="${dbname}_s2"
# stripe3="${dbname}_s3" ; stripe4="${dbname}_s4"
$SYBASE/$SYBASE_OCS/bin/isql -U... -P... -S.... << EOF
use master
go
dump database $dbname to "${dump_dir}/${dbname}_${date_ext}"
go
EOF
done
--------- cut here --------

I didn't test the above, so correct any errors that I've made. Note that
you can adjust the script to dump to stripes (i've left comments in for
naming them), add compression, etc.

Post by Larry Coon
ASE 15.0.2 on Linux.
For years we've been dumping directly to tape. We're
now changing to disk dumps, so it'll complete faster
and we'll have handy access to the last few days' dumps
(and we'll use O/S backups to save these dumps to tape).
We run backups using shell scripts initiated by cron.
With tape backups, since the media changed daily, the
dump database db1 to "tapedevicename" with init
dump database db2 to "tapedevicename"
dump database db3 to "tapedevicename" with unload
But now that we're dumping to disk, with multiple
nights' backups coexisting, I need to make sure it
uses different file names every night. So today's
dump database db1 to "/nfsmnt/asebk/db1_20080117"
dump database db2 to "/nfsmnt/asebk/db2_20080117"
dump database db3 to "/nfsmnt/asebk/db3_20080117"
dump database db1 to "/nfsmnt/asebk/db1_20080118"
dump database db2 to "/nfsmnt/asebk/db2_20080118"
dump database db3 to "/nfsmnt/asebk/db3_20080118"
(Or whatever naming convention I decide on.)
dump database db1 to "/nfsmnt/asebk/db1"
2. Rename the file after the dump is completed.
(Since this would be done in the shell script, I
have more flexibility.)
However, I'd rather do it in one step if I can. Is
there a clever way to dynamically specify the name of
the dump device in a dump database command? Or is
there an alternative method that others use which I
haven't thought of?
Thanks for any advice.
Larry Coon
University of California

[Mark A. Parsons]

Consider the following which will dump a db or log:

dump database @dbname to @dump_path
dump transaction @dbname to @dump_path

[You could also build a string(s) and submit to exec().]

Guess what's in @dump_path? Anything you want, to include multiple stripes and 'with compression = #'.

Soooo, you could write a stored proc which performs your dumps (db or log) for you to include allowing for striping,
compression, generation of datetime suffixes, database vs log, and even use different dump directories.

You could pass several configuration parameters into the stored proc or have them defined in a 'config' table.

The nice thing about putting all of this logic into a stored proc ... you can use the stored proc to perform
threshold-initiated log dumps.

Post by Larry Coon
ASE 15.0.2 on Linux.
For years we've been dumping directly to tape. We're
now changing to disk dumps, so it'll complete faster
and we'll have handy access to the last few days' dumps
(and we'll use O/S backups to save these dumps to tape).
We run backups using shell scripts initiated by cron.
With tape backups, since the media changed daily, the
dump database db1 to "tapedevicename" with init
dump database db2 to "tapedevicename"
dump database db3 to "tapedevicename" with unload
But now that we're dumping to disk, with multiple
nights' backups coexisting, I need to make sure it
uses different file names every night. So today's
dump database db1 to "/nfsmnt/asebk/db1_20080117"
dump database db2 to "/nfsmnt/asebk/db2_20080117"
dump database db3 to "/nfsmnt/asebk/db3_20080117"
dump database db1 to "/nfsmnt/asebk/db1_20080118"
dump database db2 to "/nfsmnt/asebk/db2_20080118"
dump database db3 to "/nfsmnt/asebk/db3_20080118"
(Or whatever naming convention I decide on.)
dump database db1 to "/nfsmnt/asebk/db1"
2. Rename the file after the dump is completed.
(Since this would be done in the shell script, I
have more flexibility.)
However, I'd rather do it in one step if I can. Is
there a clever way to dynamically specify the name of
the dump device in a dump database command? Or is
there an alternative method that others use which I
haven't thought of?
Thanks for any advice.
Larry Coon
University of California

[A. M.]

Post by Larry Coon
But now that we're dumping to disk, with multiple
nights' backups coexisting, I need to make sure it
uses different file names every night. So today's
dump database db1 to "/nfsmnt/asebk/db1_20080117"
dump database db2 to "/nfsmnt/asebk/db2_20080117"
dump database db3 to "/nfsmnt/asebk/db3_20080117"
dump database db1 to "/nfsmnt/asebk/db1_20080118"
dump database db2 to "/nfsmnt/asebk/db2_20080118"
dump database db3 to "/nfsmnt/asebk/db3_20080118"
(Or whatever naming convention I decide on.)

So what's the problem?

Post by Larry Coon
dump database db1 to "/nfsmnt/asebk/db1"
2. Rename the file after the dump is completed.
(Since this would be done in the shell script, I
have more flexibility.)
However, I'd rather do it in one step if I can. Is
there a clever way to dynamically specify the name of
the dump device in a dump database command? Or is
there an alternative method that others use which I
haven't thought of?

Since you want do the dump within a shell
script, its trivial. I've appended the core
of one I wrote recently. This is written in
standard POSIX shell. It should also work
with Korn shell. Not sure about bash. You'll
need to flesh the rest of it out for yourself.

Note 1: this uses the old syle compression. Use
the newer "with compression = " if you wish.

Note 2: shell scripts are the lowest common
denominator on any Unix system but aren't always
the best for everything. Try using Perl instead
if you can.

-am © MMVIII

...

if [ $COMPRESS -eq 1 ]
then
CSTR="compress::$LEVEL::"
CEND=.C$LEVEL
else
CSTR=""
CEND=""
fi

# File sizes are limited to 32GB on PH-UX.
getstripes(){
sql <<- ! | sed -n -e '/return status/d' -e '/^[- ]*$/d' -e '/ affected)
/d' -e 's/ //g' -e p
declare @pgspmb float

select @pgspmb = 1048576.0 / low
from master..spt_values
where number = 1
and type = "E"

select convert(int, ceiling((sum(u.size) / @pgspmb) / 32768.0))
from master..sysdatabases d,
master..sysusages u
where d.dbid = db_id('$1')
and u.dbid = d.dbid
group by d.dbid
go
!
}

processdb(){
STRIPES=`getstripes $1`
DUMPFILE=$CSTR$DUMPDIR/$1.db_dump.$DATE$CEND

if [ $TRUNCATE -eq 1 ]
then
echo "dump transaction $1 with truncate_only"
echo go
fi

if [ $STRIPES -eq 1 ]
then
echo "dump database $1 to '$DUMPFILE'"
else
echo "dump database $1 to '$DUMPFILE.S01'"
let NUM=2
while [ $STRIPES -gt 1 ]
do
if [ $NUM -lt 10 ]
then
echo "stripe on '$DUMPFILE.S0$NUM'"
else
echo "stripe on '$DUMPFILE.S$NUM'"
fi
let STRIPES=STRIPES-1
let NUM=NUM+1
done
fi
echo go
}

# Main routine.
{
echo `date` "Database dump of $SERVER started"

for DB in $DBLIST
do
echo `date` $DB
processdb $DB | sql -e
done

echo `date` "Database dump of $SERVER finished"
} > $LOG

...

[Eugene Korolkov]

Larry,

1. www.edbarlow.com
2. pp 312.-340 from the book by Ryan Putnam "Introductory Korn Shell
programming with Sybase ASE utilities"

They both have good archive systems, not just particular scripts.
Actually I think that Sybase should have something like Oracle's
'archivelog' option long time ago,
so that DBA should not be responsible for that at all.

HTH,
Eugene

Post by Larry Coon
ASE 15.0.2 on Linux.
For years we've been dumping directly to tape. We're
now changing to disk dumps, so it'll complete faster
and we'll have handy access to the last few days' dumps
(and we'll use O/S backups to save these dumps to tape).
We run backups using shell scripts initiated by cron.
With tape backups, since the media changed daily, the
dump database db1 to "tapedevicename" with init
dump database db2 to "tapedevicename"
dump database db3 to "tapedevicename" with unload
But now that we're dumping to disk, with multiple
nights' backups coexisting, I need to make sure it
uses different file names every night. So today's
dump database db1 to "/nfsmnt/asebk/db1_20080117"
dump database db2 to "/nfsmnt/asebk/db2_20080117"
dump database db3 to "/nfsmnt/asebk/db3_20080117"
dump database db1 to "/nfsmnt/asebk/db1_20080118"
dump database db2 to "/nfsmnt/asebk/db2_20080118"
dump database db3 to "/nfsmnt/asebk/db3_20080118"
(Or whatever naming convention I decide on.)
dump database db1 to "/nfsmnt/asebk/db1"
2. Rename the file after the dump is completed.
(Since this would be done in the shell script, I
have more flexibility.)
However, I'd rather do it in one step if I can. Is
there a clever way to dynamically specify the name of
the dump device in a dump database command? Or is
there an alternative method that others use which I
haven't thought of?
Thanks for any advice.
Larry Coon
University of California