Making a Database Offline or Online in SQL Server

In SQL Server, you can make a database offline or online using the `ALTER DATABASE` statement. This is useful for maintenance, backup, or restore operations.

Making a Database Offline

To make a database offline, you can use the following syntax:

ALTER DATABASE database_name SET OFFLINE; 

For example, to make the `MyDatabase` database offline:

ALTER DATABASE MyDatabase SET OFFLINE; 

This will make the database unavailable for connections and queries.

Example

Let's say we want to make the `MyDatabase` database offline for maintenance:

ALTER DATABASE MyDatabase SET OFFLINE; 

After executing this command, the database will be offline and no connections will be allowed.

Making a Database Online

To make a database online, you can use the following syntax:

ALTER DATABASE database_name SET ONLINE; 

For example, to make the `MyDatabase` database online:

ALTER DATABASE MyDatabase SET ONLINE; 

This will make the database available for connections and queries again.

Example

Let's say we want to make the `MyDatabase` database online after maintenance:

ALTER DATABASE MyDatabase SET ONLINE; 

After executing this command, the database will be online and connections will be allowed again.

Checking Database Status

To check the status of a database, you can use the `sys.databases` system view:

SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase'; 

This will return the current status of the database, which can be one of the following:

• ONLINE
• OFFLINE
• RESTORING
• RECOVERING
• SUSPECT