How to make On-Premises databases offline
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
- Using T-SQL Script.
- Using GUI (SSMS Tool)
1. Using T-SQL Script.
Prior to taking the database offline please make sure to check below things:
- If there is any active session
Use T-SQL Command
sp_who2 ‘active’; - 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; - 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:
- If there are any active session
Use T-SQL Command
sp_who2 ‘active’; - 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; - 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.