A Source Database in Restoring State can follow a tail-log backup WITH NORECOVERY. A customer encountered this while restoring another copy.
Review generated commands and the wizard tail-log options. NORECOVERY deliberately protects certain recovery workflows by leaving the source unavailable. Selecting a source alone does not cause that behavior. I previously reversed the checkbox advice.
SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name=N'YourSourceDatabase';
-- Only after confirming no further backup restores are intended:
-- RESTORE DATABASE [YourSourceDatabase] WITH RECOVERY;WITH RECOVERY completes the current sequence when no more restores are required and that outcome is intended. Confirm the objective first. It is not a harmless universal fix for RESTORING.
Check mirroring, log shipping, availability groups and ongoing restore chains. The state can be expected. Keep source, destination and file paths explicit to avoid changing the wrong database.
Reference: SSMS tail-log and source-state options.
Related reading
- Comprehensive Database Performance Health Check
- SQL in Sixty Seconds series
- Generate Script of SQL Server Objects – SQL in Sixty Seconds #184
- Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
RESTORING is not always an error state, it is a state to interpret within the intended recovery or replication workflow.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
