备份和还原数据库不知道能不能达到你要的分离和附加效果。 Attribute VB_Name = "SQLBackup" Public Function BackupSave(SavePath As String) '备份数据库 '为建立文件夹实例话文件夹。 Dim fsoTest As New FileSystemObject Dim dacon As ADODB.Connection Set dacon = New ADODB.Connection Dim ducon As ADODB.Connection Set ducon = New ADODB.Connection Dim syscon As ADODB.Connection Set syscon = New ADODB.Connection dacon.Open DatabaseConection ducon.Open DuConection syscon.Open DataSyslog
Dim sqlda As String Dim sqldu As String Dim sqlsys As String MDIForm1.MousePointer = 11 fsoTest.CreateFolder SavePath
'在指定文件夹下建立主恢复文件。 Open SavePath & "\数据恢复文件.bac" For Output As #1 Close #1
'备份数据库ZJSU到指定文件夹。 sqlda = "backup database [zjsu] to disk = '" & SavePath & "\ZJSU '" dacon.Execute sqlda
'备份数据库infor_collection到指定文件夹。 sqldu = "backup database [infor_collection] to disk = '" & SavePath & "\infor_collection'" ducon.Execute sqldu '备份数据库sys_log到指定文件夹。 sqlsys = "backup database [sys_log] to disk = '" & SavePath & "\sys_log'" syscon.Execute sqlsys
dacon.Close ducon.Close syscon.Close Set dacon = Nothing Set ducon = Nothing Set syscon = NothingEnd Function Public Function BackupRead(LoadPath As String) '还原数据库 Dim con As ADODB.Connection Set con = New ADODB.Connection con.Open MasterConection Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset
Dim sql As String Dim sqlda As String Dim sqldu As String Dim sqlsys As String
DoEvents '转让控制权
MDIForm1.MousePointer = 11
'检查数据库中是否存在infor_collection这个数据库。 sqldu = "select * from master..sysdatabases where name='infor_collection'" rs.Open sqldu, con, 1, 1 If rs.EOF = True Then con.Execute "create database infor_collection" End If rs.Close '检查数据库中是否存在ZJSU这个数据库。 sqlda = "select 1 from master..sysdatabases where name='ZJSU'" rs.Open sqlda, con, 1, 1 If rs.EOF = True Then con.Execute "create database ZJSU" End If rs.Close
'检查数据库中是否存在sys_log这个数据库。 sqlsys = "select 1 from master..sysdatabases where name='sys_log'" rs.Open sqlsys, con, 1, 1 If rs.EOF = True Then con.Execute "create database sys_log" End If rs.Close '还原前查询是否有(infor_collection)表的进程在运行,如果有就杀掉。 sql = "select spid from master..sysprocesses where dbid=db_id('infor_collection')" rs.Open sql, con, 1, 1 While rs.EOF = False sql = "kill " & rs(0) con.Execute sql rs.MoveNext Wend rs.Close
'还原前查询是否有(ZJSU)表的进程在运行,如果有就杀掉。 sql = "select spid from master..sysprocesses where dbid=db_id('ZJSU')" rs.Open sql, con, 1, 1 While rs.EOF = False sql = "kill " & rs(0) con.Execute sql rs.MoveNext Wend rs.Close
'还原前查询是否有(sys_log)表的进程在运行,如果有就杀掉。 sql = "select spid from master..sysprocesses where dbid=db_id('sys_log ')" rs.Open sql, con, 1, 1 While rs.EOF = False sql = "kill " & rs(0) con.Execute sql rs.MoveNext Wend rs.Close
' On Error GoTo ConnectionErr1 '还原数据库infor_collection sqldu = " restore database [infor_collection] from disk = '" & LoadPath & "\infor_collection '" con.Execute sqldu '还原数据库ZJSU sqlda = " restore database [zjsu] from disk = '" & LoadPath & "\ZJSU'" con.Execute sqlda '还原数据库sys_log sqlsys = " restore database [sys_log] from disk = '" & LoadPath & "\sys_log'" con.Execute sqlsys MDIForm1.MousePointer = 0
Attribute VB_Name = "SQLBackup"
Public Function BackupSave(SavePath As String) '备份数据库
'为建立文件夹实例话文件夹。
Dim fsoTest As New FileSystemObject Dim dacon As ADODB.Connection
Set dacon = New ADODB.Connection
Dim ducon As ADODB.Connection
Set ducon = New ADODB.Connection
Dim syscon As ADODB.Connection
Set syscon = New ADODB.Connection
dacon.Open DatabaseConection
ducon.Open DuConection
syscon.Open DataSyslog
Dim sqlda As String
Dim sqldu As String
Dim sqlsys As String
MDIForm1.MousePointer = 11
fsoTest.CreateFolder SavePath
'在指定文件夹下建立主恢复文件。
Open SavePath & "\数据恢复文件.bac" For Output As #1
Close #1
'备份数据库ZJSU到指定文件夹。
sqlda = "backup database [zjsu] to disk = '" & SavePath & "\ZJSU '"
dacon.Execute sqlda
'备份数据库infor_collection到指定文件夹。
sqldu = "backup database [infor_collection] to disk = '" & SavePath & "\infor_collection'"
ducon.Execute sqldu '备份数据库sys_log到指定文件夹。
sqlsys = "backup database [sys_log] to disk = '" & SavePath & "\sys_log'"
syscon.Execute sqlsys
MDIForm1.MousePointer = 0
Call save_log("备份数据库,备份路径为" & SavePath & "", 1)
MsgBox "数据库备份成功", 64 + vbOKOnly, "提示信息"
dacon.Close
ducon.Close
syscon.Close
Set dacon = Nothing
Set ducon = Nothing
Set syscon = NothingEnd Function
Public Function BackupRead(LoadPath As String) '还原数据库
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open MasterConection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
Dim sqlda As String
Dim sqldu As String
Dim sqlsys As String
DoEvents '转让控制权
MDIForm1.MousePointer = 11
'检查数据库中是否存在infor_collection这个数据库。
sqldu = "select * from master..sysdatabases where name='infor_collection'"
rs.Open sqldu, con, 1, 1
If rs.EOF = True Then
con.Execute "create database infor_collection"
End If
rs.Close
'检查数据库中是否存在ZJSU这个数据库。
sqlda = "select 1 from master..sysdatabases where name='ZJSU'"
rs.Open sqlda, con, 1, 1
If rs.EOF = True Then
con.Execute "create database ZJSU"
End If
rs.Close
'检查数据库中是否存在sys_log这个数据库。
sqlsys = "select 1 from master..sysdatabases where name='sys_log'"
rs.Open sqlsys, con, 1, 1
If rs.EOF = True Then
con.Execute "create database sys_log"
End If
rs.Close
'还原前查询是否有(infor_collection)表的进程在运行,如果有就杀掉。
sql = "select spid from master..sysprocesses where dbid=db_id('infor_collection')"
rs.Open sql, con, 1, 1
While rs.EOF = False
sql = "kill " & rs(0)
con.Execute sql
rs.MoveNext
Wend
rs.Close
'还原前查询是否有(ZJSU)表的进程在运行,如果有就杀掉。
sql = "select spid from master..sysprocesses where dbid=db_id('ZJSU')"
rs.Open sql, con, 1, 1
While rs.EOF = False
sql = "kill " & rs(0)
con.Execute sql
rs.MoveNext
Wend
rs.Close
'还原前查询是否有(sys_log)表的进程在运行,如果有就杀掉。
sql = "select spid from master..sysprocesses where dbid=db_id('sys_log ')"
rs.Open sql, con, 1, 1
While rs.EOF = False
sql = "kill " & rs(0)
con.Execute sql
rs.MoveNext
Wend
rs.Close
' On Error GoTo ConnectionErr1
'还原数据库infor_collection
sqldu = " restore database [infor_collection] from disk = '" & LoadPath & "\infor_collection '"
con.Execute sqldu '还原数据库ZJSU
sqlda = " restore database [zjsu] from disk = '" & LoadPath & "\ZJSU'"
con.Execute sqlda '还原数据库sys_log
sqlsys = " restore database [sys_log] from disk = '" & LoadPath & "\sys_log'"
con.Execute sqlsys MDIForm1.MousePointer = 0
Call save_log("还原数据库,还原路径为" & SavePath & "", 2)
MsgBox "数据恢复成功!", vbOKOnly + 64, "提示信息"
con.Close
Set rs = Nothing
Set con = Nothing
'ConnectionErr: MsgBox "请检查数据库连接是否正常!" & Chr(13) & "请检查要还原的文件是否存在! ", vbCritical, "还原失败"
End Function
sp_attach_db [ @dbname = ] 'dbname'
, [ @filename1 = ] 'filename_n' [ ,...16 ]参数
[@dbname =] 'dbname'要附加到服务器的数据库的名称。该名称必须是唯一的。dbname 的数据类型为 sysname,默认值为 NULL。 [@filename1 =] 'filename_n'数据库文件的物理名称,包括路径。filename_n 的数据类型为 nvarchar(260),默认值为 NULL。最多可以指定 16 个文件名。参数名称以 @filename1 开始,递增到 @filename16。文件名列表至少必须包括主文件,主文件包含指向数据库中其它文件的系统表。该列表还必须包括数据库分离后所有被移动的文件。返回代码值
0(成功)或 1(失败)结果集
无注释
只应对以前使用显式 sp_detach_db 操作从数据库服务器分离的数据库执行 sp_attach_db。如果必须指定多于 16 个文件,请使用带有 FOR ATTACH 子句的 CREATE DATABASE。如果将数据库附加到的服务器不是该数据库从中分离的服务器,并且启用了分离的数据库以进行复制,则应该运行 sp_removedbreplication 从数据库删除复制。权限
只有 sysadmin 和 dbcreator 固定服务器角色的成员才能执行本过程。示例
下面的示例将 pubs 中的两个文件附加到当前服务器。EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
--------------------
CN.Execute (你的附加数据库语句)
use master使数据库脱机
alter database 库名 set offline
使数据库联机
alter database 库名 set online