关于数据库SQL备份和还原

本方法是将数据库备份到本地磁盘

 

 

备份：

use master
Exec sp_addumpdevice 'disk','B2CSystem','d:\DB\myBck.bck'
--创建备份设备
Backup database B2CSystem to B2CSystem
--执行备份

 

 

还原：

use master

RESTORE DATABASE B2CSystem FROM DISK='d:\DB\myBck.BAK'

 

注意：因为数据不是独占打开.可能用到下面的过程

 1: --Kill掉访问某个数据库的连接

   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