The primary reason why a database is made in an offline state is that they are not in use, or they are redundant. 

There are multiple ways through which the database can be made offline 

1. Using T-SQL Script. 
2. Using GUI (SSMS Tool) 

1. Using T-SQL Script.

Prior to taking the database offline please make sure to check below things:

1. If there is any active session
          Use T-SQL Command
           sp_who2 ‘active’;
2. By default, SQL has 50 standard processes running in the background which are SQL-related and cannot be killed. If there is anything above 50 it can be killed. Use t-SQL command
   select * from sys.sysprocesses
   where spid >50;
3. Take full backup of databases using below T-SQL Query
   BACKUP DATABASE [MYDB] TO DISK = N’D: File location\MYDB.bak’ WITH NOFORMAT, NOINIT, NAME = N’MYDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
   GO

Now use alter command, to set database in offline

— Take the Database Offline
Use Master
Go
ALTER DATABASE [MYDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MYDB] SET OFFLINE
GO

Considering a career in cloud?

2. Using GUI (SSMS Tool)

Before taking the database offline please make sure to check below things:

1. If there are any active session
          Use T-SQL Command
           sp_who2 ‘active’;
2. By default, SQL has 50 processes running in background which are SQL related and cannot be killed. If there is anything above 50 it can be killed. Use t-SQL command
    select * from sys.sysprocesses
   where spid >50;
3. Take full backup of databases using below T-SQL Query
   BACKUP DATABASE [MYDB] TO DISK = N’D: File location\MYDB.bak’ WITH NOFORMAT, NOINIT, NAME = N’MYDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
   GO

Now use GUI (SSMS TOOL), to set database in offline

• Login to SSMS TOOL
• Expand the Server Tab on Which the database is hosted
• Expand Database Tab

Right click on the database > go to Task > Take Offline.

Author: Sanmesh Dheb, Systems Administrator, Fusion Practices