说明:MyComputer 为本机器名称,Con为ADODB.Connection源程序如下:
Shell "Net Share ShareFolder=E:\",vbHide
Con.Excute "Exec master..xp_cmdshell 'net use \\MyComputer\ShareFolder'"
Con.Excute"backup database Test to Disk='\\MyComputer\ShareFolder\TestBak.Bak'"。程序执行到Con.Excute"backup database Test to Disk='\\MyComputer\ShareFolder\TestBak.Bak'"。
总是提示:无法打开备份设备 '\\MyComputer\ShareFolder\TestBak.Bak'。设备出现错误或设备脱机。详细信息请参阅 SQL Server 错误日志。请问如何解决??
Shell "Net Share ShareFolder=E:\",vbHide
Con.Excute "Exec master..xp_cmdshell 'net use \\MyComputer\ShareFolder'"
Con.Excute"backup database Test to Disk='\\MyComputer\ShareFolder\TestBak.Bak'"。程序执行到Con.Excute"backup database Test to Disk='\\MyComputer\ShareFolder\TestBak.Bak'"。
总是提示:无法打开备份设备 '\\MyComputer\ShareFolder\TestBak.Bak'。设备出现错误或设备脱机。详细信息请参阅 SQL Server 错误日志。请问如何解决??
解决方案 »
- 有几个msflexgrid里面的数据需要快速的保存,以后数据导入msflexgrid要用,请问什么方法好?
- VBA 字符读取
- 打开外部程序的问题
- 如何定时从数据库中取数据
- 有人用vb写过邮件服务器软件吗,可以参考吗,我用的imail不能自动添加用户和账号,高分想送~~~
- 如何捕获POST时提交的头
- 咋个结帖,咋个给分,咋个得分哟
- 循环多次的freefile函数问题???
- 请问在vb6.0中如何控制一台针式打印机,详情请进如看,谢谢赐教
- 谁来露一手???刺激!
- 一个文本文件,不知是Unicode、ANSI、UTF-8格式中的哪一种,如何将其转换成ANSI格式
- (在線等!)怎樣知道vsFlexGrid的combolist選到了第幾個呢?
@ComputeName varchar(10),
@AdminPass varchar(10),
@FilePathName varchar(50),
@DBName varchar(20))
as
begin
SET NOCOUNT ON
declare @CmdStr varchar(1000)
set @CmdStr = 'master..xp_cmdshell "net use \\' + @ComputeName + ' /delete"'
exec (@CmdStr)
if len(@AdminPass) > 0
set @CmdStr = 'master..xp_cmdshell ''net use \\' + @ComputeName + ' ' + @AdminPass + ' /user:' +
@ComputeName + '\administrator'''
else
set @CmdStr = 'master..xp_cmdshell ''net use \\' + @ComputeName + ' "" /user:' +
@ComputeName + '\administrator''' exec (@CmdStr)
set @CmdStr ='backup database ' + @DBName + ' to disk=''\\' + @ComputeName + '\' + @FilePathName + '\' + convert(varchar, GetDate(),12) + '.bak'' with init'
-- PRINT @CmdStr
exec (@CmdStr)
SET NOCOUNT OFF --恢復顯示"影響記錄行"
return 0
end記得要在master下操作
1.建个socket服务器。主要负责多用户连接、消息广播、备份及将备份文件传送至客户连接socket.
这种方法使用sql server 混合安全模式较易处理。
而且我现在问题的关键就是在Windwos文件共享的问题,解决这个也就解决了上面的问题!
1.服务器帐户要拥有对客户机的目录访问权力;设置用户范围为everyone,读写权限;
2.执行adddumpdevice系统过程将//myComputer/Shard....加入sqlServer备份设备列表;
我个人觉得这种方法虽然可行,但设置麻烦,脱离了nt的安全管理。
我还是建议自已编写socet处理远程备份文件.
代码如下:
bas:
Public Declare Function WNetAddConnection2 Lib "mpr.dll" Alias _
"WNetAddConnection2A" (lpNetResource As NETRESOURCE, _
ByVal lpPassword As String, ByVal lpUserName As String, _
ByVal dwFlags As Long) As Long
Public Declare Function WNetCancelConnection2 Lib "mpr.dll" Alias _
"WNetCancelConnection2A" (ByVal lpName As String, _
ByVal dwFlags As Long, ByVal fForce As Long) As LongPublic Const NO_ERROR = 0
Public Const CONNECT_UPDATE_PROFILE = &H1
'网络驱动器参数
Public Const RESOURCETYPE_DISK = &H1
Public Const RESOURCETYPE_PRINT = &H2
Public Const RESOURCETYPE_ANY = &H0
Public Const RESOURCE_CONNECTED = &H1
Public Const RESOURCE_REMEMBERED = &H3
Public Const RESOURCE_GLOBALNET = &H2
Public Const RESOURCEDISPLAYTYPE_DOMAIN = &H1
Public Const RESOURCEDISPLAYTYPE_GENERIC = &H0
Public Const RESOURCEDISPLAYTYPE_SERVER = &H2
Public Const RESOURCEDISPLAYTYPE_SHARE = &H3
Public Const RESOURCEUSAGE_CONNECTABLE = &H1
Public Const RESOURCEUSAGE_CONTAINER = &H2
'错误常量
Public Const ERROR_ACCESS_DENIED = 5&
Public Const ERROR_ALREADY_ASSIGNED = 85&
Public Const ERROR_BAD_DEV_TYPE = 66&
Public Const ERROR_BAD_DEVICE = 1200&
Public Const ERROR_BAD_NET_NAME = 67&
Public Const ERROR_BAD_PROFILE = 1206&
Public Const ERROR_BAD_PROVIDER = 1204&
Public Const ERROR_BUSY = 170&
Public Const ERROR_CANCELLED = 1223&
Public Const ERROR_CANNOT_OPEN_PROFILE = 1205&
Public Const ERROR_DEVICE_ALREADY_REMEMBERED = 1202&
Public Const ERROR_EXTENDED_ERROR = 1208&
Public Const ERROR_INVALID_PASSWORD = 86&
Public Const ERROR_NO_NET_OR_BAD_PATH = 1203&
'网络驱动器映射
Public Type NETRESOURCE
dwScope As Long
dwType As Long
dwDisplayType As Long
dwUsage As Long
lpLocalName As String
lpRemoteName As String
lpComment As String
lpProvider As String
End Type
'定义一个全局的本地网络驱动器变量(当网络驱动器连接的时候自动更新,同时要检测是否关闭原来的网络驱动器)
Public LocalNetDrive As String'连接到网络驱动器
Public Function NetDriveConnect(ByVal RemotePath As String, ByVal Localpath As String, ByVal lpUserName As String, ByVal lpPassword As String) As Boolean
NetDriveConnect = False
Dim NetR As NETRESOURCE
Dim ErrInfo As Long
On Error GoTo Error_NetDriveConnect
NetR.dwScope = RESOURCE_GLOBALNET
NetR.dwType = RESOURCETYPE_DISK
NetR.dwDisplayType = RESOURCEDISPLAYTYPE_SHARE
NetR.dwUsage = RESOURCEUSAGE_CONNECTABLE
NetR.lpLocalName = Localpath
LocalNetDrive = Localpath
NetR.lpRemoteName = RemotePath
ErrInfo = WNetAddConnection2(NetR, lpPassword, lpUserName, CONNECT_UPDATE_PROFILE) '用户名和密码
If ErrInfo = NO_ERROR Then NetDriveConnect = True
Exit Function
Error_NetDriveConnect:
NetDriveConnect = False
End Function
'断开网络驱动器
Public Function NetDriveDisconnect(ByVal LocalNetDrive As String) As Boolean NetDriveDisconnect = False
Dim ErrInfo As Long
On Error GoTo Error_NetDriveDisconnect
ErrInfo = WNetCancelConnection2(LocalNetDrive, CONNECT_UPDATE_PROFILE, False)
If ErrInfo = NO_ERROR Then NetDriveDisconnect = True
Exit Function
Error_NetDriveDisconnect:
NetDriveDisconnect = False
End FunctionForm1:
Private Sub Command1_Click()
If NetDriveConnect("\\MyComputer\ShareFolder", "Z:", 用户名, 密码) = True MsgBox "Net Connection Successful!", vbInformation, "Share Connected"
'使用具有足够权限的用户名和密码登陆进你的计算机
End SubPrivate Sub Command2_Click() If NetDriveDisconnect(LocalNetDrive) = True Then MsgBox "Net Disconnection Successful!", vbInformation, "Share Disconnected"
End Sub方法是把你需要共享的文件夹共享为Z盘,然后进行备份操作,操作完成后,调用Command2,关闭共享就OK了~~
需要注意的是,备份的时候,要把你的\\MyComputer\ShareFolder修改为Z:
对了我以下的程序,怎么一直是红色的错误代码,是不是忘了引用什么对象。
Public Const NO_ERROR = 0
Public Const CONNECT_UPDATE_PROFILE = &H1
'网络驱动器参数
Public Const RESOURCETYPE_DISK = &H1
Public Const RESOURCETYPE_PRINT = &H2
Public Const RESOURCETYPE_ANY = &H0
Public Const RESOURCE_CONNECTED = &H1
Public Const RESOURCE_REMEMBERED = &H3
Public Const RESOURCE_GLOBALNET = &H2
Public Const RESOURCEDISPLAYTYPE_DOMAIN = &H1
Public Const RESOURCEDISPLAYTYPE_GENERIC = &H0
Public Const RESOURCEDISPLAYTYPE_SERVER = &H2
Public Const RESOURCEDISPLAYTYPE_SHARE = &H3
Public Const RESOURCEUSAGE_CONNECTABLE = &H1
Public Const RESOURCEUSAGE_CONTAINER = &H2
程序有时候备份通不过,有时候又可以。程序执行到Con.Excute"backup database Test to Disk='\\MyComputer\ShareFolder\TestBak.Bak'"。
总是提示:无法打开备份设备 '\\MyComputer\ShareFolder\TestBak.Bak'。设备出现错误或设备脱机。详细信息请参阅 SQL Server 错误日志。Public Function BackupSQLDBToLocal(ByVal DBName As String, ByVal LocalBackupPath As String, ByVal BackupFileName As String, ByVal Con As ADODB.Connection) As Boolean
Dim sComputerName As String
Dim sShareFilePath, sShareName As String
Dim sSQL As String
Dim Conn As ADODB.Connection
Screen.MousePointer = 11
On Error GoTo BakErr
Set Conn = Con
Conn.CommandTimeout = 0
sComputerName = gGetComputerName
sShareName = CreateShareFolder(LocalBackupPath)
sShareFilePath = "\\" & sComputerName & "\" & sShareName
BackupFileName = IIf(InStr(BackupFileName, ".Bak") > 0, BackupFileName, BackupFileName & ".Bak")
sSQL = "Exec master..xp_cmdshell 'net use \\Lituo'"
'sSQL = "Exec master..xp_cmdshell 'net use \\Lituo lituo /user:Lituo\administrator'"
'sSQL = "Exec master..xp_cmdshell 'net use " & sShareFilePath & "'"
Conn.Execute sSQL
sShareFilePath = sShareFilePath & "\" & BackupFileName
sSQL = "backup database " & DBName & " to disk='" & sShareFilePath & "' with init"
DoEvents
Conn.Execute sSQL
ShutShareFolder sShareName '删除本地共享文件夹
BackupSQLDBToLocal = True
Set Conn = Nothing
Screen.MousePointer = 0
Exit Function
BakErr:
Screen.MousePointer = 0
ShutShareFolder sShareName '删除本地共享文件夹
Set Conn = Nothing
BackupSQLDBToLocal = False
MsgBox Err.Description, vbInformation, CompanyName
End FunctionPrivate Function gGetComputerName() As String
Dim sComputerName As String
Dim lSize As Long
lSize = 255
sComputerName = Space(lSize)
GetComputerName sComputerName, lSize
sComputerName = Left(sComputerName, lSize) '得到本地机器名
gGetComputerName = sComputerName
End FunctionPrivate Function CreateShareFolder(ByVal SharePath As String) As String '建立本地共享文件夹
Shell "Net Share ShareFolder=" & SharePath & " /Unlimited", vbHide
CreateShareFolder = "ShareFolder" '建立本地共享文件夹
End FunctionPrivate Sub ShutShareFolder(ByVal ShareName As String) '删除本地共享文件夹
Shell "net share " & ShareName & " /delete", vbHide
End Sub