SQL Server database restoration experience

In the past few days, the restoration function of the ms SQL database is used. One problem is that the current user is using the database and cannot recover it. I checked some posts, mainly through the stored procedure. A few days ago, I found a class that can be restored, and then I asked the students to solve the problem with their methods. The main principle is to turn off the current user by another user, and then use the new user for recovery. The new user has higher permissions.

The recovery code is as follows:

If f = "" Then 'f is the backup file name
Msgbox "select backup file", vbokonly + vbinformation, "prompt"
Else

CNN. Close 'Shut down the current user process first
Cnn2.open "provider = sqloledb.1; Integrated Security = sspi; persist Security info = false; Data Source = (local)" 'open a new user process. Use Windows to verify logon, you can also use another user, just change the connection string.



SQL = "select spid from Master... sysprocesses where dbid = db_id ('devic')" 'queries users who are using the device Database
Set rst = cnn2.execute (SQL)

While RST. EOF = false
SQL = "kill" & RST. Fields (0) 'Close the user process in use
Cnn2.execute SQL 'note that cnn2 is executed by a new process.
RST. movenext
Wend
RST. Close

SQL = "Restore database device from disk = '" & F & "'" 'Restore database command
Cnn2.execute SQL

Cnn2.close
Set cnn2 = nothing 'cnn2 fulfill its mission and completely release it

CNN. open' and then open the original connection
Msgbox "recovery successful", vbokonly + vbinformation, "prompt"
End if

This method avoids the inconvenience caused by stopping the Database Service and ensures that the original connection can continue to be used. The overall feeling is more convenient.