--还原数据库时数据库正在使用导致数据库无会还原,此存储过存在msater数据库下创建。exec killspid cztdba 结束此数据库的进程,这样才能还原数据库
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
/**
* 还原数据库备份文件
* @return
*/
public String restoreDatabase() {
String path = null;
try {
path = WebUtils.getRealPath(getServletContext(), restDatabasePath);
} catch (FileNotFoundException e) {
logger.error("Can't get web path.", e);
}
//文件夹不存在创建
uploadedFileHandler.setTargetPath(path);
//得到上传后文件对象
File file = uploadedFileHandler.doUpload(data, dataFileName);
if (file.exists()) {
StringBuffer sbf = new StringBuffer();
sbf.append("use master exec killspid 'cztdba' restore database cztdba from disk='")
.append(file.getPath())
.append("'");
//执行还原数据语句
try {
jdbcTemplate.execute(sbf.toString());
addActionError("还原数据库成功");
} catch(Exception e) {
addActionError("还原数据库失败");
}
/**
* 不添加这句会找不到数据库里的表,添加上这句会出现异常但是对系统无影响,所以把此异常屏蔽
*/
try {
jdbcTemplate.execute("use cztdba");
} catch(Exception e) {
//e.printStackTrace();
}
}
return INPUT;
}
/**
* 备份数据库文件
* @return
*/
public String backDatabase() {
StringBuffer path = new StringBuffer();
try {
path.append(WebUtils.getRealPath(getServletContext(), backDatabasePath));
} catch (FileNotFoundException e) {
logger.error("Can't get web path.", e);
}
//文件夹不存在创建
uploadedFileHandler.setTargetPath(path.toString());
//下载文件名称,随机生成
downFileName = DateUtil.getDateTime("yyyyMMddhhmmss", new Date()) + ".back";
//得到文件在系统中实际地址
path.append(File.separatorChar)
.append(downFileName);
//开始备份文件
StringBuffer sbf = new StringBuffer("backup database cztdba to disk='")
.append(path.toString())
.append("'");
jdbcTemplate.execute(sbf.toString());
try {
inputStream = new FileInputStream(path.toString());
} catch (FileNotFoundException e) {
e.printStackTrace();
addActionError("备份文件出现问题");
return INPUT;
}
return SUCCESS;
}