Problem in doing a backup of database on SQL server through Java code

[bhagat]

Problem in doing a backup of database on SQL server through Java code
using jdbc


Statement callBackupDbase = con.createStatement();


String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";


if(callBackupDbase != null){
callBackupDbase.execute(dbackup);



}


I get the following error

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
backup or restore operation within a transaction.


Could anyone help me with that


Bhagat

 

[Roedy Green]

String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";

There is a class called BatchUpdateException. If you can find out
what calls it, that will be a clue as to how you are supposed to
submit that command.

In the meantime try submitting it from the ad hoc query tool that
came with the database.

 

[steve]

There is a class called BatchUpdateException. If you can find out
what calls it, that will be a clue as to how you are supposed to
submit that command.

In the meantime try submitting it from the ad hoc query tool that
came with the database.

it is very simple!!, when you backup the database , normally you do the
command from the command line, it is not a transaction, as it has to be
outside the transactions , so that a snapshot of the database can be made.
( unless the database is shutdown , with users disconnected.)

as you are submitting the command from a program, you are creating a
transaction, how can you backup a database , that is doing a backup from
within a transaction?

it is like trying to format your c: drive from inside windows. ( you cannot
get a lock on the resources)

you way round it would be to use a batch file ( external to the database),
triggered by the java program.


steve

 

[Dave Glasser]

Problem in doing a backup of database on SQL server through Java code
using jdbc


Statement callBackupDbase = con.createStatement();


String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";


if(callBackupDbase != null){
callBackupDbase.execute(dbackup);



}


I get the following error

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
backup or restore operation within a transaction.


Could anyone help me with that

Try calling:

con.setAutoCommit(true);

before calling con.createStatement. That should insure that there is
no transaction opened.

However, autocommit is supposed to be the default mode for a
connection when it's created. Are you perhaps running this code inside
an application server that created the connection for you?


--
Check out QueryForm, a free, open source, Java/Swing-based
front end for relational databases.

http://qform.sourceforge.net

If you're a musician, check out RPitch Relative Pitch
Ear Training Software.

http://rpitch.sourceforge.net

 

[steve]

Problem in doing a backup of database on SQL server through Java code
using jdbc


Statement callBackupDbase = con.createStatement();


String dbackup = "BACKUP DATABASE databaseName TO DISK = 'Path for the
backup file";


if(callBackupDbase != null){
callBackupDbase.execute(dbackup);



}


I get the following error

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform a
backup or restore operation within a transaction.


Could anyone help me with that

Try calling:

con.setAutoCommit(true);

before calling con.createStatement. That should insure that there is
no transaction opened.

However, autocommit is supposed to be the default mode for a
connection when it's created. Are you perhaps running this code inside
an application server that created the connection for you?

you are still inside a transaction, commit only allows other users to see
your changes to a database.( makes the changes perm.)

you issue a backup inside a transaction,

the transaction starts.
you issue backup command
the database subroutines to the backup,
backup checks status of connection, sees you are INSIDE a transaction (your
waiting to return from the backup subroutine)
database returns an error.
returns back to your calling routine
then you try to issue a commit.


even with oracle , you have to run 'rman', which can handle active
transactions & connections whilst it is backing up you database.


a solution , though not a good one, would be to write a file from inside the
database, then have a daemon running outside the database , checking for the
existence of that file. then launching the backup solution.