Microsoft (News - Alert) explains that a database becomes offline by explicit user action and remains offline until additional user action is taken. However, I have seen that sometimes this is not true! So, we come to the question - Why does a database go into offline state?

A database goes offline mainly due to two distinct reasons:

• It can happen due to a software bug. For example, you’re using Microsoft SQL Server 2012 without the cumulative update package 1 for SQL Server 2012 Service Pack 1. If you try to rebuild an index and the transaction log fills during the rebuilding process, you will receive an error message and the database will be taken offline, and its status will be changed to "In Recovery" (KB2739940). Therefore, problems can occur in accessing the physical files that make up the database. For example, it can happen that the database is inserted into a SAN and network connectivity is lost.
• If you are using your database and the connection is lost even just for a temporary network glitch, your database will go offline (not immediately but after some time).

Now that we know the reasons that can take a database offline, let us see how to recover it.

How to Bring Back a Database to the Online State?
You can do the following to bring back the database to the Online state.

Open the SQL Server Management Studio (SSMS).

If you see that the state of one of the databases is Offline, the first place to look is the SQL Server error log. You can find the SQL Server Error Log under the Management / SQL Server menu.

Double-click on the Current item.

Choose the SQL Server item and then the current item to display the log.

In this case, I do not see any errors related to my database from the log. I can think that someone inadvertently took the database offline manually. To be safe, I copied the physical .mdf and .ldf files to a safe place.

Finally, try to bring the database online.

You can do this via T-SQL or using the SSMS. The result will be the same.

The database is back online.

From the Log of SQL Server, I can see that all is fine.

I can see in detail:

• The Starting Up phase.
• The beginning of the Parallel redo phase.
• The end of the Parallel redo phase.

Now another case.

Suppose, something happened that interrupted the connection to the database.

In case of network problems and the database is hosted on a SAN, physical files may get corrupted.

How do we proceed in this case?

To be safe, I copied the physical mdf and ldf files to a safe place. Then I tried to bring online the database. This time I received the following error:

The complete error message looks like:

It is clear that the mdf or the ldf or both files are damaged.

Looking at the SQL Server error log, we can see more information:

From the Log, we can see that SQL Server tries to start up the database but found a problem and shows the error message 824.

The error message 824 usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged.

The problem is that SQL Server reading the page (2:0) of the tpcc_log.ldf found a logical consistency of the data read.

Due to this problem, the error 5105 is generated.

This time, the process to bring the database to the online state may fail.

Below, we will briefly explain the two ways to resolve the issue.

1. Use T-SQL Commands

You can use the T-SQL commands to rebuild the corrupt log file and gain access to the database.

First, set the database in EMERCENCY MODE using the following command:

ALTER DATABASE <databasename> SET EMERGENCY, SINGLE_USER

When the database is in single user mode, you can execute the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to repair the corrupt transaction log file.

DBCC CHECKDB (<databasename> REPAIR_ALLOW_DATA_LOSS)

If corruption still exists, you cannot repair the transaction log file. So, you need to rebuild it.

Put the database in Offline state, then change the name of the corrupted log file. Then execute the following command:

ALTER DATABASE <databasename> REBUILD LOG ON (News - Alert) (NAME= logical name, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tpcc_log.ldf’)

2. Use a SQL Database Repair Software

Alternatively, you can use a dedicated software, such as Stellar Repair for MS SQL to recover your database. This software can parse the physical mdf file and extract the data. It can insert the extracted data into a new database with a new transaction log file. Every object will be copied from views to stored procedures.

The software usage is very friendly. You just need to select the mdf file and press the Repair button.

The program will start reading every data and object from the database. At the end of this phase, choose whether to save the data in a new database or live database. It also provides the other options to save the database like; CSV, HTML and XLS.

The software will prompt the ‘Save Complete’ message after saving the database