//备份代码
string sql = @"BACKUP DATABASE test1 TO DISK = N'd:\databack\test2.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10,NOFORMAT";
SqlConnection conn = new SqlConnection("server=192.168.0.200;database=test1;User Id=sa;pwd=123;"); conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();//还原代码(不能执行成功)
string sql = @"restore database test2 from disk = 'd:\databack' with file = 'test2.bak',nounload,stats = 10,recovery";
SqlConnection conn = new SqlConnection("server=192.168.0.200;database=test2;User Id=sa;pwd=123;");
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();我想实现将test1数据库还原来test2的数据库
但总是不能成功,请各位大大帮帮忙啊,在线等。
string sql = @"BACKUP DATABASE test1 TO DISK = N'd:\databack\test2.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10,NOFORMAT";
SqlConnection conn = new SqlConnection("server=192.168.0.200;database=test1;User Id=sa;pwd=123;"); conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();//还原代码(不能执行成功)
string sql = @"restore database test2 from disk = 'd:\databack' with file = 'test2.bak',nounload,stats = 10,recovery";
SqlConnection conn = new SqlConnection("server=192.168.0.200;database=test2;User Id=sa;pwd=123;");
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();我想实现将test1数据库还原来test2的数据库
但总是不能成功,请各位大大帮帮忙啊,在线等。
MOVE 'test1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test2.mdf',
MOVE 'test1Log1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test2.ldf'
这样就可以了,你试一下
这样就可以了
'清除用户连接 Private Sub killAllUser()
Dim sqlstr As String
Dim myadapter As SqlDataAdapter
Dim mydataset As New DataSet
Dim constr As String = "data source=.;initial catalog=master;uid=sa;pwd=sa;"
Dim mysqlconn As SqlConnection = New SqlConnection(constr)
Try
mysqlconn.Open() '连接master数据库,打开连接
'查出所有用户的spid
sqlstr = "select spid from sysprocesses where dbid=(select dbid from sysdatabases where name like '数据库名')"
myadapter = New SqlClient.SqlDataAdapter(sqlstr, mysqlconn)
myadapter.Fill(mydataset, "sysprocesses") Dim i As Integer
Dim mysqlcomm As SqlCommand
For i = 0 To mydataset.Tables(0).Rows.Count - 1
'删除用户spid
Dim strkill As String
strkill = "kill " + CType(mydataset.Tables(0).Rows(i).Item(0), String)
mysqlcomm = New SqlCommand(strkill, mysqlconn)
mysqlcomm.ExecuteNonQuery()
Next
mysqlconn.Close()
Catch ex As Exception
If Not mysqlconn.State = ConnectionState.Closed Then
mysqlconn.Close()
End If
Throw ex
End Try End Sub ''' <summary>
''' 还原数据库
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <res></res>
Private Sub 还原数据RToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 还原数据RToolStripMenuItem.Click
Dim mysqlcomm As SqlCommand = New SqlCommand
Dim strsql As String = String.Empty
Dim strbak As String = String.Empty
Dim fo As OpenFileDialog = New OpenFileDialog
fo.DefaultExt = ".bak"
fo.InitialDirectory = Application.StartupPath
fo.Filter = "数据库文件|*.bak|所有文件|*.*"
If fo.ShowDialog = Windows.Forms.DialogResult.OK Then
Try
killAllUser() 'kill掉所用用户的方法见下面的代码
Catch ex As Exception
Throw ex
End Try
strbak = fo.FileName
strsql = "restore database sincehome from disk='" + 数据库路径 + "' with recovery" '数据库恢复语句
Dim constr As String = "data source=.;initial catalog=master;uid=sa;pwd=sa;"
Dim mysqlconn As SqlConnection = New SqlConnection(constr)
mysqlconn.Open()
mysqlcomm = New SqlCommand(strsql, mysqlconn)
mysqlcomm.ExecuteNonQuery()
strsql = "alter database sincehome set online"
mysqlcomm = Nothing
mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)
mysqlcomm.ExecuteNonQuery()
mysqlconn.Close()
mysqlcomm.Dispose()
MsgBox("数据库还原成功", MsgBoxStyle.OkOnly, "提醒")
End If
End Sub