Backup and recovery database-zhonggu Project

Each system should have database backup and restoration functions to prevent irreparable losses caused by database damage in various circumstances. This function is quite simple, but some problems have been encountered in the code implementation process. I have summarized all the errors for your reference. The following provides the correct code implementation: ImportsSystem. Data. SqlClient

Each system should have database backup and restoration functions to prevent irreparable losses caused by database damage in various circumstances. This function is quite simple, but some problems have been encountered in the code implementation process. I have summarized all the errors for your reference. The following provides the correct code implementation: Imports System. Data. SqlClient

Each system should have database backup and restoration functions to prevent irreparable losses caused by database damage in various circumstances. This function is quite simple, but some problems have been encountered in the code implementation process. I have summarized all the errors for your reference.

The following provides the correct code implementation:

Imports System. data. sqlClientPublic Class Form1 'backup database' backup database Test to disk = ''WITH Format SQL statement Private Sub btnBackup_Click (sender As Object, e As EventArgs) Handles btnBackup. click Dim conn As SqlConnection conn = New SqlConnection ("Data Source = .; initial Catalog = test3; User ID = sa; Password = 123456 ") Dim cmd As SqlCommand Dim path As String 'select the backup path FolderBrowserDialog1.ShowDialog () path = Fo LderBrowserDialog1.SelectedPath If path = Nothing Then MessageBox. show ("file name cannot be blank", "system prompt") Exit Sub End If 'Execute SQL command statement, backup database cmd = New SqlCommand ("backup DATABASE test3 to disk = '" & path & "\ BACKUP' WITH format, backup log with norecovery", conn) conn. open () Try cmd. executeNonQuery () Catch ex As Exception MsgBox (ex. message, MsgBoxStyle. okOnly, "system prompt") Exit Sub End Try MsgBox ("backup successful") conn. close () End Sub 'Restore database Private Sub btnRecovery_Click (sender As Object, e As EventArgs) Handles btnRecovery. Click If MsgBox ("Do you really want to restore? The data will be restored to the latest backup! ", MsgBoxStyle. yesNo, "system prompt") = MsgBoxResult. yes Then Dim cn As New SqlConnection Dim cn1 As New SqlConnection Dim mydr As SqlDataReader Dim str As String Dim tmpConnectionString As String = "Data Source = .; initial Catalog = test3; User ID = sa; Password = 123456; pooling = false "Dim all As String 'Get the current file name filter character Me. openFileDialog1.Filter = "all files (*. *) | *. * | Backup File (*. bak) | *. bak "Me. openFileDialog1.ShowDialog () all = OpenFileDialog1.FileName If all = Nothing Then MessageBox. show ("file name cannot be blank", "system prompt") Exit Sub End If cn. connectionString = tmpConnectionString cn1.ConnectionString = tmpConnectionString cn. open () cn1.Open () 'queries the database-related process Dim cm As SqlCommand = New SqlCommand ("use master select spid from master .. sysprocesses where dbid = db_id ('test3') ", cn) mydr = cm. executeReader () Dim cm1 As SqlCommand = New SqlComma Nd () cm1.Connection = cn1 While (mydr. read () 'Kill the process str = "kill" & mydr ("spid "). toString () cm1.CommandText = str cm1.CommandType = CommandType. text Application. doEvents () cm1.ExecuteNonQuery () End While mydr. close () 'offline the DATABASE to be restored cm = New SqlCommand ("alter database test3 set offline with rollback immediate", cn) cm. executeNonQuery () 'Restore backup cm = New SqlCommand ("restore database test3 from disk = '" & Ll & "'with replace", cn) cm. executeNonQuery () 'Bring the DATABASE to be restored ONLINE cm = New SqlCommand ("alter database test3 set online with rollback immediate", cn) cm. executeNonQuery () MsgBox ("recovered successfully. The software is automatically disabled. Please restart the system! ") 'Close database connection cn. Close () cn1.Close () Me. Close () Else Exit Sub End If End SubEnd Class

Error Code 1:

Select spid from test3. sysprocesses where dbid = db_id ('test3 ')

Error description:

Solution:

Add the use master before the SQL statement.

Use master select spid from master .. sysprocesses where dbid = db_id ('test3 ')

User master indicates that the statement is executed in the master database. spid indicates the session ID of the current User process. master is the system database, which contains many objects and each object has its own owner. if no owner is specified, dbo is the object owner by default. the complete expression of an object is: Database Name. owner. object, such as: master. dbo. sysobjects. You must first find all the processes of the current database before the process is completed.

Error 2:

Use SQL statements to restore a database.

Error description:

Solution:

Method 1:

The database is taken offline before restoration, and then online after restoration. Add two SQL statements.

Alter database [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE

Alter database [datebase] SET ONLINE

Method 2:

(This method restricts the access to the database, because we often access multiple users, so we should consider this solution when it meets the situation .)

Other processes are connected to the database during restoration, which leads to unavailability of exclusive access. You can use the single-user mode of the database. The setting method is as follows: select the database to be restored --> attribute --> Option --> restrict access. The value is changed from MULTI_USER to SINGLE_USER.

The following is the GUI mode and the statement is relatively simple:

USE MASTER

GO

ALTER DATABASEeol_tcgroup SET SINGLE_USER with rollback immediate;

GO