Reference

Database states & recovery

What each database state means, what caused it, what to check, and how to get back to ONLINE without losing data. Plus safe file migration in an availability group, running batches across servers, and the dbatools one-liners worth memorizing.

Database states Moving files in an AG :CONNECT across servers dbatools kit

Database states

Check state and the reason first, every time. One query tells you where you stand:

SELECT name, state_desc, is_in_standby,
       -- why the log can't clear, if relevant
       log_reuse_wait_desc
FROM sys.databases WHERE name = 'YourDB';

ONLINEHEALTHY

Means: normal. The database is available.

Watch: ONLINE does not mean healthy underneath. An AG secondary can be ONLINE while its redo queue drowns. Check queues and log_reuse_wait_desc separately.

RECOVERY_PENDINGCRITICAL

Means: SQL Server knows the database exists but could not start recovery on it. Almost always a resource problem, not corruption. A data or log file is missing, offline, or unreachable, or the service account lost permission to the path.

Check: the SQL error log for the file it could not open, and that every file in sys.master_files actually exists and is reachable.

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

Fix: restore access to the missing file or path (drive came back, permission fixed), then bring it back with ALTER DATABASE YourDB SET ONLINE; No data loss, because nothing was corrupted. The engine just could not reach the files.

SUSPECTCRITICAL

Means: recovery started and failed. This is the scary one. Usually a corrupt or missing log, or a torn write, so the engine could not roll forward or roll back cleanly.

Check: the error log for the exact failure and msdb.dbo.suspect_pages. Do not panic-detach, that can make it worse.

First move: if this database is in an availability group, do not repair it. Fail over or reseed from a healthy replica, which has a clean copy. Repair is for when you have no clean copy anywhere.

Recovery path, in order of least data loss:

Restore from backup to the point of failure. This is the correct answer whenever you have a good backup chain, and it loses nothing if your log backups are intact.
Automatic page repair pulls a clean copy of a bad page from an AG replica, if only specific pages are damaged.
EMERGENCY + CHECKDB repair only as a last resort, when there is no clean backup and no healthy replica. See EMERGENCY below.

EMERGENCYMANUAL

Means: a state you set on purpose, not one that happens to you. It makes a SUSPECT database read-only and single-user so you can inspect it or attempt repair.

ALTER DATABASE YourDB SET EMERGENCY;
ALTER DATABASE YourDB SET SINGLE_USER;
-- inspect first; this is your chance to pull data out before any repair
DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

REPAIR_ALLOW_DATA_LOSS deletes what it can't fix. It is the true last resort, run only after you have confirmed there is no good backup and no healthy replica, and ideally after copying out what data you can while in EMERGENCY read-only. Its name is a warning, not a formality.

DBCC CHECKDB ('YourDB', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
ALTER DATABASE YourDB SET MULTI_USER;

RESTORINGEXPECTED

Means: a restore is in progress, or a restore was left WITH NORECOVERY and is waiting for more log to be applied. On an AG secondary this is normal.

Fix: if it is a stuck restore you meant to finish, RESTORE DATABASE YourDB WITH RECOVERY; brings it online. If it is an AG secondary, leave it, that is how it should look.

OFFLINEINTENTIONAL

Means: someone took it offline on purpose. It does not come back by itself.

Fix: ALTER DATABASE YourDB SET ONLINE; Confirm you know why it was taken offline before you bring it back.

Moving database files in an AG without losing data

Moving data or log files to a new drive is routine on a standalone instance. Inside an availability group it needs a specific order, because the paths have to match across replicas and you cannot just detach and reattach an AG database. Here is the safe sequence.

The standalone move (the building block)

The move itself is two steps: tell SQL the new path, then physically move the file while the database is offline for that file.

-- 1. register the new location (does not move anything yet)
ALTER DATABASE YourDB
  MODIFY FILE (name = YourDB_Data, filename = 'E:\Data\YourDB.mdf');
-- 2. take it offline, move the file at the OS level, bring it back
ALTER DATABASE YourDB SET OFFLINE;
-- move YourDB.mdf from old path to E:\Data\ in Windows
ALTER DATABASE YourDB SET ONLINE;

In an AG, do the secondaries first. The paths and the moves have to line up on every replica. Work one replica at a time so you always have a healthy copy serving.

The AG-safe order

On each SECONDARY, one at a time: remove the database from the AG on that secondary (this does not touch the primary), do the standalone move above on the secondary, then rejoin the database to the AG. The secondary reseeds or resyncs. The primary is untouched and still serving the whole time.
Once every secondary is moved and synchronized: fail the AG over to a moved secondary, so a replica with the new paths becomes primary.
On the old primary (now a secondary): do the same move, then rejoin.

Done this way you never take the availability group down and you never risk the only good copy. There is always a synchronized replica serving while another is being moved. The data loss risk is zero because the AG keeps a live, redundant copy at every step.

Do not just SET OFFLINE on an AG primary and move its files. An AG database cannot be moved with a naive offline-and-move on the primary the way a standalone can. Move it out of the AG on a replica, or move secondaries and fail over. Respect the order and there is no data loss; skip it and you can break the AG.

Running a batch across many servers with :CONNECT

When you need to run the same read-only check against a list of instances, SQLCMD mode in SSMS lets one script hop between servers with the :CONNECT command. Turn it on under Query, SQLCMD Mode, then:

-- runs the same check on each instance, labeling the source
:CONNECT DRSQL01
SELECT @@SERVERNAME AS server, name, state_desc FROM sys.databases;
GO
:CONNECT DRSQL02\INST1
SELECT @@SERVERNAME AS server, name, state_desc FROM sys.databases;
GO

Each :CONNECT opens a fresh connection to that instance for the batch that follows, using your Windows credentials by default. Label every result with @@SERVERNAME so you can tell the outputs apart, and keep the statements read-only when you are sweeping a fleet during an incident. For dozens of servers, the PowerShell audit (Invoke-HadrAudit.ps1) scales better than pasting :CONNECT blocks by hand.

The dbatools kit

When tooling is allowed on the box, dbatools (the open-source PowerShell module) turns the fiddly cross-server jobs into one-liners. The ones worth memorizing, all safe to run and read-only unless noted:

# copy logins WITH their SIDs, passwords, and roles (fixes the orphaned-login-after-failover problem at the root)
Copy-DbaLogin -Source PROD -Destination DR

# copy agent jobs so DR isn't running naked after failover
Copy-DbaAgentJob -Source PROD -Destination DR

# find orphaned users across a database
Get-DbaDbOrphanUser -SqlInstance DR-SQL02 -Database OrdersDB

# check AG health across the fleet
Get-DbaAgReplica -SqlInstance PROD-AG-LISTENER

# last known good backups (spot the databases with no recent log backup)
Get-DbaLastBackup -SqlInstance PROD-SQL01

The first two are the durable fix for the classic "it failed over but the app can't log in and the jobs stopped" incident, because logins and jobs live outside the availability group and do not travel with it. Script them from prod to every replica and put the drift check in your regular audit so it never surprises you at 2am.

Reminder: everything on this page is reference. Run the commands yourself, against your own version and your site's runbook, read-only unless you have confirmed the fix and the approval. This page never connects to anything.