Recovery Pending State in SQL Server? Five Ways to Fix It

What Does Recovery Pending Mean in SQL Server?

SQL Server reaches the RECOVERY_PENDING state when a resource error stops recovery during startup. Microsoft describes the database as not damaged and says the files are missing or system limits prevent the start.

Your database remains unavailable until you resolve the error and let recovery finish. Microsoft lists every state in its Database States Guide.

Why Does SQL Server Set This State?

Five causes explain most recovery pending databases.

• Wrong Permissions. OS error 5 means Windows denies the SQL Server service access to the database file.
• Moved or Missing Files. OS error 3 means the file path stored in SQL Server points to a file that does not exist.
• File in Use. OS error 32 means another process, for example antivirus software, uses the file.
• Storage Not Ready. Network and iSCSI locations must be available before SQL Server starts.
• Low Resources. Error 945 appears when files are unreachable or memory and disk space are short.

Which Errors Put a Database in Recovery Pending?

Two startup errors name the failed file in the error log.

Microsoft explains in its Error 17204 Guide that SQL Server opens files with the Windows CreateFile function. OS error numbers in the message name the root cause.

How to Find the Affected Database in SSMS?

Open New Query, paste the query below and execute it. Rows in the result are your recovery pending databases.

SELECT d.name, d.state_desc, d.user_access_desc
FROM sys.databases AS d
WHERE d.state_desc = 'RECOVERY_PENDING';

List the file names and paths of the database next. Method two needs the logical name from this query.

SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'SalesDB');

Open SQL Server Logs under Management and select the current log. Search for 17204 or 17207 to find the OS error number.

What to Check Before the Fix?

Protect the data files before you alter anything.

• Files. Shut down the instance and duplicate every surviving database file on a separate disk.
• Account. Find the service account in SQL Server Configuration Manager.
• Paths. Compare the paths from sys.master_files with the real files in File Explorer.
• Backups. Locate the latest full backup plus all log backups after it.
• Space. Check that the disk for the database files has free capacity.

Which Free Methods Fix a Recovery Pending Database?

Four free methods remove the state. Start with method one because most cases end there.

Fix the OS Error and Set the Database Online (Free)

Microsoft lists the same fix for every OS error code. Resolve the error and bring the database online.

1. Find the OS error number in the 17204 or 17207 message.
2. For error 5, grant the service account of the instance access to the file. Microsoft names the group SQLServerMSSQLUser$ComputerName$MSSQLSERVER for a default instance.
3. For error 3, correct the path or restore the missing file.
4. For error 32, find the process that uses the file and stop it. Add the SQL Server database files to the exclusions of your antivirus software.
5. Execute the command below.

ALTER DATABASE [SalesDB] SET ONLINE;

Edit the service account only in SQL Server Configuration Manager.

Point the Database to the Right Files (Free)

Use this method when the files exist in a new folder and SQL Server still looks at the old path.

1. Get the logical file name with the second query from the earlier section.
2. Execute the commands below for every moved file.

ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = SalesDB_Data, FILENAME = N'E:\SQLData\SalesDB.mdf');
ALTER DATABASE [SalesDB] SET OFFLINE;

3. Move the physical file to the new folder in File Explorer.
4. Execute the SET ONLINE command from method one and query sys.master_files to confirm the new path.

Microsoft documents this sequence for a planned file move. Grant the service account rights on the new folder.

Rebuild a Missing Log File (Free, Breaks the Log Backup Chain)

Use this method when only the LDF file is gone. SQL Server rebuilds the log with a new 1 MB file in the default log location.

1. Stop the SQL Server service and duplicate the MDF and NDF files into a folder like D:\Rescue.
2. Start the service again.
3. Execute the command below to attach the copies under a new name.

CREATE DATABASE [SalesDB_Copy]
ON (FILENAME = N'D:\Rescue\SalesDB.mdf')
FOR ATTACH_REBUILD_LOG;

4. Take a full backup of the new database right away.

ATTACH_REBUILD_LOG works only for READ_WRITE databases that shut down cleanly. Microsoft states that the rebuild breaks the log backup chain.

Restore From a Backup (Free, Safest)

Microsoft says missing files need a restore from a valid backup. Restore the full backup first and apply the log backups in order.

RESTORE DATABASE [SalesDB]
FROM DISK = N'D:\SQLBackups\SalesDB_full.bak'
WITH REPLACE, NORECOVERY;

RESTORE LOG [SalesDB]
FROM DISK = N'D:\SQLBackups\SalesDB_log.trn'
WITH RECOVERY;

Restored data ends at the last log backup you apply. Open the BAK Restore Steps guide for the SSMS wizard.

How to Fix Recovery Pending State With EmailDoctor Software?

Try EmailDoctor SQL Database Recovery if the MDF file is damaged and no backup exists. After the scan you see rules, triggers, functions, stored procedures, views and tables in the preview.

Demo versions preview all recovered objects for free and cap exports at 25 table records with 5 other objects.

Windows 7, 8, 10 and 11 work with .NET Framework 4.5 or higher. Free disk space of 100 MB is required and 4 GB RAM is recommended.

CSV export saves table records only. Stored procedures and triggers need the SQL Server database or script route.

Open and Scan the Database File

1. Launch the recovery software after the installation.
2. Click Open and select the MDF file copy.
3. Choose Quick Scan and enable Auto detect to identify the SQL Server version. Select the version in the dropdown when detection fails.
4. Open the NDF Options tab to attach secondary files. Choose automatic detection or click Add Files or Add Folder to attach NDF files yourself.
5. When the scan report appears confirm the Quick Scan note.

Preview the Tables

6. Select a table in the left panel and compare its records in the preview with what you expect.

Export the Tables to CSV Files

7. Click Export and choose Export As CSV.
8. Tick the tables you need in the list.
9. Choose With Schema & Data to save the table structure and the records.
10. Click Save and browse to the folder for the CSV files.
11. Click OK in the Export Completed dialog and then Close.
12. Open the folder to check the exported CSV files.

    Select Advanced Scan if the Quick Scan preview shows fewer tables than the database should have. See the MDF File Repair Methods guide for the differences between both scan modes.

Which Method Fits Your Situation?

Match your OS error and file status to the method.

Which Snags Slow Down the Fix?

Check this list when a method fails.

• Same OS Error Returns. Check the permission of the service account again because SET ONLINE repeats the failed file open.
• Service Account Edited by Hand. Edit the account only in SQL Server Configuration Manager.
• File Still in Use. Confirm in a cluster that the previous node released the file handles.
• Attach Rejects the Files. Restore a backup instead because the files came from an unclean shutdown.
• Restore Refuses to Overwrite. Add the WITH REPLACE option to the RESTORE DATABASE command.
• State Returns After a Restart. Make the network or iSCSI storage available before SQL Server starts.

Frequently Asked Questions

Final Words

Recovery pending means SQL Server could not reach a database file or a resource at startup. Every OS error in the log names the file and the cause.

Fix the permission or the path first and bring the database online. Never delete the MDF and LDF files of a database that only lacks access.

EmailDoctor software scans damaged MDF files and exports the tables to CSV files.