java.sql.SQLException: 无法打开备份设备 'db_bk_data'。设备出现错误或设备脱机。

使用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