Search notes:

SQL Server: offlining and onlining a database

db-off-on.sql is a script, to be called with sqlcmd, that offlines and onlines a database.

I usually use this script to maker sure that no session is running in a particular database, for example, before executing a deployment script etc.

When the script is called, the name of the database needs to be stored in the sqlcmd variable DB, which can be set with the -v option:

P:\ath\to\script\dir>  sqlcmd -S server_name -i db-off-on.sql -v DB=database_name

Note that the database is offlined with the rollback immediate option. That means that modifications of currently running transactions are lost.

--
-- sqlcmd -S … -i db-off-on.sql -v DB=…
--

use master
go

print ('');
print ('Current connections');
print ('');

select
   ses.session_id,
   con.connect_time,
   substring(ses.login_name, 1, 20) login,
   cast(replace(
        replace(
          substring(sql.text, 1, 50),
          char(10),
          ' '
       ), char(13), ' ') as varchar(50)) sql_text
from
   sys.dm_exec_connections                          con                                            join
   sys.dm_exec_sessions                             ses on con.session_id = ses.session_id  cross apply
   sys.dm_exec_sql_text(con.most_recent_sql_handle) sql 
 where
    db_name(ses.database_id) = "$(DB)";
go

print ('offlinging database');
alter database $(DB)
  set offline with rollback immediate
go

print ('onlining database');
alter database $(DB)
  set online;
go

Github repository about-MSSQL, path: /scripts/db-off-on.sql

Index