Restore Database

A SQL Server database remains in the RESTORING state after a successful restore operation if the WITH NORECOVERY option was used in the RESTORE command. This is an expected state, which keeps the database offline so that additional differential or transaction log backups can be applied. 

To make the database accessible and bring it online, you must explicitly finalize the restore process with the WITH RECOVERY option. 

Solution

Use the following T-SQL command to complete the recovery process and bring the database online:

sql

RESTORE DATABASE [Your_Database_Name] WITH RECOVERY;
GO

• [Your_Database_Name] should be replaced with the actual name of your database. 

Important Considerations

• Ensure all necessary logs are applied: Only run the WITH RECOVERY command after you are certain that all required transaction log backups have been applied. If you force recovery prematurely, you will not be able to apply further backups without restarting the entire restore sequence from the full backup, which may result in data loss or an inconsistent database state.
• Database Mirroring: In database mirroring configurations, the mirror database is intentionally kept in the RESTORING state until a failover occurs. The standard RESTORE DATABASE ... WITH RECOVERY command is not the correct solution in this specific scenario; instead, manage the mirroring session through failover or by removing the mirroring configuration if no longer needed.
• Other potential issues: If the command fails, check the SQL Server error logs for underlying issues, which could indicate corrupted backup files or other system problems. 

For more information, you can refer to the Microsoft Learn documentation on restoring a database or the MSSQLTips article on the restoring state. 

======================

1. Restore the database full backup .bak
2. Restore the transactions log backups .trn files – 24 backup files if the backup was done hourly.

After successful transaction logs restore, the db remains in Restoring.. state.

List all backups that were restored:

SELECT TOP (300) s.database_name, s.name, m.physical_device_name, s.backup_start_date, s.first_lsn, s.last_lsn
FROM msdb..backupset s
INNER JOIN msdb..backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = ‘EpicorERP’
ORDER BY s.backup_start_date DESC;

Warning: The Restore Options must be set to NORECOVERY.

If the option is RECOVERY, the potential issues are below:

TITLE: Microsoft SQL Server Management Studio
——————————

Restore of database ‘EpicorERP’ failed. However, the Tail-Log backup operation completed successfully.  (Microsoft.SqlServer.Management.RelationalEngineTasks)

——————————
ADDITIONAL INFORMATION:

Microsoft.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 65036000066397100001, which is too early to apply to the database. A more recent log backup that includes LSN 65038000017253300001 can be restored. (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.200.48053.0&LinkId=20476

---