How to Take Database Offline and Bring it Online - SQL Server DBA Tutorial

How to Take Database Offline and Bring it Online

In Microsoft SQL Server, you can take a database offline and bring it back online using Transact-SQL (T-SQL) commands. To do this, you can use the ALTER DATABASE statement with the SET OFFLINE and SET ONLINE options.

1. Take a database offline:

To take a database offline, use the following command:

USE master;

GO

 

-- Replace 'YourDatabaseName' with the name of your database

 

ALTER DATABASE YourDatabaseName SET OFFLINE;

 

This command will set the specified database (YourDatabaseName in this case) to offline mode. The database will not be accessible by users or applications until you bring it back online.

 

2. Bring a database online:

To bring a database back online, use the following command:

USE master;

GO

 

-- Replace 'YourDatabaseName' with the name of your database

 

ALTER DATABASE YourDatabaseName SET ONLINE;

This command will set the specified database (YourDatabaseName in this case) to online mode, making it accessible again for users and applications.

 

Remember to execute these commands with appropriate permissions. Usually, you need to be a member of the db_owner or sysadmin role to perform these actions. Also, ensure that there are no active connections to the database before taking it offline, as it might cause data inconsistency or other issues. Always plan for any downtime and communicate with users or applications that rely on the database to avoid disruptions.

 

To Fix In Recovery 

ALTER DATABASE [DB_Name] SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [DB_Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO