Restoring a database from the command line

[Chris Wood]

Hi,

Forgive me if I'm missing something here, but there doesn't seem to be a
command line alternative to the "Restore Database" wizard in Sybase Central,
i.e. a dbrestore.exe

Can anyone tell me a simple way to restore a database file and transaction
log from a backup using the command line? I'd prefer it if I didn't have to
connect to another database and use the RESTORE DATABASE syntax.

(It would be nice if there was a command of the type 'dbrestore <backup
file> <destination db file>' that could be used as a simple alternative to
the wizard.)

Thanks,

Chris Wood

[Chris Wood]

P.S. I'm using SQL Anywhere Studio 9.0.2 3044

Post by Chris Wood
Hi,
Forgive me if I'm missing something here, but there doesn't seem to be a
command line alternative to the "Restore Database" wizard in Sybase
Central, i.e. a dbrestore.exe
Can anyone tell me a simple way to restore a database file and transaction
log from a backup using the command line? I'd prefer it if I didn't have
to connect to another database and use the RESTORE DATABASE syntax.
(It would be nice if there was a command of the type 'dbrestore <backup
file> <destination db file>' that could be used as a simple alternative to
the wizard.)
Thanks,
Chris Wood

[Chris Keating(iAnywhere Solutions)]

The 'Backup Database' creates an archive image of the database. You can
also opt for an image backup which requires no additional effort if used
other than to copy the image to the desired location when reverting to
the backup.

The 'Backup Database' is executed using SQL with the statement BACKUP
DATABASE TO '<location>' WITH COMMENT '<some comment>'; and does not
have a command line equivalent. The dbbackup.exe creates an image backup
which is distinct from an archive backup.

An archive backup needs to be restored as it contains all of the files
for a backup in a single file. You need to either execute this from an
engine or from Sybase Central. For a command line option, you can
establish a DBISQL connection to the utility_db to execute the restore
statement on the archive. For more information, see the online help.
--
Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

*****************************************************************************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition =and try out the market-leading database for mobile, embedded
and small to medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

*****************************************************************************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

*****************************************************************************

[Greg Fenton]

Post by Chris Wood
Forgive me if I'm missing something here, but there doesn't seem to be a
command line alternative to the "Restore Database" wizard in Sybase Central,
i.e. a dbrestore.exe

RESTORE DATABASE (and it SC equivalent) is meant to restore a backup
database from an *archive*.

To give a full answer, we'd need to know how it is you are doing backups
(e.g. provide the dbbackup command with all its options).

But for the majority of situations, the command-line alternative you are
looking for is "copy".

In the following scenario, assume we do a full backup weekly on
Saturday, incremental backups daily, and that we need to do a recovery
on Monday. Also assume that the production .log file is intact (which
you can verify by using "dbtran" on the .log file).

First, store the production database and log (the one you are trying to
recover):

rename c:\production\ c:\production_crashed\

Second, get the full backup:

makedir c:\production\
copy d:\my_backups\saturday\my_db.db c:\production\my_db.db

Third, apply the incremental bacukps:

dbeng9 c:\production\my_db.db -a c:\my_backups\saturday\my_db.log
dbeng9 c:\production\my_db.db -a c:\my_backups\sunday\my_db.log

Fourth, apply the transaction log up to the point of failure:

dbeng9 c:\production\my_db.db -a c:\production_crashed\my_db.log


Hope this helps,
greg.fenton

[Chris Wood]

Thanks for your help guys.

It is an archive we're creating, not just an online backup of the db file
and transaction log, using an event with the following syntax (or very
similar):

begin
declare cmd long varchar;
declare day_name char(20);
declare q char(1);
set q="CHAR"(39);
set day_name=DATENAME(WEEKDAY,current date);
set cmd='BACKUP DATABASE TO ';
set cmd=cmd || q || 'E:\\DATA\\DBBackups\\PROD\\';
set cmd=cmd || day_name || '.bkp' || q || ' ATTENDED OFF';
execute immediate with escapes off cmd
end

These archives are backed up as part of the normal backup process for the
server (handled by our client, I don't know the details) and rather than add
an extra event to the database to create a backup of the db file and
transaction log at the same time as the archive is created, I wondered if
there was an easy way to restore this file to a db/log file on a periodic
basis for use in troubleshooting/testing with recent data.

It seems I will have to go with the method whereby I connect to the
utility_db and restore the archive to another location (using a Windows
scheduled task or similar). Assuming the same database server is used to
perform the restore, would the strain on the server be greater or less than
performing a second backup in the other format?

Thanks again,

Chris