Sometimes you will want to remove all access to a database for a period of time without detaching the database or deleting it. One option to achieve this is to take the database off-line. You can do this using standard SQL Server tools or Transact-SQL.
Using SQL Server Tools
The easiest way to take a SQL Server database off-line is using the graphical user interface tools provided. If you are using either SQL Server Management Studio or Enterprise Manager, you can simply right-click the name of a database. This causes a context-sensitive menu to appear. Within the "All Tasks" or "Tasks" submenu is the "Take Offline" option. Clicking this option takes the database off-line. You can return the database to an operational status by selecting "Bring Online" from the same menu.
ALTER DATABASE Command
The preferred manner in which to take a database off-line using Transact-SQL statements is with the ALTER DATABASE command. This command allows database administrators to modify database settings, files and filegroups.
Taking a Database Off-Line
The T-SQL for taking a database off-line is similar to that for setting restricted access mode. The simplest variation of the command is the following statement:
ALTER DATABASE database-name SET OFFLINE
The above command attempts to take the named database off-line immediately. If a user or a background process is currently connected to the database the command cannot be completed. In this situation, the ALTER DATABASE statement will be blocked and will wait until all connections are closed. This ensures that no transactions are rolled back unexpectedly. During the period of blocking, no new connections to the database will be permitted.
NB: Background processes that are running against SQL Server may cause the command to be indefinitely blocked, as can internal processes. The AUTO_UPDATE_STATISTICS_ASYNC in particular should be checked. If set to ON, the statement will be blocked.
Failing When Blocked
If you run the ALTER DATABASE command whilst users or processes may be connected, but you do not wish the command to be blocked, you can execute the statement with the NO_WAIT option. This causes the command to fail with an error.
ALTER DATABASE database-name SET OFFLINE WITH NO_WAIT
Forcing Disconnection of Users and Processes
In some cases it is appropriate to run the ALTER DATABASE command and allow it to be blocked until all of the current connections are closed. For SQL Servers that are used only internally to an organisation, each user can be contacted and asked to disconnect. Once they have, the command will complete automatically and the database will be inaccessible.
In other situations, for example a publicly accessible web site database, it may not be possible to ask users to disconnect. In these cases you may automatically disconnect users and to roll back their active transactions. This is achieved using the ROLLBACK clause.
The ROLLBACK clause can be used to immediately disconnect users or can be provided with a number of seconds to pause before taking the database off-line. To force the immediate disconnection of general users and processes and the rolling back of their transactions, use the following command:
ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK IMMEDIATE
If desired, you can specify that there should be a pause before disconnecting the users. During this period any attempts to connect to the database are refused but existing connections are given a number of seconds to complete their processing. The following example statement pauses for two minutes before disconnecting users:
ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK AFTER 120 SECONDS
Bringing a Database On-Line
Once you have completed any maintenance work and want to resume the use of the database, you can bring it back on-line using the ALTER DATABASE command:
ALTER DATABASE database-name SET ONLINE
No comments:
Post a Comment