OFFLINE - Taking a database offline in SQL Server

Q

How to set a database state to OFFLINE in SQL Server?

✍: FYIcenter.com

A

If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax:

ALTER DATABASE database_name SET OFFLINE

The following tutorial example will bring "FyiCenterComData" offline:

ALTER DATABASE FyiCenterComData SET OFFLINE
GO

SELECT name, state_desc from sys.databases
GO
name               state_desc
master             ONLINE
tempdb             ONLINE
model              ONLINE
msdb               ONLINE
FyiCenterComData   OFFLINE

USE FyiCenterComData
GO
Msg 942, Level 14, State 4, Line 1
Database 'FyiCenterComData' cannot be opened because 
   it is offline.

 

Moving Database Physical Files to New Locations in SQL Server

ONLINE/OFFLINE - Database States in SQL Server

Managing Databases and Physical Files in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-11-20, 2367🔥, 0💬