--新建存储过程，清除连接数据库的所有连接****************
CREATE PROCEDURE [dbo].[StopLogin]
@Dname varchar(50)
AS
DECLARE
@name varchar(50),
@s varchar(1000)
BEGIN
IF (@Dname = '')
BEGIN
DECLARE DataName CURSOR FOR
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
GO
exec StopLogin 'ckgwbndb20-10-12'
go
-----end**********************************************
--第二步新建作业,定时执行语句备份
declare @name varchar(250)
set @name='F:\Backup\TestDB1_'+
convert(varchar(50),getdate(),112)+'.bak';
BACKUP DATABASE[ckgwbndb20-09-07] TO
DISK=@name
WITH NOFORMAT, NOINIT,
NAME = N'TestDB1-完整 数据库 备份',
SKIP, NOREWIND, NOUNLOAD
restore
database [ckgwbndb20-07-09]
from disk=@name
with file=1,
REPLACE,
recovery
