Decommissioning a SQL Server database: a checklist for dependencies, a scream test, the final backup and a clean drop

#The goal

Someone has asked you to "get rid of the old database". Dropping it takes one second. The risk is that something still uses it: a monthly report, an Agent job on another server, a synonym in another database, or a login whose default database it is. This checklist gets you from request to a dropped database with a restorable backup and nothing broken.

Work through it in order. Record each answer in the change ticket.

#1. Confirm ownership and approval

• Named application owner has approved the decommission in writing.
• Data retention requirement is known (legal, finance, audit). This decides how long you keep the final backup.
• The database is on the right server. Check the instance and database name twice.

SELECT @@SERVERNAME AS server_name, name, database_id, create_date,
       state_desc, recovery_model_desc, compatibility_level
FROM sys.databases
WHERE name = N'LegacyCRM';

#2. Check whether anything still uses it

#Current connections

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE s.database_id = DB_ID(N'LegacyCRM');

#Reads and writes since the last restart

Index usage statistics reset when the instance restarts, so check the start time first.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

USE LegacyCRM;
SELECT OBJECT_NAME(ius.object_id) AS table_name,
       MAX(ius.last_user_seek)   AS last_seek,
       MAX(ius.last_user_scan)   AS last_scan,
       MAX(ius.last_user_lookup) AS last_lookup,
       MAX(ius.last_user_update) AS last_update
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.database_id = DB_ID()
GROUP BY ius.object_id
ORDER BY last_update DESC, last_scan DESC;

Tip

No activity since a restart two days ago proves nothing about a month-end job. If the instance restarted recently, run the connection query from step 2 on a schedule (for example an Agent job writing to a table) for a full business cycle before you continue.

#3. Find dependencies

• Agent jobs on this instance that run in or mention the database:

SELECT j.name AS job_name, j.enabled, s.step_id, s.step_name,
       s.subsystem, s.database_name
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE s.database_name = N'LegacyCRM'
   OR s.command LIKE N'%LegacyCRM%';

• Agent jobs and SSIS packages on other servers. Run the same query against every instance in your estate (a central management server or Invoke-DbaQuery from dbatools helps).
• Other databases referencing it through three-part names or synonyms. Run in each user database on the instance:

SELECT DB_NAME() AS in_database,
       OBJECT_NAME(d.referencing_id) AS referencing_object,
       d.referenced_database_name, d.referenced_entity_name
FROM sys.sql_expression_dependencies AS d
WHERE d.referenced_database_name = N'LegacyCRM';

SELECT DB_NAME() AS in_database, name, base_object_name
FROM sys.synonyms
WHERE base_object_name LIKE N'%LegacyCRM%';

• Logins that default to it. If you drop the database, those logins fail to connect when no database is specified in the connection string.

SELECT name, type_desc, default_database_name
FROM sys.server_principals
WHERE default_database_name = N'LegacyCRM';

• HA, replication and change tracking features:

SELECT d.name, d.is_published, d.is_subscribed, d.is_merge_published,
       d.is_cdc_enabled, d.replica_id, d.is_encrypted
FROM sys.databases AS d
WHERE d.name = N'LegacyCRM';

SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = N'LegacyCRM';

A non-NULL replica_id means the database is in an availability group. is_encrypted = 1 means TDE; see step 5.

• Outside SQL Server: connection strings in app config, SSRS/Power BI data sources, ETL tools, DNS aliases and firewall rules. Ask the owner, and search your config repositories for the database name.

#4. Run a scream test

Make the database unavailable for an agreed period (two to four weeks, or one full month-end) before dropping it. If something breaks, someone will tell you, and you can bring it back in seconds.

Record the file locations first; you need them later.

SELECT name, type_desc, physical_name, size * 8 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID(N'LegacyCRM');

Then take it offline:

ALTER DATABASE LegacyCRM SET OFFLINE WITH ROLLBACK IMMEDIATE;

WITH ROLLBACK IMMEDIATE disconnects current sessions and rolls back their open transactions. To undo the scream test:

ALTER DATABASE LegacyCRM SET ONLINE;

A database in an availability group can't be set offline. Either remove it from the AG first (step 6) or use restricted access instead:

ALTER DATABASE LegacyCRM SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

RESTRICTED_USER still lets sysadmin, dbcreator and db_owner members in, so it is a weaker test.

• Disable (don't delete) the Agent jobs found in step 3 at the same time, so they don't fail every night during the test.
• Announce the start and end dates of the scream test to the owner and service desk.

#5. Take and verify the final backup

The database must be online for a backup. Bring it back online for this step only, ideally in single-user mode so nothing reconnects:

ALTER DATABASE LegacyCRM SET ONLINE;
ALTER DATABASE LegacyCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BACKUP DATABASE LegacyCRM
TO DISK = N'\\backupshare\archive\LegacyCRM_FINAL_20261108.bak'
WITH COPY_ONLY, CHECKSUM, COMPRESSION, STATS = 10;

RESTORE VERIFYONLY
FROM DISK = N'\\backupshare\archive\LegacyCRM_FINAL_20261108.bak'
WITH CHECKSUM;

COPY_ONLY keeps the existing backup chain untouched, which matters if you need to roll back the decommission. COMPRESSION isn't available on every edition and version; drop it if the statement errors.

The same with dbatools:

Backup-DbaDatabase -SqlInstance sql01 -Database LegacyCRM `
  -Path \\backupshare\archive -CopyOnly -CompressBackup -Checksum -Verify

Caution

If the database uses TDE, the backup is useless without the certificate. Confirm the certificate and its private key are backed up and stored with the archive, and record the certificate thumbprint in the ticket.

SELECT DB_NAME(k.database_id) AS db, c.name AS cert_name, c.thumbprint, c.expiry_date
FROM sys.dm_database_encryption_keys AS k
JOIN master.sys.certificates AS c ON c.thumbprint = k.encryptor_thumbprint
WHERE k.database_id = DB_ID(N'LegacyCRM');

• Test-restore the backup to a non-production server under a different name. VERIFYONLY checks the file is readable; only a real restore proves you can get the data back.
• Copy the backup to storage with the agreed retention (and immutability, if your policy requires it). Record the location and the delete-after date.

#6. Drop the database

Warning

Dropping a database while it is offline removes it from SQL Server but leaves the .mdf, .ndf and .ldf files on disk. Bring it online first so DROP DATABASE deletes the files, or delete them manually afterwards using the paths you recorded in step 4.

If it is in an availability group, remove it from the AG on the primary first, then drop the leftover copy on each secondary:

-- On the primary
ALTER AVAILABILITY GROUP [AG1] REMOVE DATABASE [LegacyCRM];

-- On each secondary (the copy is left in RESTORING state)
DROP DATABASE LegacyCRM;

On a standalone instance:

ALTER DATABASE LegacyCRM SET ONLINE;
ALTER DATABASE LegacyCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE LegacyCRM;

Then confirm the files are gone using the paths from step 4.

#7. Clean up

• Backup and restore history in msdb for this database:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'LegacyCRM';

• Logins that only existed for this database. Check each one has no user in any other database before you drop it, and change the default database of any login found in step 3:

ALTER LOGIN [crm_app] WITH DEFAULT_DATABASE = [master];
-- after confirming it is not used elsewhere
DROP LOGIN [crm_app];

• Agent jobs disabled in step 4: delete them, or script them out to the ticket first.
• Maintenance jobs that list databases explicitly (backup, index and CHECKDB jobs). Remove the name so they don't fail.
• Log shipping, replication and CDC configuration, if any was found.
• Monitoring, CMDB and the database inventory: remove the database so you don't get false alerts or licence counts.
• Backup storage: old routine backups follow normal retention; only the final archive copy is kept longer.

#Quick reference

#Prevent it next time

• Record an owner and a purpose for every new database (an extended property or your CMDB) so the next decommission starts with an answer.
• Review databases with no activity quarterly using the usage query in step 2.
• Keep this checklist as the template for the change ticket so nobody skips the scream test or the TDE certificate.