SQL Server backup and Restoration

1 :/*

2. Back up a database using SQL statements

   3: */

   4: BACKUP DATABASE mydb

   5: TO DISK ='C:\DBBACK\mydb.BAK' 

6: -- specify the path and file name of the database to be backed up. Note: The Path folder must have been created. The file name can be identified by date.

   7:  

   8: /*

9: restore a database using SQL statements

  10: */

  11: USE master

  12: RESTORE DATABASE mydb

  13: FROM DISK='C:\DBBACK\mydb.BAK'

  14: WITH REPLACE

Note: in many cases, the data cannot be restored directly because it is not exclusively opened. The following process may be used.

1: -- kill the connection to a database

   2: CREATE PROC KillSpid(@DBName varchar)

 

   3: AS

 

   4: BEGIN

 

   5:     DECLARE @SQL varchar

 

   6:     DECLARE @SPID int

 

   7:     SET @SQL='DECLARE CurrentID CURSOR FOR 

 

   8:     SELECT spid FROM sysprocesses WHERE dbid=db_id('''+@DBName+''') '

 

   9:     FETCH NEXT FROM CurrentID INTO @SPID

 

  10:     WHILE @@FETCH_STATUS <>-1

 

  11:     BEGIN

 

  12:         exec('KILL '+@SPID)

 

  13:         FETCH NEXT FROM  CurrentID INTO @SPID

 

  14:     END

 

  15:     CLOSE CurrentID

 

  16:     DEALLOCATE CurrentID

 

  17: END

 

It is best to use a single user to operate the database after kill the user

 

SP_DBOPTION @DBName,'single user','true'