752
社区成员
发帖
与我相关
我的任务
分享通过设备文件恢复数据库后，数据库中只有系统表？
数据库位SQL SERVER 2000，用下面代码恢复数库只有系统表，没有用户表。但通过企业管理器恢复的话，就有用户表，为什么？请问哪里有问题呢？
///*********判断是否安装SQL是否已经启动*********/
oleobject pbobject
pbobject = create oleobject
ll_status = pbobject.connecttonewobject("sqldmo.sqlserver")
if ll_status = 0 then //连接成功
pbobject.name = ls_serverinfo
pbobject.logintimeout = 10
//以sql server方式连接
pbobject.loginsecure = false
li_ret = pbobject.status
if li_ret <> 1 then
destroy(pbobject)
messagebox("提示信息:","sql server未启动" , StopSign!)
return
end if
else
destroy(pbobject)
messagebox("提示信息:","sql server未安装" , StopSign!)
return
end if
/***************连接数据库*************/
//Profile isystem
SQLCA.DBMS = "OLE DB"
SQLCA.LogPass = ls_pass
SQLCA.LogId = ls_username
SQLCA.AutoCommit = true
SQLCA.DBParm = "PROVIDER='SQLOLEDB',"+&
"DATASOURCE='" + ls_serverinfo + "'," +&
"PROVIDERSTRING='Database=master"+"'"
connect using sqlca ;
if sqlca.sqlcode <> 0 then
messagebox("提示信息:","数据库连接失败，请检查数据库登录密码是否填写正确!")
return
end if
/***********通过设备文件恢复数据库***********/
//创建一个空库
ls_execsql = "create database "+ ls_dbname
execute immediate :ls_execsql;
if sqlca.SQLCode = 0 then
commit;
else
rollback;
end if
ls_execsql = "exec sp_addumpdevice 'disk' "+" , '"+ls_dbname+"' "+","+" '"+ls_path+"'"
execute immediate :ls_execsql;
ls_execsql = "restore database "+ls_dbname+" from disk = '"+ls_path+"'"
execute immediate :ls_execsql;
ls_execsql = "exec sp_dropdevice '"+ls_dbname+"'"
execute immediate :ls_execsql;
...全文
请发表友善的回复…
发表回复
WorldMobile 2014-05-13
- 打赏
- 举报
是不是你连接的数据库的参数不对，或者你把生成的恢复数据库的sql，手工执行一下看看是不是可以
yjq777 2014-05-13
- 打赏
- 举报
手工通过设备文件恢复没有问题，就是通过程序操作有问题，只有系统表，没有用户表。下面是完整CODE
/**************************************************
* 描述：通过设备文件创建数据库和配置文件
**************************************************/
string ls_execsql , ls_serverinfo , ls_username , ls_pass , ls_repass , ls_dbname , ls_path , ls_null
integer li_ret
long ll_status , ll_current_row
ls_path = gs_currentpath+"\xhis.bak"
dw_1.accepttext()
ll_current_row = dw_1.getrow()
ls_serverinfo = dw_1.getitemstring(ll_current_row , "serverinfo")
if ls_serverinfo = '' or isnull(ls_serverinfo) = true then
messagebox("提示信息:" , "请填写服务器名称或者IP!")
dw_1.setfocus()
dw_1.setcolumn(1)
return
end if
ls_username = dw_1.getitemstring(ll_current_row , "dbuser")
ls_pass = trim(dw_1.getitemstring(ll_current_row , "dbpass"))
if ls_pass = '' or isnull(ls_pass) = true then
messagebox("提示信息:" , "请填写数据库登录密码!")
dw_1.setfocus()
dw_1.setcolumn(3)
return
end if
ls_repass = trim(dw_1.getitemstring(ll_current_row , "redbpass"))
if ls_repass = '' or isnull(ls_repass) = true then
messagebox('提示信息:' , "请填写重复登录密码!" )
dw_1.setfocus()
dw_1.setcolumn(4)
return
end if
if ls_pass <> ls_repass then
messagebox('提示信息:','两次登录密码输入不一致,请检查!')
setnull(ls_null)
dw_1.object.dbpass.primary.current = ls_null
dw_1.object.redbpass.primary.current = ls_null
dw_1.setfocus()
dw_1.setcolumn(3)
return
end if
ls_dbname = dw_1.getitemstring(ll_current_row , "dbname")
///*********判断是否安装SQL是否已经启动*********/
oleobject pbobject
pbobject = create oleobject
ll_status = pbobject.connecttonewobject("sqldmo.sqlserver")
if ll_status = 0 then //连接成功
pbobject.name = ls_serverinfo
pbobject.logintimeout = 10
//以sql server方式连接
pbobject.loginsecure = false
li_ret = pbobject.status
if li_ret <> 1 then
destroy(pbobject)
messagebox("提示信息:","sql server未启动" , StopSign!)
return
end if
else
destroy(pbobject)
messagebox("提示信息:","sql server未安装" , StopSign!)
return
end if
/***************连接数据库*************/
//Profile isystem
SQLCA.DBMS = "OLE DB"
SQLCA.LogPass = ls_pass
SQLCA.LogId = ls_username
SQLCA.AutoCommit = true
SQLCA.DBParm = "PROVIDER='SQLOLEDB',"+&
"DATASOURCE='" + ls_serverinfo + "'," +&
"PROVIDERSTRING='Database=master"+"'"
connect using sqlca ;
if sqlca.sqlcode <> 0 then
messagebox("提示信息:","数据库连接失败，请检查数据库登录密码是否填写正确!")
return
end if
/***********通过设备文件恢复数据库***********/
//创建一个空库
ls_execsql = "create database "+ ls_dbname
execute immediate :ls_execsql;
if sqlca.SQLCode = 0 then
commit;
else
rollback;
end if
ls_execsql = "exec sp_addumpdevice 'disk' "+" , '"+ls_dbname+"' "+","+" '"+ls_path+"'"
execute immediate :ls_execsql;
ls_execsql = "restore database "+ls_dbname+" from disk = '"+ls_path+"'"
execute immediate :ls_execsql;
ls_execsql = "exec sp_dropdevice '"+ls_dbname+"'"
execute immediate :ls_execsql;
if sqlca.sqlcode = 0 then
commit;
//删除数据库文件
if fileexists(ls_path) = true then
filedelete(ls_path)
end if
//修改dbms.ini文件,如果没有该文件则直接创建然后写入
if fileexists(gs_currentpath+"\dbms.ini") = true then
setprofilestring(gs_currentpath+"\dbms.ini" , "database" , "dbname" , ls_dbname)
setprofilestring(gs_currentpath+"\dbms.ini" , "database" , "logid" , ls_username)
setprofilestring(gs_currentpath+"\dbms.ini" , "database" , "logpass" , gn_des.of_encrypt(ls_pass))
setprofilestring(gs_currentpath+"\dbms.ini" , "database" , "serverinfo" , gn_des.of_encrypt(ls_serverinfo))
else
integer li_filenum
li_filenum = fileopen(gs_currentpath+"\dbms.ini" , LineMode! , Write! , LockWrite! , Append!)
fileclose(li_filenum)
setprofilestring(gs_currentpath+"\dbms.ini" , "database" ,"dbname" , ls_dbname)
setprofilestring(gs_currentpath+"\dbms.ini" , "database" ,"logid" , ls_username)
setprofilestring(gs_currentpath+"\dbms.ini" , "database" ,"logpass" , gn_des.of_encrypt(ls_pass))
setprofilestring(gs_currentpath+"\dbms.ini" , "database" ,"serverinfo" , gn_des.of_encrypt(ls_serverinfo))
end if
messagebox("提示信息:","请关闭该窗口后再次运行xhis程序!")
else
rollback;
messagebox("创建失败", sqlca.sqlerrtext , Stopsign!)
end if
disconnect using sqlca ;
