T-SQL Script to Take All User Databases Offline

DYNQ_OflineAlldb.sql

DECLARE @dbName NVARCHAR(255)
DECLARE @sql NVARCHAR(MAX)

-- Create a cursor to iterate over all databases except system databases
DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') -- Exclude system databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the dynamic SQL statement to take the database offline
    SET @sql = 'ALTER DATABASE [' + @dbName + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;'
    
    -- Execute the dynamic SQL statement
    EXEC sp_executesql @sql
    
    -- Fetch the next database name
    FETCH NEXT FROM db_cursor INTO @dbName
END

-- Close and deallocate the cursor
CLOSE db_cursor
DEALLOCATE db_cursor

To take all databases offline in a SQL Server instance using T-SQL, you can create a script that iterates over all databases and sets each one to the OFFLINE state. Here's a T-SQL script to accomplish this:

Explanation:

1. Cursor Declaration:

   • A cursor named db_cursor is declared to iterate over all database names in the sys.databases catalog view, excluding the system databases (master, model, msdb, tempdb).

2. Cursor Open and Fetch:

   • The cursor is opened, and the first database name is fetched into the @dbName variable.

3. While Loop:

   • A WHILE loop runs as long as there are database names to process (@@FETCH_STATUS = 0).

4. Dynamic SQL Construction:

   • Inside the loop, a dynamic SQL statement is constructed to take the current database offline. The WITH ROLLBACK IMMEDIATE clause is used to immediately rollback any active transactions and forcefully disconnect all users from the database.

5. SQL Execution:

   • The constructed SQL statement is executed using sp_executesql.

6. Fetch Next:

   • The cursor fetches the next database name into the @dbName variable.

7. Cursor Closure and Deallocation:

   • After all databases have been processed, the cursor is closed and deallocated to release resources.

Note: Be very careful when running this script, especially on a production server, as it will forcefully take all user databases offline. Always ensure you have appropriate backups and have notified relevant stakeholders before executing such operations.