出错提示:Microsoft][ODBC SQL Server Driver][SQL Server]无法重写文件 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Data.MDF'。数据库 'test' 正在使用该文件。
[Microsoft][ODBC SQL Server Driver][SQL Server]文件 'test_Data' 无法还原为 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Data.MDF'。请使用 WITH MOVE 选项来标识该文件的有效位置。
[Microsoft][ODBC SQL Server Driver][SQL Server]无法重写文件 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF'。数据库 'test' 正在使用该文件。
[Microsoft][ODBC SQL Server Driver][SQL Server]文件 'test_Log' 无法还原为 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF'。请使用 WITH MOVE 选项来标识该文件的有效位置。
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE 操作异常终止。
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(".", "sa", "sa");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "mydb";
oRestore.Files = @"d:Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = false;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}这也我要网上找的,但我运行出错,请各位帮帮忙
[Microsoft][ODBC SQL Server Driver][SQL Server]文件 'test_Data' 无法还原为 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Data.MDF'。请使用 WITH MOVE 选项来标识该文件的有效位置。
[Microsoft][ODBC SQL Server Driver][SQL Server]无法重写文件 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF'。数据库 'test' 正在使用该文件。
[Microsoft][ODBC SQL Server Driver][SQL Server]文件 'test_Log' 无法还原为 'C:\Program Files\Microsoft SQL Server\MSSQL\data\test_Log.LDF'。请使用 WITH MOVE 选项来标识该文件的有效位置。
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE 操作异常终止。
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(".", "sa", "sa");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "mydb";
oRestore.Files = @"d:Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = false;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}这也我要网上找的,但我运行出错,请各位帮帮忙
Public Function fBackupDatabase_a(ByVal sBackUpfileName$ _
, ByVal sDataBaseName$ _
, Optional ByVal sIsAddBackup As Boolean = False _
) As Boolean Dim Conn As SqlClient.SqlConnection
Dim Cmd As SqlClient.SqlCommand
Dim iConcStr, iSql, iReturn As String
Dim Msg As String
Dim boolReturn As Boolean = True Try
'连接数据库服务器.(用户必须有足够的权限)
iConcStr = System.Configuration.ConfigurationSettings.AppSettings("ConnString")
Conn = New SqlClient.SqlConnection(iConcStr)
Conn.Open() 'BACKUP DATABASE [Mder]
'TO DISK = N'D:\Backup\Database\Mder.bak',
'DISK = N 'F:\Mder' WITH NOINIT , NOUNLOAD ,
'NAME = N 'Mder 备份', NOSKIP , STATS = 10, NOFORMAT '生成数据库备份语句
iSql = "backup database [" & sDataBaseName & "]" & vbCrLf & _
"to disk=N'" & sBackUpfileName & "'" & vbCrLf & _
"with description='" & "xu-backup at:" & Now() & "'" & vbCrLf & _
IIf(sIsAddBackup, "", ",init") Cmd = New SqlClient.SqlCommand(iSql, Conn)
Cmd.ExecuteNonQuery()
Catch ex As System.Exception
boolReturn = False
Msg = ex.Message()
End Try
Return boolReturn End Function
, ByVal sDataBaseName$ _
, Optional ByVal sDataBasePath$ = "" _
, Optional ByVal sBackupNumber As Int16 = 1 _
, Optional ByVal sReplaceExist As Boolean = False) As String Dim Conn As SqlClient.SqlConnection
Dim Cmd As SqlClient.SqlCommand
Dim iConcStr, iSql, iI As String
Dim iReturn As String
Dim Adap As New SqlDataAdapter
'Dim boolReturn As Boolean = True
Dim ds As DataSet
Dim I As Int32
Try '连接数据库服务器.(用户必须有足够的权限)
iConcStr = System.Configuration.ConfigurationSettings.AppSettings("ConnString")
Conn = New SqlClient.SqlConnection(iConcStr)
Conn.Open() '得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
If sDataBasePath = "" Then
Try
iSql = "select filename from master..sysfiles"
Cmd = New SqlCommand(iSql, Conn)
iSql = Cmd.ExecuteScalar
sDataBasePath = Microsoft.VisualBasic.Left(iSql, InStrRev(iSql, "\"))
Catch ex As Exception
iReturn = "查找数据库存放目录出错!" + ex.Message
GoTo TestReturn
End Try End If '检查数据库是否存在
If sReplaceExist = False Then
Try
iSql = "select 1 from master..sysdatabases where name='" & sDataBaseName & "'"
Cmd = New SqlCommand(iSql, Conn)
iSql = ""
iSql = Cmd.ExecuteScalar
If iSql <> "" Then
iReturn = "数据库已经存在!"
GoTo TestReturn
End If
Catch ex As Exception
iReturn = "查找数据库是否已经存在出错!" + ex.Message
GoTo TestReturn
End Try
End If '关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
Try
iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
ds = New DataSet
Adap = New SqlDataAdapter(iSql, Conn)
Adap.Fill(ds)
For I = 0 To ds.Tables(0).Rows.Count - 1
iSql = "kill" & ds.Tables(0).Rows(I)("spid")
Cmd = New SqlCommand(iSql, Conn)
Cmd.ExecuteNonQuery()
Next
Catch ex As Exception
iReturn = "关闭用户进程出错!" + ex.Message
GoTo TestReturn
End Try '获取数据库恢复信息
iSql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber
Adap = New SqlDataAdapter(iSql, Conn)
Adap.Fill(ds) '生成数据库恢复语句
iSql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
"from disk='" & sBackUpfileName & "'" & vbCrLf & _
"with file=" & sBackupNumber & vbCrLf
For I = 0 To ds.Tables(0).Rows.Count - 1
iReturn = ds.Tables(0).Rows(I)("PhysicalName")
iI = InStrRev(iReturn, ".")
iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
iSql = iSql & ",move '" & ds.Tables(0).Rows(I)("LogicalName") & _
"' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
Next
iSql = iSql & IIf(sReplaceExist, ",replace", "")
Cmd = New SqlCommand(iSql, Conn)
Cmd.ExecuteNonQuery()
iReturn = "数据库还原成功!"
Catch ex As Exception
iReturn = "恢复数据库出错!" + ex.Message
GoTo TestReturn
Finally
Conn.Close()
End TryTestReturn:
Return iReturn End Function
[email protected]
Alter database es Set Offline With rollback immediate
Alter database es Set Online With Rollback immediate
这是一个SQL语句 修改对应的数据库名和备份文件即可
用此数据库脚本就可以了,我之前也遇到了你这样的问题
restore database 数据库名 from disk = N'数据库路径' with replacehttp://topic.csdn.net/u/20100417/08/9efd18f2-167c-4b40-9b26-58637cacba46.html
我有一个可以在局域网内异机备份、还原sql数据库的动态库,要的话联系我:QQ962464727
看看这个:
源码下载地址:
http://www.codeproject.com/KB/dotnet/SQL_Server_backup.aspx