Decommissioning a Database: A Safe Checklist Before the Drop

Decommissioning a database is a one-way door, so collect the evidence before you touch the drop. Check who uses it, who points at it, and whether your backup really restores. Then wait, offline, before you remove anything.

Why “nobody uses it” is not evidence

Every shop has that database. Nobody remembers the owner, the name looks old, and the disk is getting full. Someone says, “It has been quiet for months. Just drop it.”

Then quarter-end arrives. A finance report that runs four times a year fails, and your phone rings. The database was quiet because the work happens on a calendar, not every day.

So I treat the drop as the last step of a short checklist. To show the checklist, I need a database that has neighbors. The demo below builds one, with a job, a login and a second database that reads from it.

Build a database with neighbors

The first block creates the database, a small table and a few rows. The SELECT at the end touches the table on purpose, so SQL Server has some usage to report.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.Customers (CustomerId int PRIMARY KEY, CustomerName nvarchar(50) NOT NULL);
INSERT dbo.Customers (CustomerId, CustomerName)
VALUES (1, N'Ava Stone'), (2, N'Ben Okafor'), (3, N'Cara Lin');

SELECT COUNT(*) AS CustomerRows FROM dbo.Customers;

The next block adds the neighbors. A second database has a view over the first one. A login uses it as its default database. An Agent job has a step that runs inside it. The job and the login live at the server level, not inside the database. The demo creates both and removes them in the last block. If SQL Server Agent is stopped, you will see a harmless notice about it.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityReportsDemo;
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoNightlyCustomerLoad')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoNightlyCustomerLoad';
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'DemoLogin')
    DROP LOGIN DemoLogin;
GO
CREATE DATABASE SqlAuthorityReportsDemo;
GO
USE SqlAuthorityReportsDemo;
GO
CREATE VIEW dbo.CustomerList AS
SELECT CustomerId, CustomerName FROM SqlAuthorityDemo.dbo.Customers;
GO
EXEC msdb.dbo.sp_add_job @job_name = N'DemoNightlyCustomerLoad';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoNightlyCustomerLoad', @step_name = N'Load',
     @subsystem = N'TSQL', @database_name = N'SqlAuthorityDemo',
     @command = N'SELECT COUNT(*) FROM dbo.Customers;';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoNightlyCustomerLoad';
CREATE LOGIN DemoLogin WITH PASSWORD = N'Demo-Only-Pass#2026', CHECK_POLICY = OFF,
     DEFAULT_DATABASE = SqlAuthorityDemo;

Collect the evidence

Start with the clock. Usage numbers only mean something next to the time the server started, because they reset on a restart. Write both down.

USE SqlAuthorityDemo;
SELECT SYSDATETIME() AS CollectedAt,
       (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS ServerStartedAt;

SELECT OBJECT_NAME(object_id) AS ObjectName, index_id,
       last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
ORDER BY ObjectName, index_id;

The second result shows Customers with a last_user_scan and a last_user_update time, from my own SELECT and INSERT. On your server, empty columns mean “not since the restart.” They do not mean “never.” That difference is the quarter-end trap.

Now ask who still points at the database.

DECLARE @Database sysname = N'SqlAuthorityDemo';

SELECT j.name AS JobName, s.step_id, s.database_name
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE s.database_name = @Database OR CHARINDEX(@Database, s.command) > 0
ORDER BY j.name, s.step_id;

SELECT name AS LoginName, default_database_name
FROM sys.server_principals
WHERE default_database_name = @Database
ORDER BY name;

SELECT OBJECT_NAME(referencing_id, DB_ID(N'SqlAuthorityReportsDemo')) AS ReferencingView,
       referenced_database_name, referenced_entity_name
FROM SqlAuthorityReportsDemo.sys.sql_expression_dependencies
WHERE referenced_database_name = @Database
ORDER BY ReferencingView;

You get one job, one login and one view. Any row here is a conversation to have, not a green light. This list is also incomplete. It cannot see connection strings, synonyms, dynamic SQL or a spreadsheet that connects once a quarter. Ask the application owner, and ask about the calendar.

Prove you can come back

Take a final backup, then restore it under a different name. A backup you have never restored is a hope. The backup folder comes from a server property, and the restored copy’s files sit next to the originals, so no path is typed by hand.

USE master;
DECLARE @Folder nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultBackupPath'));
DECLARE @Backup nvarchar(400) =
    @Folder + CASE WHEN RIGHT(@Folder, 1) = N'\' THEN N'' ELSE N'\' END + N'SqlAuthorityDemo_final.bak';
DECLARE @Mdf nvarchar(260), @Ldf nvarchar(260), @Sql nvarchar(max);

SELECT @Mdf = REPLACE(physical_name, N'SqlAuthorityDemo', N'SqlAuthorityRestoreTest')
FROM sys.master_files WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND type = 0;
SELECT @Ldf = REPLACE(physical_name, N'SqlAuthorityDemo', N'SqlAuthorityRestoreTest')
FROM sys.master_files WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND type = 1;

SET @Sql = N'BACKUP DATABASE SqlAuthorityDemo TO DISK = N''' + @Backup + N''' WITH COPY_ONLY, CHECKSUM, INIT;';
EXEC (@Sql);

SET @Sql = N'RESTORE DATABASE SqlAuthorityRestoreTest FROM DISK = N''' + @Backup + N''' WITH CHECKSUM, REPLACE,
    MOVE N''SqlAuthorityDemo'' TO N''' + @Mdf + N''',
    MOVE N''SqlAuthorityDemo_log'' TO N''' + @Ldf + N''';';
EXEC (@Sql);

SELECT COUNT(*) AS RestoredRows FROM SqlAuthorityRestoreTest.dbo.Customers;

The restored copy has the same three rows. This demo writes one file, SqlAuthorityDemo_final.bak, into your default backup folder. Delete it when you finish. In real life, keep the backup, plus any certificate or key it needs, for as long as your policy says.

Take it offline and wait

Offline is the safe waiting room. Nothing can connect, and one command brings the database back.

USE master;
ALTER DATABASE SqlAuthorityDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;
SELECT name, state_desc FROM sys.databases WHERE name = N'SqlAuthorityDemo';

ALTER DATABASE SqlAuthorityDemo SET ONLINE;
SELECT name, state_desc FROM sys.databases WHERE name = N'SqlAuthorityDemo';

In real life, leave it offline for a full business cycle, a quarter or longer. If nobody calls, the owner signs off, and then you drop it. Here I only prove the way back works. Last, clean up everything the demo made, including the job and the login.

USE master;
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoNightlyCustomerLoad')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoNightlyCustomerLoad';
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'DemoLogin')
    DROP LOGIN DemoLogin;
DROP DATABASE IF EXISTS SqlAuthorityRestoreTest;
DROP DATABASE IF EXISTS SqlAuthorityReportsDemo;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Before the next drop, collect the evidence, test the restore and give it one full business cycle.

A quiet database is not an unused database, it is a candidate for review.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.