Use SQL statements to back up and restore SQL Server databases, and precautions

This article from: http://hi.baidu.com/yanzuoguang/blog/item/e94b2534d44b6b83a71e1255.html

Thank you for writing this article! After reading the database recovery statements written by so many authors, none of them mentioned that the restoration must be successful in single-user mode,

Not to mention: do not create stored procedures in the target database. For beginners, if they do not know these two prerequisites, they may encounter various problems.

 

Backup: backup database dbname to disk = '; D: \ DBN. ';
Recovery: Restore database dbname from disk = '; D: \ DBN. ';

Recovery can be successful only in single-user mode. We need to disconnect other users and switch to another database.

We can disconnect other users by writing the stored procedure.

I used to perform this experiment.
1. Find all connections to the database and kill all (using VB to call a stored procedure)
2. Use VB to call another written Stored Procedure for restoration,Note that neither of the two stored procedures should be created in the target database..

In my own project, I established the following killrubbishprocess stored procedure in the SQL server system to keep the database master

And write the preceding recovery statement as a stored procedure in the master.

Here I will provide you with the Stored Procedure for killing connections. It is very easy to restore the database.
Create procedure DBO. killrubbishprocess
Declare @ spidnum int
Declare rubbish_cursor cursor
Select spid
From master. DBO. sysprocesses
Where spid> 10 and spid <= 32767 and status = 'sleeping' and loginame = 'sa'
Order by spid

Open rubbish_cursor

Fetch next from rubbish_cursor
Into @ spidnum
Select s_kill = "kill" + Cast (@ spidnum as char (5 ))
While @ fetch_status = 0
Begin
Exec (s_kill)
Fetch next from rubbish_cursor
Into @ spidnum

End

Close rubbish_cursor
Deallocate rubbish_cursor
Go

The restoration statement can be called, but it must be ensured that when other users are killed for connection, other users cannot connect to the database during the recovery period.