備份:backup database 數據庫名字 to disk='備份到的位置' with init
還原:restore database 數據庫名字 from disk='備份到的位置' with replace
數據庫連接類
------------------------------------------------------------
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.log4j.Logger;
/**
* 數據庫備份與還原
* @author Stream
*
*/
public class DateBakDao {
/**
* 1
*/
private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String url = "jdbc:sqlserver://localhost:1413;databaseName=stream";
private static String urlrevert = "jdbc:sqlserver://localhost:1413;databaseName=master"; //還原不能用自己的數據庫,不能自己還原自己吧^_^
private static String username = "sa";
private static String password = "stream1990";
/**
* 2
*/
static {
try {
Class.forName(driver);
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 3
*/
public static Connection getCon(String type) {
Connection con = null;
try {
if(type.equals("bak")){ //因為寫一個類中,
con=DriverManager.getConnection(url,username,password);
}else{
con=DriverManager.getConnection(urlrevert,username,password);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
return con;
}
}
/**
* 4
*/
public static void closeCon(ResultSet rs,PreparedStatement ps,Connection con){
try {
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
public static void main(String[] args) {
System.out.println(getCon("bak"));
}
}
備份處理JSP
------------------------------------------------------------
<%@ page language="java" import="java.util.*,com.*,org.apache.log4j.Logger" pageEncoding="UTF-8"%>
<%@page import="java.sql.*;"%>
<%
boolean flag=true;
Connection con=null;
ResultSet rs=null;
String filePath="";
Logger logger=Logger.getLogger("doDataBak.jsp");
String value = request.getParameter("bakname");
if(value==null || value.trim().equals("")){
//為它取個名字
java.util.Date now = new java.util.Date();
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyyMMddHHmmss");
value = formatter.format(now);
}
try{
con=new DateBakDao().getCon("bak");
filePath=this.getServletConfig().getServletContext().getRealPath("/")+"admin\\bak\\"+value+".dat";
String sql="backup database stream to disk='"+filePath+"'"+" with init";
//out.print(sql);
Statement st=con.createStatement(); //這句和注意咯,別習慣成自然寫成另一句了
st.execute(sql);
logger.info("成功備份數據庫到"+filePath);
st.close();
}catch(Exception e){
flag=false;
e.printStackTrace();
logger.info("備份數據庫時出現錯誤");
}finally{
new DateBakDao().closeCon(rs,null,con);
}
if(flag==true && new java.io.File(filePath).isFile()){
out.print("<script>alert('備份成功')</script>");
out.print("數據備份在"+filePath);
}else{
out.print("<script>alert('備份失敗')</script>");
}
%>
數據庫還原處理 JSP
------------------------------------------------------------
<%@ page language="java" import="java.util.*,com.*,org.apache.log4j.Logger" pageEncoding="UTF-8"%>
<%@page import="java.sql.*;"%>
<%
String filePath=request.getParameter("path");
boolean flag=true;
Connection con=null;
ResultSet rs=null;
Logger logger=Logger.getLogger("doDataRevert.jsp");
try{
//關閉這個數據庫的所有連接
con=new DateBakDao().getCon("revert");
String killconnsql = "exec killspid 'stream' "; //這里是執行一個存儲過程,存儲過程在下面貼出
CallableStatement stt = con.prepareCall(killconnsql);
stt.execute();
stt.close();
//執行數據庫還原
//filePath=this.getServletConfig().getServletContext().getRealPath("/")+"bak\\magic.dat";
String sql="restore database stream from disk='"+filePath+"'"+" with replace";
//out.print(sql);
Statement st=con.createStatement(); //同備份
st.execute(sql);
st.close();
}catch(Exception e){
flag=false;
e.printStackTrace();
logger.info("還原數據庫出現錯誤");
}finally{
new DateBakDao().closeCon(rs,null,con);
}
if(flag==true){
logger.info("還原了一次數據庫");
out.print("還原成功");
}else{
out.print("<script>alert('還原失敗')</script>");
}
%>
關閉指定數據庫所有連接的存儲過程
------------------------------------------------------------
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status < >-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
--用法
use master
exec killspid '數據庫名'
定時備份數據庫
ContextListener類
package serverbean;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.http.HttpServlet;
public class ContextListener extends HttpServlet implements
ServletContextListener {
/**
*
*/
private static final long serialVersionUID = 1L;
public ContextListener() {
}
private java.util.Timer timer = null;
public void contextInitialized(ServletContextEvent event) {
timer = new java.util.Timer(true);
event.getServletContext().log("定時器已啟動");
//timer.schedule(new DatabaseTask(event.getServletContext()), 0,
// 60 * 60 * 1000); // 后邊最后一個參數代表監視器的監視周期,現在為一小時
timer.schedule(new DatabaseTask(event.getServletContext()), 0, 1000); // 后邊最后一個參數代表監視器的監視周期,現在為一秒
event.getServletContext().log("已經添加任務調度表");
}
public void contextDestroyed(ServletContextEvent event) {
timer.cancel();
System.out.println("定時器銷毀");
event.getServletContext().log("定時器銷毀");
}
}
MyTask類
package serverbean;
import java.util.Calendar;
import java.util.TimerTask;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import dbconn.DBResult;
public class DatabaseTask extends TimerTask {
/**
* Constructor of the object.
*/
public DatabaseTask() {
super();
}
// private static final int C_SCHEDULE_HOUR = 3;//這個代表3點鐘的時候執行任務
private static final int C_SCHEDULE_SECOND = 3;// 這個代表3秒鐘的時候執行任務
private static boolean isRunning = false;
private ServletContext context = null;
public DatabaseTask(ServletContext context) {
this.context = context;
}
public void run() {
Calendar cal = Calendar.getInstance();
if (!isRunning) {
// if (C_SCHEDULE_HOUR == cal.get(Calendar.HOUR_OF_DAY)) {
if (C_SCHEDULE_SECOND == cal.get(Calendar.SECOND)) {
isRunning = true;
context.log("開始執行指定任務");
String filePath = this.context.getRealPath("/database_bak");
SimpleDateFormat tempDate = new SimpleDateFormat(
"yyyy-MM-dd" + "_" + "HH-mm-ss"); // HH表示24小時制，hh表示12小時制
String datetime = tempDate.format(new java.util.Date());
filePath = filePath + "\\FIOM_bak_" + datetime;
String sql = "backup database FIOM to disk= '" + filePath
+ "'";
DBResult conn=new DBResult();
try {
conn.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// System.out.println(this.context.getRealPath("/database_bak"));
// 對table空值進行修改,并更改編碼標記.
// 由于在查詢時就進行了鎖定,防止查詢后沒有錄入數據
// new CodeDAO().changeState();//此處寫執行任務代碼
isRunning = false;
context.log("指定任務執行結束");
}
} else {
context.log("上一次任務執行還未結束");
}
}
public void init() throws ServletException {
// Put your code here
}
}
最后需要在"web.xml"加上這樣一句話，這樣才能在服務器啟動的時候就執行監視器。
<listener>
<listener-class>serverbean.ContextListener </listener-class>
</listener>
posted on 2011-11-04 22:32
飛翔天使 閱讀(900)
評論(0) 編輯 收藏 所屬分類:
JSP