使用java程序通过jdbc调用数据库存储过程实现数据库的备份和还原时报如下错误:
java.sql.SQLException: 无法打开备份设备 'db_yxt_data'。设备出现错误或设备脱机。详细信息请参阅 SQL Server 错误日志。
备份数据库的存储过程如下:
use db_test
go
--数据库备份存储过程(backupPath:备份路径,backupFile:将要产生的备份文件名)
CREATE PROCEDURE backupDatabase(@backupPath varchar(200),@backupFile varchar(200))
as
declare
@path varchar(200),
@ReturnCode int
begin
SELECT @path = @backupPath+'/'+@backupFile;
IF EXISTS(SELECT 1 FROM master.dbo.sysdevices WHERE name ='db_test_data'
OR phyname = @path)
begin
exec sp_dropdevice 'db_test_data'
end
EXEC sp_addumpdevice 'disk', 'db_test_data', @path;
BACKUP DATABASE db_test TO db_test_data ;
end
GO
错误产生的原因:
对应的数据库备份文件存放路径不存在,如backupFile=‘c:/backup’,但是c盘目录下没有backup文件夹,则会报上述错误。
解决办法是在相应java备份程序前先创建对应的目录,具体java代码如下:
/**
* 数据库备份
*
* @return
*/
public String backUpDatabase() {
Session session = null;
String result = "数据库备份失败!";
//从数据库系统参数中获取备份路径
String backUpPath = getSvalueBySkey("backUpPath");
if (null == backUpPath) {
result = "系统参数中没有指定备份路径,数据库备份失败!";
} else {
try {
//若备份目录不存在则新建
File file = new File(backUpPath);
if(!file.exists()){
file.mkdirs();
}
session = systemParaDao.getSessionFactory().getCurrentSession();
Connection con = session.connection();
String nowDateStr = new SimpleDateFormat("yyyy-MM-dd-HHmmss").format(new Date());
String sql = "{call dbo.backupDatabase(?,?)} ";//项目数据库备份
CallableStatement stat = con.prepareCall(sql);
stat.setString(1, backUpPath+"/aa");
stat.setString(2, "bakdb_" + nowDateStr + ".bak");
stat.execute();
result = "数据库备份成功,备份文件位置:" + backUpPath + "/bakdb_" + nowDateStr + ".bak";
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
return "{\"message\":\""+result+"\"}";
}
扩展:
数据库恢复存储过程如下:
--结束数据库访问进程(恢复之前,应该调用该存储过程杀死所有访问当前将要恢复的数据库进程,否则数据库无法进行恢复操作)
use master
go
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
GO
--数据库备份恢复
use master
go
CREATE PROCEDURE restoreBxtDatabase(@backupPath varchar(200),@backupFile varchar(200))
as
declare
@path varchar(200)
begin
SELECT @path = @backupPath+'/'+@backupFile;
IF EXISTS(SELECT 1 FROM master.dbo.sysdevices WHERE name ='db_test_data'
OR phyname = @path)
begin
EXEC sp_dropdevice 'db_test_data';
end
EXEC sp_addumpdevice 'disk', 'db_test_data', @path;
RESTORE DATABASE db_test FROM db_test_data;
end
GO