Common problems of SQL Server database recovery

Time:2021-4-19

When dealing with database restore, the database is in use error message often appears.
Common problems of SQL Server database recovery
As the name suggests, SQL server refuses to let anyone restore the database because someone or an application is still using the database. The solution is very simple, is to use sp_ Who 2 finds out the SPID (server process ID) of the connection, and then kills the SPID with the kill command. You can continue to restore the database.
Common problems of SQL Server database recovery
If there are only one or two SPIDS to kill, you can use the kill instruction slowly, but if there are dozens or hundreds of SPIDS to kill, you must use a more scientific method. You can use this T-SQL to kill all SPIDS at once
Common problems of SQL Server database recovery
However, if you encounter some aggressive and sticky link settings, it will be a bit troublesome. When the SPID of this link is killed, a new link will be automatically generated immediately. There’s no way to kill it completely. At this time, you can take out the trump card, which is to find the highly aggressive login, and then disable the login, so that it can’t connect to the database. After the database is restored, enable the login
Common problems of SQL Server database recovery

This work adoptsCC agreementReprint must indicate the author and the link of this article