DBA Scripts: Get Database Inventory

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/dba-scripts-get-database-inventory/)
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:

• sql/inventory/Get-DatabaseInventory.sql
• powershell/wrappers/inventory/Get-DatabaseInventory.ps1

Example Output

Run against a lab instance with seven user databases. Every column the script returns is in the grid, so it is worth scrolling right rather than stopping at the recovery model.

Understanding the Results

Two things in the capture above are worth pointing at before the columns themselves. One database is on compatibility level 130 while every other is on 170, and most rows show LOG_BACKUP as their log reuse wait rather than NOTHING. Neither is a fault. Both are the kind of detail this script exists to put in front of you before a migration, rather than after one.

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.

Microsoft’s reference covers sys.databases in full.

Frequently Asked Questions

Related Scripts

You may also find these scripts useful:

• Database Sizes and Free Space
• Database Summary
• Database Snapshot Inventory
• Login and Job Inventory
• Linked Server Inventory
• OS and Hardware Info
• Patch Level
• Services Information
• Migration Risk Assessment
• Server Inventory (pillar overview)
• DBA Scripts: Get Database Health, state, recovery model and log reuse for every database in one pass
• DBA Scripts: The Complete Guide

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.