sql还原问题

hzc1 2008-03-18 01:54:17
在SQL2000中我拥有二个账户,一个SA,一个A,都有密码,其中A只能访问其中一个数据库,比如ABC,在程序中用代码对ABC进行备份,成功.用SA还原也成功,可是如果SQL2000中有A的账户或没有A的账户时,问题出现了,用A不能访问ABC,A没有了访问ABC的权力,但是在ABC的用户中可以看到有A,但是登录名称为空,此时非得手工在ABC的用户中删除,然后在安全账户中对账户A进行修改之后,A才能访问ABC.不知是何故?
我用来备份还原的代码:
Public Function fBackupDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sIsAddBackup As Boolean = False _
) As String

Dim iDb As ADODB.Connection
Dim iConcStr$, iSql$, iReturn$
Dim uid, PWD, addr As String

On Error GoTo lbErr

'读取数据库相关信息
uid = "a"
PWD = "abc"
addr = LOCAIP

'创建对象
Set iDb = New ADODB.Connection

'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "driver=SQL Server;server=" & addr & ";uid=" & uid & ";pwd=" & PWD
iDb.Open iConcStr

'生成数据库备份语句
iSql = "backup database [" & sDataBaseName & "]" & vbCrLf & _
"to disk='" & sBackUpfileName & "'" & vbCrLf & _
"with description='" & "zj-backup at:" & Date & "(" & Time & ")'" & vbCrLf & _
IIf(sIsAddBackup, "", ",init")

iDb.Execute iSql
iReturn = "数据库备份成功!"
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fBackupDatabase_a = iReturn
End Function



Public Function fRestoreDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sDataBasePath$ = "" _
, Optional ByVal sBackupNumber& = 1 _
, Optional ByVal sReplaceExist As Boolean = True _
) As String

Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&
Dim uid, PWD, addr As String

On Error GoTo lbErr

'读取数据库相关信息
uid = "sa"
PWD = "sa"
addr = LOCAIP


'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset

'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "driver=SQL Server;server=" & addr & ";uid=" & uid & ";pwd=" & PWD
iDb.Open iConcStr

'得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
If sDataBasePath = "" Then
iSql = "select filename from master..sysfiles"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
iSql = iRe(0)
iRe.Close
sDataBasePath = Left(iSql, InStrRev(iSql, "\"))
End If

'检查数据库是否存在
If sReplaceExist = False Then
iSql = "select 1 from master..sysdatabases where name='" & sDataBaseName & "'"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
If iRe.EOF = False Then
iReturn = "数据库已经存在!"
iRe.Close
GoTo lbExit
End If
iRe.Close
End If

'关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
While iRe.EOF = False
iSql = "kill " & iRe(0)
iDb.Execute iSql
iRe.MoveNext
Wend
iRe.Close

'获取数据库恢复信息
iSql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly

'生成数据库恢复语句
iSql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
"from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber & vbCrLf
With iRe
While Not .EOF
iReturn = iRe("PhysicalName")
iI = InStrRev(iReturn, ".")
iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
iSql = iSql & ",move '" & iRe("LogicalName") & _
"' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
.MoveNext
Wend
.Close
End With
iSql = iSql & IIf(sReplaceExist, ",replace", "")

iDb.Execute iSql
iReturn = "数据库恢复成功!"
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fRestoreDatabase_a = iReturn
End Function


...全文
35 点赞 收藏 3
写回复
3 条回复
hzc1 2008年05月12日
没有高手回答,郁闷~~~~~~~~~~
回复 点赞
knowledge_Is_Life 2008年04月30日
不明LZ在说什么
回复 点赞
hzc1 2008年03月19日
有没有人要可以帮帮忙啊?
回复 点赞
发动态
发帖子

863

社区成员

5.5w+

社区内容

VB 数据库(包含打印,安装,报表)
社区公告
暂无公告