Forcibly disable database connection in SQL Server

1.Problem Introduction

To prevent data exceptions, an error will be reported and your operation will be terminated.

 

Of course, in SQL 2005, an option [close so connection] is provided for selection when you [detach/delete] the database (it seems that SQL 2000 does not exist ), however, the [backup/restore] operation does not have this option. You may say that you can [offline database] And then [Backup and Restore, however, you will find that [offline] processing is too slow. J. What methods can be used to solve this problem in sql2005 and SQL 2000 ???

 

2.Solution:

1. Unplug the network cable of the machine.

2. Notify the user connected to this database to disconnect. If many users may be connected or do not know which user is connected, the connection will be unavailable.

3. Use the stoplogin command in SQL Server to forcibly disconnect the connection. The details are as follows:

Instructions for use:

Stoplogin @ dname

Where@ DnameName of the database to be forcibly disconnected. If you want to disconnect all connections of the database 'Demo', you only need to execute[Stoplogin 'Demo']If you want to disconnect all databases for maintenance, you only need to execute[Stoplogin '']You can.

 

The following is an example:

Ex1. comparison of SQL Execution results before and after forcible disconnection using stoplogin

First, execute the following SQL statement:

Use demo2

Go

Select top 1 * From invmb

The execution result is:

Query executed successfully

 

 

Run the following SQL statement:

Stoplogin 'demo2'

 

The execution result is:

Because the database 'demo2' is offline, the database cannot be opened.

 

3. StoploginCode details(Writing is in a hurry. Please forgive me for any bugs)

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [stoplogin] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [stoplogin] Go   Create procedure stoplogin @ Dname varchar (50) As Declare @ Name varchar (50 ), @ S varchar (1000) Begin If (@ dname = '') Begin Declare dataname cursor Select name from sysdatabases where name not in ('master ')   Open dataname   Fetch next from dataname Into @ name   While (@ fetch_status = 0) Begin Declare TB cursor local For Select n 'Kill '+ Cast (spid as varchar) From master .. sysprocesses Where dbid = db_id (@ name)   Open TB   Fetch next from TB into @ s   While @ fetch_status = 0 Begin Exec (@ s)   Fetch next from TB into @ s End   Close TB Deallocate TB Fetch next from dataname Into @ name End   Close dataname Deallocate dataname End Else Begin Declare TB cursor local For Select n 'Kill '+ Cast (spid as varchar) From master .. sysprocesses Where dbid = db_id (@ dname)   Open TB   Fetch next from TB into @ s   While @ fetch_status = 0 Begin Exec (@ s)   Fetch next from TB into @ s End   Close TB Deallocate TB End End