To quickly disconnect all non admins from a database, I use this:
ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
One of the development teams recently requested that their production databases be copied to a development server on a weekly basis. In the development environment, developers have db_owner access to all of their databases.
In order to restore a database, no one can be connected to it if it already exists. Since my ALTER DATABASE statements will only work on non admins, I also need to run this to kick out developers:
DECLARE @spid varchar(10)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid IN (DB_ID('Database1'), DB_ID('Database2'))
WHILE @@ROWCOUNT <> 0
BEGIN
EXEC('KILL ' + @spid)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE
dbid IN (DB_ID('Database1'), DB_ID('Database2')) AND
spid > @spid
END
Most SQL Server DBAs have a similar script to this, but I thought I'd post mine in case you don't have one already.
NOTE: I can't run the following commands as we are using SQL Litespeed. Their extended stored procedure to restore databases, master.dbo.xp_restore_database, uses multiple connections to the database.
ALTER DATABASE Database1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
One of the development teams recently requested that their production databases be copied to a development server on a weekly basis. In the development environment, developers have db_owner access to all of their databases.
In order to restore a database, no one can be connected to it if it already exists. Since my ALTER DATABASE statements will only work on non admins, I also need to run this to kick out developers:
DECLARE @spid varchar(10)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid IN (DB_ID('Database1'), DB_ID('Database2'))
WHILE @@ROWCOUNT <> 0
BEGIN
EXEC('KILL ' + @spid)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE
dbid IN (DB_ID('Database1'), DB_ID('Database2')) AND
spid > @spid
END
Most SQL Server DBAs have a similar script to this, but I thought I'd post mine in case you don't have one already.
NOTE: I can't run the following commands as we are using SQL Litespeed. Their extended stored procedure to restore databases, master.dbo.xp_restore_database, uses multiple connections to the database.
ALTER DATABASE Database1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
