DBA Scripts: Get Database Inventory

Peter Whyte

–

Part of the DBA-Tools Project.

One Query, Every Database’s Migration Readiness

Before you migrate, upgrade, or consolidate anything, you need one honest answer per database: is it actually online, what recovery model is it running, and is its compatibility level going to surprise you on the target server. Digging that out of SSMS one database properties dialog at a time doesn’t scale past a handful of databases, and it’s easy to miss the one database somebody set to read-only eighteen months ago and forgot about.

Get-DatabaseInventory pulls every user database on the instance into a single result set: state, recovery model, log reuse wait, compatibility level, read-only/auto-close flags, creation date, and collation. It’s the first thing to run before you plan a migration, not partway through one.

Why Database Inventory Matters

A migration or upgrade plan built on assumptions instead of a real inventory tends to fail on the details, not the big picture:

  • Compatibility level doesn’t move itself on restore or attach. A database sitting three versions behind the instance’s default will keep running old cardinality estimation behaviour on new hardware, silently, until someone notices a regression.
  • Recovery model drives your backup strategy. A database quietly running SIMPLE when everyone assumed FULL means point-in-time recovery isn’t actually available for it.
  • log_reuse_wait_desc stuck on anything other than NOTHING or LOG_BACKUP (replication, active transaction, backup) is worth knowing about before a migration, not discovering mid-cutover.
  • Read-only and auto-close flags change what a migration script can and can’t do to a database, and both are easy to set once and forget.
  • Collation mismatches between source and target are one of the most common “why did this fail on restore/attach” causes, and catching it here is cheaper than catching it during the cutover window.

When to Run This Script

  • Before scoping any migration, consolidation, or version upgrade, as the first script to run, not a mid-project check
  • As a standing pre-migration checklist item alongside Get Migration Risk Assessment and Get Version Upgrade Readiness
  • When you inherit a server and need a fast, honest baseline of what’s actually running on it before you touch anything
  • Periodically on servers slated for future migration, so drift (a database quietly flipped to read-only, a compat level someone forgot to bump) gets caught early

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-DatabaseInventory
Category    : migration
Purpose     : Inventory user databases for migration readiness — compatibility level, recovery model, state.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : VIEW ANY DATABASE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
    d.name AS database_name,
    d.database_id,
    d.state_desc,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    d.compatibility_level,
    d.is_read_only,
    d.is_auto_close_on,
    d.create_date,
    d.user_access_desc,
    d.collation_name
FROM sys.databases AS d
WHERE d.database_id > 4
ORDER BY d.name;

Filters to user databases (database_id > 4, so master/tempdb/model/msdb are excluded) and returns one row per database, ordered by name.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Inventory every user database's migration-relevant state:
.\run.ps1 Get-DatabaseInventory

# To run against a remote sql server:
.\run.ps1 Get-DatabaseInventory -ServerInstance SQLSERVER01

This script lives in the repo at:

Example Output

Get-DatabaseInventory output showing key columns, SQL Server output

Real output captured 2026-07-31 07:12 against .. Reformat/trim as needed; screenshot preferred for the live post.

Understanding the Results

The lab instance behind this post came back clean: all 7 user databases ONLINE, all on FULL recovery, all at compatibility level 170 (matching the SQL Server 2025 instance default), no read-only or auto-close flags set, and every log_reuse_wait_desc sitting at NOTHING. The migdb_*-prefixed databases are lab fixtures created for testing the migration DDL generators elsewhere in this project. A real server won’t have those, but the columns read the same way regardless of what’s actually on the box.

What to check per column:

  • state_desc — anything other than ONLINE (RESTORING, RECOVERY_PENDING, SUSPECT) needs attention before it needs a migration plan.
  • recovery_model_desc — confirm it matches what your backup strategy assumes. SIMPLE where you expected FULL means no log backups are running and no point-in-time recovery exists for that database.
  • compatibility_level — compare against the instance’s own version. A database sitting well below the instance default carries forward old query-optimizer behaviour; that’s sometimes deliberate (a known regression workaround) but should never be silent.
  • log_reuse_wait_desc — NOTHING and LOG_BACKUP are the two expected steady states. Anything else (ACTIVE_TRANSACTION, REPLICATION, BACKUP) means something is actively holding the log open right now.
  • is_read_only — a database flipped to read-only will fail any migration step that tries to write to it (schema changes, data fixes). Confirm it’s intentional before you script around it.
  • is_auto_close_on — costly on any database with regular traffic (the file closes and reopens on every connection gap); worth flagging even outside a migration context.
  • collation_name — compare source and target before a cross-server restore or attach. A mismatch here is one of the most common causes of a failed post-migration cutover.

Best Practices

  • Run this before scoping a migration, not after the plan is already written. The compatibility level and collation columns can change the shape of the plan itself.
  • Keep a dated copy of the output alongside your migration runbook so “what did the source actually look like” isn’t a question you have to reconstruct later.
  • Re-run it immediately before cutover, not just during planning. Recovery model and read-only flags can drift in the weeks between the two.

Related Scripts

You may also find these scripts useful:

Summary

Get-DatabaseInventory is deliberately narrow: one row per database, the handful of columns that actually change a migration plan, nothing else. It’s not a replacement for Get Database Summary‘s ongoing operational view or Get Database Sizes and Free Space‘s capacity detail. It’s the fast, honest baseline you pull before you commit to a migration plan, so the plan is built on what’s actually on the server rather than what the documentation says should be there.

Run it early, keep the output, and re-run it right before cutover. The columns that matter most here (recovery model, compatibility level, collation) are exactly the ones that fail quietly if nobody checks them until the migration is already underway.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *