Are you working with SQL Server Management Studio (SSMS)and do you see one or more of your databases in a Recovery Pending state? Do you want to bring it online so that you have access to the database and its tables? Perhaps you moved the two database files (mdf and ldf) to a different drive and now find that after you try to bring it back online (in SSMS, right-click the database, Tasks, Bring Online), you can’t. Instead of Online, the status is Recovery Pending. This post provides a solution.

There are some instructions for moving your databases from one location to another. Have a look at the article called SQL Server Move Database Files Step By Step over at mssqltips.com. First, take the database offline. Next, physically move the data and log files to new locations. The next step is to ALTER the database to use new paths of its files. Set any necessary security permissions to the new location, if necessary. Finally, bring the database online.

How do you check the state of all your databases? You can run the following SQL query as is in a query window in SSMS.

SELECT name, state_desc from sys.databases 
GO

Set Emergency

The first step is to set emergency. Here is an article called How to fix Recovery Pending State in SQL Server Database? Following the first manual method, the first step is to set the database into emergency mode with the following code. In your example, you would change MikeTest to your database name.

ALTER DATABASE [MikeTest] SET EMERGENCY;
GO

What do you do if you get an error that shows the database in its incorrect location (path)? Below is an example of that error.

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\MSSQL\DATA\MikeTest.mdf". Operating system error 3: "3(The system cannot find the path specified.)".

Below is the code we would use to change the location over to the D drive. We have already physically moved the two files over to the D drive but the database thinks that the files are on the C drive. Now let’s tell the database that it now will be living on the D drive with the following commands. In your example, you would change MikeTest to your database name and change the location to where the database file now reside.

USE master
GO
ALTER DATABASE MikeTest
MODIFY FILE (NAME = MikeTest, FILENAME = 'D:\MSSQL\DATA\MikeTest.mdf')
ALTER DATABASE MikeTest 
MODIFY FILE (NAME = MikeTest_log, FILENAME = 'D:\MSSQL\DATA\MikeTest_log.ldf')

The next command is to set single user.

ALTER DATABASE [MikeTest] set single_user
GO

The next command is to run DBCC CHECKDB. Here is the command.

DBCC CHECKDB ([MikeTest], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO

Access is Denied

You might get an error after running the above command. If you get the access is denied error, you can go into the file using File Explorer and set the Security on the two files. You could set Users to have Full Control.

The second-last command is to set it to multi user.

ALTER DATABASE [MikeTest] set multi_user
GO

Lastly just bring the database online.

ALTER DATABASE [TestMike] SET ONLINE;
GO