问关于SQL SERVER 备份和数据恢复的问题
如何在程序中对远程或者本机的SQL SERVER数据库进行备份和恢复,因为不同于ACCESS啊
另如何在程序中实现初始化数据库,我有导出的SQL文件
各位高手,帮忙一下
[解决办法]
備份：backup database [dbname] to disk = "d:\bak.bak "
恢復：restore database [dbname] from disk= "d:\bak.bak "--需連接master庫並斷開所有[dbname]的連接
初始化：把sql文件讀入以SQL語句執行就可以了
[解决办法]
备份简单，恢复时先杀进程。
/***************************
杀进程存储过程
***************************/
use master
go
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N 'dbo.p_killspid ') AND objectproperty(id,N 'IsProcedure ')=1)
drop proc p_killspid
go
create proc p_killspid (@dbname varchar(20)) with encryption
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
declare @a varchar(100)
while @@fetch_status < > -1
begin
set @a= 'kill '+rtrim(@spid)
exec(@a)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
GO
[解决办法]
建议到邹捷大哥的blog上看看！！
http://blog.csdn.net/zjcxc/
[解决办法]
远程备份时注意路径问题，因为是服务器上的路径，而不是本机路径，
备份前最好指定服务器上已存在的路径，否则会出错
[解决办法]
都说完了，没得说的，不过zlt982001(乐天) 说的要特别注意。
[解决办法]
这是我的数据备份和恢复模块
Option Explicit
Public conn As New ADODB.Connection '数据库连接变量
Private WithEvents objBackup As SQLDMO.Backup
Private WithEvents objRestore As SQLDMO.Restore
Public isql As String
Private Sub cmdBackup_Click() '备份按钮
Dim objSQLServer As New SQLDMO.SQLServer
Dim strServer As String
Dim strUserID As String
Dim strPassword As String
Dim strDatabase As String
Dim strFile As String
strServer = "127.0.0.1 "
strUserID = "sa "
strPassword = " "
strDatabase = "lifei "
strFile = App.Path & "\数据备份\bak "
If Dir(strFile) <> " " Then
If MsgBox( "文件 " & strFile & "已存在，是否删除？ ", vbQuestion + vbYesNo) = vbYes Then
Kill strFile
Else
Exit Sub
End If
End If
lblProgress.Caption = "备份进度： 0% "
Screen.MousePointer = 11
On Error GoTo ErrorHandler
objSQLServer.Connect strServer, strUserID, strPassword
Set objBackup = New SQLDMO.Backup
With objBackup
.PercentCompleteNotification = 1
.Database = strDatabase
.Files = strFile
.SQLBackup objSQLServer
End With
Set objBackup = Nothing
objSQLServer.Close
Set objSQLServer = Nothing
Screen.MousePointer = 0
Exit Sub
ErrorHandler:
Screen.MousePointer = 0
MsgBox Err.Description, vbCritical
End Sub
Private Sub cmdRestore_Click()
Dim objSQLServer As New SQLDMO.SQLServer
Dim strServer As String
Dim strUserID As String
Dim strPassword As String
Dim strDatabase As String
Dim strFile As String
strServer = "127.0.0.1 "
strUserID = "sa "
strPassword = " "
strDatabase = "lifei "
strFile = App.Path & "\数据备份\bak "
If Dir(strFile) = " " Then
MsgBox "文件 " & strFile & "不存在！ ", vbExclamation
Exit Sub
End If
lblProgress.Caption = "恢复进度： 0% "
Screen.MousePointer = 11
On Error GoTo ErrorHandler
objSQLServer.Connect strServer, strUserID, strPassword
Set objRestore = New SQLDMO.Restore
With objRestore
.PercentCompleteNotification = 1
.Database = strDatabase
.ReplaceDatabase = True
.Files = strFile
.SQLRestore objSQLServer
End With
Set objRestore = Nothing
objSQLServer.Close
Set objSQLServer = Nothing
Screen.MousePointer = 0
Exit Sub
ErrorHandler:
Screen.MousePointer = 0
MsgBox Err.Description, vbCritical
End Sub
Private Sub Command1_Click()
Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
If conn.State <> 0 Then
conn.Close
Set conn = Nothing
End If
End Sub
Private Sub objBackup_Complete(ByVal Message As String)
lblProgress.Caption = "备份成功！ "
Set objBackup = Nothing
End Sub
Private Sub objBackup_PercentComplete(ByVal Message As String, ByVal Percent As Long)
lblProgress.Caption = "备份进度： " & Percent & "% "
DoEvents
End Sub
Private Sub objRestore_Complete(ByVal Message As String)
lblProgress.Caption = "恢复成功！ "
Set objRestore = Nothing
End Sub
Private Sub objRestore_PercentComplete(ByVal Message As String, ByVal Percent As Long)
lblProgress.Caption = "恢复进度： " & Percent & "% "
DoEvents
End Sub
[解决办法]
xp_cmdshell是SQL Server的扩展存储过程，你可以在网上搜索一下，用这个存储过程怎么实现远程备份的
[解决办法]
--你是否有足够的权限?如果有足够的权限,可以备份到本机
--如果有,可以用下面的方法,查询分析器中操作:
-- 创建共享目录
exec master.dbo.XP_cmdshell 'md c:\bak '
exec master.dbo.XP_cmdshell 'net share bak=c:\bak '
--进行数据备份
backup database 数据库 to disk= 'c:\bak\备份.bak '
/*--以下操作在我的电脑中进行:
打开我的电脑,地址栏中输入:
\\SQL服务器的计算机名\bak 复制里面的备份.bak到你的电脑就行了
--我的电脑中的操作结束--*/
--删除备份生成的文件
exec master..xp_cmdshell 'del c:\bak\备份.bak '
--删除共享
exec master.dbo.XP_cmdshell 'net share c:\bak /delete /y '
exec master.dbo.XP_cmdshell 'rd c:\bak '
--否则,你还是完全共享你本机的目录,用下面的方法备份:
backup database 数据库 to disk= '\\你的计算机名\共享目录名\备份文件名 '
Top
另外,通过网络备份,在我的电脑中能访问,但在SQL中不能访问的解决办法
---通过映射网络驱动器
--1.映射
exec master..xp_cmdshell 'net use z: \\xz\c$ " " /user:xz\administrator '
/*--说明:
z: 是映射网络路径对应本机的盘符,与下面的备份对应
\\xz\c$ 是要映射的网络路径
xz\administrator xz是远程的计算机名,administrator是登陆的用户名
--*/
--2.进行数据库备份
backup database 数据库名 to disk= 'z:\备份文件名 '
--3.备份完成后删除映射
exec master..xp_cmdshell 'net use z: /delete '
[解决办法]
说明：我没真正试过，你测试一下。
原创：邹建
'*************************************************************************
'**模 块 名：fBackupDatabase_a
'**描 述：备份数据库,返回出错信息,正常恢复,返回 " "
'**调 用：fBackupDatabase_a "备份文件名 ", "数据库名 "
'**参数说明：
'** sBackUpfileName 恢复后的数据库存放目录
'** sDataBaseName 备份的数据名
'** sIsAddBackup 是否追加到备份文件中
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人：邹建
'**日 期：2003年12月09日
'*************************************************************************
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$
On Error GoTo lbErr
'创建对象
Set iDb = New ADODB.Connection
'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj "
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
GoTo lbExit
lbErr:
iReturn = Error
lbExit:
fBackupDatabase_a = iReturn
End Function
'*************************************************************************
'**模 块 名：frestoredatabase_a
'**描 述：恢复数据库,返回出错信息,正常恢复,返回 " "
'**调 用：frestoredatabase_a "备份文件名 ", "数据库名 "
'**参数说明：
'** sDataBasePath 恢复后的数据库存放目录
'** sBackupNumber 是从那个备份号恢复
'** sReplaceExist 指定是否覆盖已经存在的数据
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人：邹建
'**日 期：2003年12月09日
'*************************************************************************
Public Function fRestoreDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sDataBasePath$ = " " _
, Optional ByVal sBackupNumber& = 1 _
, Optional ByVal sReplaceExist As Boolean = False _
) As String
Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&
On Error GoTo lbErr
'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset
'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj "
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
