Difference between Detaching database and bringing database Offline

SQL Server Offline and Detach Database




Detach Database/Attach Database: The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

you can attach a copied or detached SQL Server database. When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:

• For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.
  If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.
• If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.

Database Offline: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.


Use OFFLINE and ONLINE
1) If you are trying to make the database temporary unavailable for a period of time, you could take the database OFFLINE, and make it available by bringing it ONLINE whenever it is or you are ready.

2a) If you want to move the database or log files to different physical location or changing the database and/or log physical file name, AND keep them within the same SQL Server instance.
- Before you take the database offline, you need to know the logical name of the file. The name field is the logical name for the physical file name that you are about to change the path. Record the logical name for later use.

SELECT name, type, type_desc, physical_name, state, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase')

- Take the database OFFLINE.
ALTER DATABASE YourDatabase SET OFFLINE
WITH ROLLBACK IMMEDIATE;

-Move the database and/or log files to different location.
- Update the database and/or log files path registered on its SQL Server instance. Execute the T-SQL command for each database and log files,

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
FILENAME = 'Your Database or Log new physical path')

Bring the database ONLINE.
ALTER DATABASE YourDatabase SET ONLINE;

If you just want to change the logical name, you dont have to take the database OFFLINE. You could change it on SSMS or T-SQL.

Right click on the database > Properties > Select Page > Change it under Logical Name column > OK.

ALTER DATABASE YourDatabase 
MODIFY FILE (NAME = YourFileLogicalName,
NEWNAME = YourFileNewLogicalName)

2b) If you are relocating a file during part of the scheduled disk maintenance process, you may need to change the file path registered on its SQL Server instance before taking the database OFFLINE. This assure the server has the right path for the database and log files when the instance restart, after being shut down for maintenance.
- When the database and/or log file path is successfully updated on the instance, a message will show; 'The file "YourDatabaseFile" has been modified in the system catalog. The new path will be used the next time the database is started.'

Use DETACH and ATTACH,
1) If you want to move the database and log files to a different SQL Server instance or another server.
- Rollback active transaction and gain exclusive access. Detach the database. (optional: update statistics) 

ALTER DATABASE YourDatabase SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

EXEC sp_detach_db 'YourDatabase', 'true'

- Move database and log files to different location.
- Attach the database on different instance on same/another server. 

USE master;

CREATE DATABASE YourDatabase
ON (FILENAME = 'the physical path of your database file')
LOG ON (FILENAME = 'the physical path of your log file')
FOR ATTACH;

- If the database has previously set to single user, you may want to set it to multiple user access option.

ALTER DATABASE YourDatabase SET MULTI_USER

2) Even though you can choose to detach and attach the database back on the same SQL Server instance for scenario like changing physical path or file name, it is recommended to use the OFFLINE and ONLINE methods due to the restriction and limitation on DETACH. When you detach a database, you remove the database from the instance. It is required to remove the database from any participation of replication, mirroring and snapshot.

Reference: 

Query to List all the partitioned tables in SQL Server database

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]