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

Discussion in 'Java' started by bhagat, Oct 3, 2005.

  1. bhagat

    bhagat Guest

    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
     
    bhagat, Oct 3, 2005
    #1
    1. Advertising

  2. bhagat

    Roedy Green Guest

    On 3 Oct 2005 09:00:24 -0700, "bhagat" <>
    wrote or quoted :

    >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.
    --
    Canadian Mind Products, Roedy Green.
    http://mindprod.com Again taking new Java programming contracts.
     
    Roedy Green, Oct 3, 2005
    #2
    1. Advertising

  3. bhagat

    steve Guest

    On Tue, 4 Oct 2005 00:39:59 +0800, Roedy Green wrote
    (in article <>):

    > On 3 Oct 2005 09:00:24 -0700, "bhagat" <>
    > wrote or quoted :
    >
    >> 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.
    >


    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
     
    steve, Oct 4, 2005
    #3
  4. bhagat

    Dave Glasser Guest

    "bhagat" <> wrote on 3 Oct 2005 09:00:24 -0700
    in comp.lang.java.programmer:

    >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
     
    Dave Glasser, Oct 5, 2005
    #4
  5. bhagat

    steve Guest

    On Wed, 5 Oct 2005 10:50:56 +0800, Dave Glasser wrote
    (in article <>):

    > "bhagat" <> wrote on 3 Oct 2005 09:00:24 -0700
    > in comp.lang.java.programmer:
    >
    >> 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.
     
    steve, Oct 5, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lee
    Replies:
    5
    Views:
    598
    Terry Burns
    Feb 14, 2006
  2. VisionSet

    Re: backup database

    VisionSet, Aug 30, 2003, in forum: Java
    Replies:
    0
    Views:
    380
    VisionSet
    Aug 30, 2003
  3. gavino
    Replies:
    13
    Views:
    1,072
  4. Replies:
    2
    Views:
    526
  5. Replies:
    0
    Views:
    666
Loading...

Share This Page