Agent Jobs

Take Database Offline And Online

Here’s some quick SQL logic that will take the database offline, then online again. It’s helpful for maintenance reference.

use master;
set nocount on

-- check if database is online.  if so proceed to take offline.  if not; do nothing.
if exists(select state_desc from sys.databases where name = 'MyDatabase' and state_desc = 'online')
	begin
		alter database [MyDatabase] set offline with rollback immediate;
	end
	else
		print 'The database is already offline. No changes will be made';

-- confirm the database is OFFLINE.
select
	'server_name'		= upper(@@servername)
,	'database_name'		= upper(name)
,	'is_offline'		= state_desc
from
	sys.databases
where
	name = 'MyDatabase';

-- set database online
alter database [MyDatabase] set online;

-- confirm the database is ONLINE.
select
	'server_name'		= upper(@@servername)
,	'database_name'		= upper(name)
,	'is_offline'		= state_desc
from
	sys.databases
where
	name = 'MyDatabase';

use master;
set nocount on

-- check if database is online.  if so proceed to take offline.  if not; do nothing.
if exists(select state_desc from sys.databases where name = 'MyDatabase' and state_desc = 'online')
	begin
		alter database [MyDatabase] set offline with rollback immediate;
	end
	else
		print 'The database is already offline. No changes will be made';

-- confirm the database is OFFLINE.
select
	'server_name'		= upper(@@servername)
,	'database_name'		= upper(name)
,	'is_offline'		= state_desc
from
	sys.databases
where
	name = 'MyDatabase';

-- set database online
alter database [MyDatabase] set online;

-- confirm the database is ONLINE.
select
	'server_name'		= upper(@@servername)
,	'database_name'		= upper(name)
,	'is_offline'		= state_desc
from
	sys.databases
where
	name = 'MyDatabase';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s