Generate Script To Bring All User Databases Online in SQL Server

You may often get into situations where moving the database files physically is necessary. Online databases won't let you perform these migrating tasks while they are "live," therefore you will need to take your database offline to complete the task. As shown below, you can accomplish it through the below T-SQL Scripts.

You can create scripts to change SQL Server databases from offline to online by using the query below.

SELECT 'ALTER DATABASE ['+name+'] SET ONLINE'+CHAR(10)+' GO'

FROM MASTER.sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','distribution')

AND state_desc='OFFLINE'

Bring Database Online Using T-SQL

Open a new query window in management studio (SSMS) and run the T-SQL statement below. The database name and the SQL phrases SET ONLINE are used in this basic ALTER DATABASE statement to put the database back online.

ALTER DATABASE [Database-Name] SET ONLINE

GO

Bring Database Online Using SSMS

1.     Login to SQL Server Management Studio.

2.     In the Object Explorer, right-click the offline database.

3.     In the right-click menu select Tasks >> Bring Online.