OFFLINE - Taking a database offline in SQL Server

Q

How to set a database state to OFFLINE in SQL Server?

✍: FYIcenter.com

A

If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax:

ALTER DATABASE database_name SET OFFLINE

The following tutorial example will bring "FyiCenterComData" offline:

ALTER DATABASE FyiCenterComData SET OFFLINE
GO

SELECT name, state_desc from sys.databases
GO
name               state_desc
master             ONLINE
tempdb             ONLINE
model              ONLINE
msdb               ONLINE
FyiCenterComData   OFFLINE

USE FyiCenterComData
GO
Msg 942, Level 14, State 4, Line 1
Database 'FyiCenterComData' cannot be opened because 
   it is offline.

⇒ Moving Database Physical Files to New Locations in SQL Server

⇐ ONLINE/OFFLINE - Database States in SQL Server

⇑ Managing Databases and Physical Files in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-11-20, 4204🔥, 0💬

Database in Use When Renaming a Database in SQL Server
Why I am getting this error when renaming a database in SQL Server? If you are trying to rename a database that is in use, you will get an error message like this: "The database could not be exclusively locked to perform the operation." Before renaming a database, you must stop all client sessions u... 2016-11-20, 5157🔥, 0💬

OFFLINE - Taking a database offline in SQL Server
How to set a database state to OFFLINE in SQL Server? If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax: ALTER DATABASE database_name SET OFFLINE The following tutorial example will bring "FyiCenterComD... 2016-11-20, 4205🔥, 0💬

ONLINE/OFFLINE - Database States in SQL Server
What are database states in SQL Server? A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys.databases catalog view. The following table defines the database state... 2016-11-20, 3592🔥, 0💬

Location of Database Files in SQL Server
Where is my database stored on the hard disk in SQL Server? If a database is created with simple CREATE DATABASE statement, the server will create two database files on the hard disk to store data and configuration information about that data bases: database_name.mdf - SQL Server Database Primary Da... 2016-11-24, 3208🔥, 0💬

Managing Tables and Columns in SQL Server
Where to find answers to frequently asked questions on Managing Tables and Columns in SQL Server? Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on Managing Tables and Columns in SQL Server. Clear answers are provided with tutorial exercises on crea... 2016-11-20, 3145🔥, 0💬

Moving Database Physical Files to New Locations in SQL Server
How to move database physical files in SQL Server? If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, and link it to the files at the new location. The following tutorial gives you a good example: ALTER DATABASE F... 2016-11-20, 3007🔥, 0💬

SINGLE_USER/MULTI_USER - Database User Access Options in SQL Server
How to set database to be SINGLE_USER in SQL Server? Databases in SQL Server have three user access options: MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. This is the default. SINGLE_USER - One user at a time is allowed to connect to the databas... 2016-11-20, 2948🔥, 0💬

Creating Databases with Specified Physical Files in SQL Server
How to create database with physical files specified in SQL Server? If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement: CREATE DATABASE database_name ON (NAME = logical_data_name, FILENAME = physical_data_name,... 2016-11-24, 2938🔥, 0💬

Renaming Database Names in SQL Server
How to rename databases in SQL Server? If don't like the name of a database, you can change it by using the "ALTER DATABASE" statement with the following syntax: ALTER DATABASE database_name MODIFY NAME = new_database_name The tutorial example below shows you how change the database name from "FyiCe... 2016-11-24, 2915🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.