备份: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
飞翔天使 阅读(890)
评论(0) 编辑 收藏 所属分类:
JSP