java对mssql数据库的备份和恢复

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import java.sql.Types; import java.util.logging.Level; import java.util.logging.Logger; /** * * @author Msir */ public class BackupRestore{ /*str的值只能是backup或者restore，把该值传入存储过程作为判断是备份还是恢复*/ public void backupres(Connection conn,String str) throws SQLException{ CallableStatement st = null; st = conn.prepareCall("{call DBbackup(?,?,?,?,?,?,?)}");//存储过程 st.setInt(1, 0); st.setString(2, "D://back//llp");//不包含文件后缀名的路径 st.setInt(3, 0); st.setInt(4, 1); st.setInt(5, 0); st.setString(6, str); st.registerOutParameter(7, Types.VARCHAR); st.execute(); String out=st.getString(7); System.out.println(out); } public static void main(String[] args){ Connection conn = null; BackupRestore br=new BackupRestore(); try { conn = DB.getMsConn("localhost");//获取数据库连接，在这里对数据库llp备份和恢复，为了避免恢复出错，连接到非llp库 } catch (SQLException ex) { Logger.getLogger(BackupRestore.class.getName()).log(Level.SEVERE, null, ex); } if(conn!=null) System.out.println("已连接！"); try { br.backupres(conn, "restore"); } catch (SQLException ex) { Logger.getLogger(BackupRestore.class.getName()).log(Level.SEVERE, null, ex); } } } 

java对数据库的备份和恢复要用到的数据库连接方法DB.getMsConn：

public final class DB { private static Connection access; public static Connection getMsConn(String ip) throws SQLException{ Connection conn=null; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn= DriverManager.getConnection("jdbc:jtds:sqlserver://" + ip + ":1433/master;user=sa;password=sa"); } catch (SQLException ex) { throw new SQLException("无法连接远程服务器！可能是由下列原因导致该错误的发生：/r/n/r/n" + "1、网络连接中断；/r/n" + "2、远程数据库已被停止服务；/r/n" + "3、数据库配置错误；/r/n" + "4、传递了错误的连接参数；/r/n" + "5、数据库结构已被破坏",ex); }catch (ClassNotFoundException ex) { HLogger.log(ex, "获取远程数据库驱动"); } return conn; } }

用到的存储过程(该存储过程由网上收集+个人修改):

 

  CREATE       PROCEDURE     DBbackup     
                      @bakequip         int,                   --     备份设备：磁盘&磁带     
                      @bakpath           varchar(50),   --     带全路径的备份文件名     
                      @baktype           int,                   --     完全备份&增量备份     
                      @baklog             int,                   --     ‘0’备份日志     
                      @bakdb               int,                   --     ‘0’备份数据库     
                      @kind     varchar(7),                 --备份还是恢复     
      @retmsg     varchar(20)     out          --返回信息     
  AS     
        DECLARE     @DevName_data         varchar(50)     
        DECLARE     @DevName_log       varchar(50) 
        DECLARE   @db_path   varchar(100) 
        DECLARE   @log_path   varchar(100) 
   SELECT        @DevName_data  =@bakpath +  '.dat' 
   SELECT        @DevName_log  = @bakpath   +  'log.dat'
  SELECT         @db_path = @bakpath +  '.dat'     
  SELECT         @log_path= @bakpath   +  'log.dat'
        DBCC     CHECKDB(llp)     
  IF NOT  EXISTS (SELECT * FROM sysdevices WHERE NAME=@DevName_log OR NAME=@DevName_data)
        BEGIN
        EXEC   sp_addumpdevice   'disk',   @DevName_data,@db_path 
        EXEC   sp_addumpdevice   'disk',   @DevName_log,@log_path 
 IF     @kind='backup'     
        BEGIN     
                IF     @bakequip=0     
                BEGIN     
                        IF     @baktype=0     
                        BEGIN     
                                IF     @bakdb=0     
                                BEGIN         
                                        BACKUP     DATABASE     llp     TO     DISK=@Devname_data     
                                        WITH     INIT     
                                END     
                                IF     @baklog=0     
                                BEGIN                             
                                        BACKUP     LOG     llp     WITH     NO_LOG                             
                                        BACKUP     LOG     llp     TO     DISK=@DevName_log   
                                        WITH     INIT,NO_TRUNCATE     
                                END     
                        END     
                        ELSE     BEGIN     
                                IF     @bakdb=0     
                                BEGIN     
                                        BACKUP     DATABASE     llp     TO     DISK=@DevName_data 
                                        WITH     NOINIT     
                                END     
                                IF     @baklog=0     
                                BEGIN     
                                        BACKUP     LOG     llp     WITH     NO_LOG                             
                                        BACKUP     LOG     llp     TO     DISK=@DevName_log   
                                        WITH     NOINIT,NO_TRUNCATE     
                                END     
                        END                     
                END     
                SET  @retmsg='数据库备份成功！'     
        END     
        IF     @kind='restore'             
        BEGIN     
                RESTORE DATABASE llp  FROM  DISK=@DevName_data  WITH REPLACE     
                SET @retmsg='恢复数据库成功！'     
        END     
END
ELSE
  BEGIN 
  EXEC   SP_DropDevice   @Devname_data 
  EXEC   sp_dropdevice   @devname_log 
   EXEC   sp_addumpdevice   'disk',   @DevName_data,@db_path 
  EXEC   sp_addumpdevice   'disk',   @DevName_log,@log_path
        IF     @kind='backup'     
        BEGIN     
                IF     @bakequip=0     
                BEGIN     
                        IF     @baktype=0     
                        BEGIN     
                                IF     @bakdb=0     
                                BEGIN         
                                        BACKUP     DATABASE     llp     TO     DISK=@Devname_data     
                                        WITH     INIT     
                                END     
                                IF     @baklog=0     
                                BEGIN                             
                                        BACKUP     LOG     llp     WITH     NO_LOG                             
                                        BACKUP     LOG     llp     TO     DISK=@DevName_log   
                                        WITH     INIT,NO_TRUNCATE     
                                END     
                        END     
                        ELSE     BEGIN     
                                IF     @bakdb=0     
                                BEGIN     
                                        BACKUP     DATABASE     llp     TO     DISK=@DevName_data 
                                        WITH     NOINIT     
                                END     
                                IF     @baklog=0     
                                BEGIN     
                                        BACKUP     LOG     llp     WITH     NO_LOG                             
                                        BACKUP     LOG     llp     TO     DISK=@DevName_log   
                                        WITH     NOINIT,NO_TRUNCATE     
                                END     
                        END                     
                END     
                SET  @retmsg='数据库备份成功！'     
        END     
        IF     @kind='restore'             
        BEGIN     
    DECLARE hCForEach CURSOR FOR
    SELECT s='kill '+CAST(spid AS VARCHAR) FROM master..sysprocesses
    WHERE dbid=DB_ID('llp')
    EXEC sp_msforeach_worker '?'
                RESTORE DATABASE llp  FROM  DISK=@DevName_data  WITH REPLACE     
                SET @retmsg='恢复数据库成功！'     
        END     
END
        RETURN     0
GO