SQL Server Database Decommissioning Check List

By: Svetlana Golovko   |   Updated: 2016-06-23   |   Comments (3)   |   Related: More > Database Administration

Problem

In one of our past tips we provided a new database questionnaire request form to help you gather information required for a new database creation.  But what are steps to decommission or migrate a database?

Solution

Different companies have various requirements and processes for database or database server decommissioning. In this tip we will provide steps that will help you decommission a database and make sure you have a good back-out plan (for example, in case there is a need to bring the database back online). We will also provide scripts that will help you identify dependencies in case there is a need to migrate the database instead of decommissioning.

You may already have a process documented, but this list will help you review most of the dependencies and make sure you are prepared for either a migration or decommissioning.

Preparation steps performed before decommissioning/migration

1. Get business owner, IT manager or other applicable approvals for the database decommissioning.
2. Have an agreement on warranty period (time frame when business may request to bring the database back online). During this period the database will be offline, logins disabled, any related jobs disabled, etc. The infrastructure will be in a "disabled" state, but nothing is deleted.
3. Have an agreement on final dates - how long the database will be offline, how long backups have to be retained, other archival/decommissioning requirements. Some financial database backups have to be retained up to 7 years due to government regulations.
4. For critical applications that will be decommissioned make sure the SQL Server software media is available in case auditors request a restore of the database. For example, you can't restore SQL Server 2000 database directly to SQL Server 2014. You have to be sure you can restore the database to the SQL Server 2008 first, change the database compatibility mode and then backup it and restore it to the SQL Server 2014.
5. If you are migrating the database make sure that the new SQL Server version is supported by a vendor. You may proceed with an unsupported migration (where the database will be running under compatibility mode) if you have technical approvals for this scenario or if your practice allows you to do this.
6. If you are migrating from SQL Server Enterprise Edition, for example, to the Standard Edition make sure features are compatible:
   

USE [_Demo_Decomm_1]
GO
-- Enterprise Edition features used
select * from sys.dm_db_persisted_sku_features

7. Make sure all dependencies are taken into consideration (Reporting Services, Linked Servers, Replication, SSIS packages, etc.). Involve all stakeholders to identify potential dependencies. See some of the scripts used for dependency checks below.
8. Make sure that all licenses are up to date (correspond to the new version if this is migration, freed up if this is decommissioning). Communicate and plan licensing changes with vendor and internal resources responsible for the licensing (including application, SQL Server and other dependent software, if applicable).
9. Have a back-out plan for the migration that has all of the steps to bring the database/application back online.
10. Create relevant Change Management documents if you have a formal Change Management process.

Phase 1 - Initial Decommissioning/Migration Tasks

1. Disable all jobs related to the database after the final backup (do not delete jobs until Phase 2 of the decommissioning is complete):
   

USE [_Demo_Decomm_1]
GO
-- disable related jobs (jobs that have T-SQL step with a database name selected for the step)
SELECT j.* FROM msdb.dbo.sysjobsteps js
 JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE j.[enabled] = 1 AND js.database_name = DB_NAME()

-- disable every job found with the query above
EXEC msdb.dbo.sp_update_job
    @job_name = N'Export Some Data',
    @enabled = 1 ;
    
-- there could be other jobs that are related to the database. 
-- For example, scheduled SSIS packages.
   

2. Disable logins on the old SQL Server if users do not have access to other databases and if they are not members of any server role:
   

USE [_Demo_Decomm_1]
GO
CREATE TABLE dbo.##temp_logins (sid VARBINARY(200), db NVARCHAR(128));

EXEC sys.sp_MSforeachdb 
 'INSERT INTO dbo.##temp_logins 
  SELECT sid, ''?''  FROM [?].sys.database_principals 
   WHERE type IN (''S'', ''U'', ''G'') -- SQL Logins, Windows Logins and Windows Groups
   AND authentication_type > 0 --excludes schemas and users without logins in contained databases
   AND principal_id > 1 -- dbo';

SELECT sp.name as Login_Name, 
 CASE WHEN all_dbs.db IS NOT NULL THEN '-- Exists in ' + all_dbs.db +  ' database'   
  WHEN sr.member_principal_id IS NOT NULL THEN '-- Is member of the ' + r.name + ' server role ' 
  ELSE 'ALTER LOGIN [' + sp.name + '] DISABLE ; ' END as "Keep/Disable Login"
FROM tempdb.dbo.##temp_logins c_db -- current database (to decommission)
 JOIN sys.server_principals sp 
  ON c_db.sid = sp.sid
 LEFT JOIN  sys.server_role_members sr
  ON sp.principal_id = sr.member_principal_id
 LEFT JOIN sys.server_principals AS r
    ON sr.role_principal_id = r.principal_id
 LEFT JOIN tempdb.dbo.##temp_logins all_dbs
  ON c_db.sid = all_dbs.sid and all_dbs.db != DB_NAME()

WHERE c_db.db = DB_NAME() ;
 
DROP TABLE dbo.##temp_logins;

Here is an example of the query output:



Make sure that all disabled logins from step 2 have been migrated to the new server if this is a database migration. Here is a script from Microsoft that you can use to copy logins including SQL Server login passwords.

3. Update statistics, rebuild indexes and check database before the migration. Check the database after migration.
4. Make sure that all application services have been disabled and there are no connections to the database:
   

USE [_Demo_Decomm_1]
GO
-- check DB connections
SELECT login_time, [host_name], [program_name] 
FROM sys.dm_exec_sessions WHERE database_id = DB_ID();

5. Backup the database and use the latest backup for the migration/decommissioning. Validate the backup. Make sure that the final database backup is available for the warranty or compliance period. Check with your Backups Administrator for retention policies and make sure the backup will be preserved as long as required.
6. Review and schedule disabling of Database Snapshots, Replication, Log Shipping, Availability Groups, Mirroring, etc. if applicable. To check if the database is a publisher or a distributor for a replication topology run the following script:
   

SELECT name, is_published, is_merge_published, is_distributor FROM master.sys.databases

7. Check if the FILESTREAM is enabled on the database:
   

USE [_Demo_Decomm_1]
GO
-- FILESTREAM
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
    FROM sys.database_filestream_options;

If you migrate the database you will need to enable FILESTREAM on the new server.

8. Find if there are FileTables in the database (FILESTREAM dependency):
   

USE [_Demo_Decomm_1]
GO
-- FileTables
SELECT * FROM sys.filetables;
GO
SELECT * FROM sys.tables WHERE is_filetable = 1;
GO

9. Check if the database has Full Text catalogs:
   

USE [_Demo_Decomm_1]
GO
-- full-text catalogs
SELECT name FROM sys.fulltext_catalogs

If you migrate the database you will need to make sure that the Full Text Service is running on the new server.

10. Check if the database is referenced by T-SQL code (procedures, views, etc.) in other databases:
    

-- cross-db dependencies
CREATE   TABLE #tmp_dependencies (
   referencing_db_name NVARCHAR(128),
   referencing_object NVARCHAR(128),
   referenced_database_name NVARCHAR(128),
   referenced_schema_name NVARCHAR(128),
   referenced_entity_name NVARCHAR(128))
GO

EXEC sys.sp_MSforeachdb 'INSERT INTO #tmp_dependencies
SELECT  ''?'' AS referencing_db_name, 
  OBJECT_NAME (referencing_id) AS referencing_object, 
  referenced_database_name, 
      referenced_schema_name, 
      referenced_entity_name
FROM [?].sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
      AND is_ambiguous = 0
   AND referenced_database_name = ''_Demo_Decomm_1'';'
 
SELECT * FROM #tmp_dependencies;

DROP TABLE #tmp_dependencies;
GO

If there are dependencies make sure that appropriate code changes/decommissioning is performed to avoid broken dependent applications.

11. Check if there are synonyms in other databases that reference the database:
    

-- synonyms
CREATE   TABLE #tmp_synonyms (
   referencing_db_name NVARCHAR(128),
   synonym_name NVARCHAR(128),
   base_object_name NVARCHAR(1035))
GO

EXEC sys.sp_MSforeachdb 'INSERT INTO #tmp_synonyms
SELECT  ''?'' AS referencing_db_name,  name AS synonym_name, base_object_name 
FROM [?].sys.synonyms WHERE base_object_name LIKE ''[[]_Demo_Decomm_1].%'';'
 
SELECT * FROM #tmp_synonyms;

DROP TABLE #tmp_synonyms;
GO

Investigate synonym usage to avoid broken dependent applications.

12. Remove (if exists) linked servers that might be used to connect to the old database (you will need to run this on different SQL Servers):
    

USE [master]
GO
SELECT name, product, provider, data_source, provider_string, [catalog]
 FROM sys.servers WHERE is_linked = 1

13. Check the database's files locations:
    

USE [_Demo_Decomm_1]
GO
SELECT file_id, type_desc, data_space_id, name, physical_name FROM sys.database_files

If the database had a dedicated LUN that is not shared with other databases work with Sysadmins to decommission/repurpose the LUN as well.

14. Check if the database has CLR dependency (has assemblies):
    

USE [_Demo_Decomm_1]
GO
-- CLR
SELECT name, clr_name, is_user_defined FROM sys.assemblies 

If this was the last database on the server that was using CLR you may consider disabling this feature on SQL Server (to reduce the attack surface by disabling unused features). If this is a database migration then make sure that the new SQL Server has CLR enabled.

15. Check Service Broker dependencies:
    

USE [_Demo_Decomm_1]
GO
-- Service Broker dependencies
SELECT * FROM  sys.service_queues WHERE is_ms_shipped = 0

16. Check the database Audit specifications:
    

USE [_Demo_Decomm_1]
GO
-- DB audit
SELECT name, is_state_enabled FROM sys.database_audit_specifications

Read this tip if you need to move the database that has audit enabled to a different SQL Server.

17. Check if you need to disable any server level DDL triggers that might be related to the database:
    

USE master
GO
-- Server Level DDL triggers
SELECT t.name, m.[definition] 
 FROM sys.server_sql_modules m
  JOIN sys.server_triggers t ON m.object_id = t.object_id 
 WHERE [definition] LIKE '%Demo_Decomm_1%'

18. Make sure that database monitoring has been disabled if monitoring is performed by a third party company. Disable the monitoring in other tools, if applicable.
19. Understand how credentials and proxy accounts work and make sure that there are no dependencies (for example SSIS proxy account that has access to the database as well). Cross check with Step #1 (SQL Server Agent Jobs that might be related to the database).
    

USE [master]
GO
SELECT  j.name AS job_name, js.step_name, p.proxy_id, 
  p.name AS proxy_name, p.credential_id, l.name AS login_name,
  c.name AS credential_name 
 FROM msdb.dbo.sysproxies p 
  JOIN sys.syslogins l ON p.user_sid = l.sid 
  JOIN sys.credentials c ON p.credential_id = c.credential_id
  JOIN msdb.dbo.sysjobsteps js ON p.proxy_id = js.proxy_id
  JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
ORDER BY  j.name

20. Verify if the database is encrypted or has a certificate or has symmetric/asymmetric keys:
    

USE [_Demo_Decomm_1]
GO
SELECT * FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID();

SELECT name, key_length, algorithm_desc, create_date, modify_date
FROM sys.symmetric_keys;

SELECT name, algorithm_desc 
FROM sys.asymmetric_keys;

SELECT name, subject, start_date, expiry_date 
FROM sys.certificates;

Create a backup of the Database Master Key if required as described here.

Backup the certificate if required using an example provided here.

Read more tips about encryption here.

21. Check if there are Resource Governor dependencies:
    

--- Get the classifier function Id and state (enabled).
SELECT * FROM sys.resource_governor_configuration
GO
--- Get the classifier function name and the name of the schema
--- that it is bound to.
SELECT * ,
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

22. Review Extended Events that are filtered by database name or by the database ID:
    

USE [master]
GO
SELECT e.event_session_id, s.name, e.predicate 
 FROM sys.server_event_session_events e
  JOIN   sys.server_event_sessions s ON e.event_session_id = s.event_session_id
WHERE predicate LIKE '%source_database_id%' OR  predicate LIKE '%database_name%';

Disable the sessions, script them (if required for the database migration).

23. Review databases maintenance plans/scripts. Make sure that there is no database specific tasks or hard coded database names.
24. Review and disable database specific alerts:
    

USE [_Demo_Decomm_1]
GO
-- alerts
SELECT * FROM msdb.dbo.sysalerts 
WHERE database_name = DB_NAME() -- SQL Server event alerts for DB
 OR performance_condition LIKE '%' + DB_NAME() + '%' -- performance condition alerts

25. Run the sp_helpdb procedure to record the current database owner:
    

USE [_Demo_Decomm_1]
GO
 -- db_owner 
EXEC sys.sp_helpdb

Some applications require the application account be mapped to the dbo built-in user.

26. Note the SQL Server Service Pack or Cumulative Update level:
    

SELECT @@VERSION;

If you migrate the database to a SQL Server that has higher version you may need to perform additional application testing before you migrate the database.

27. On the Central Management Server run the following script to find policies or conditions that might be related to the database:
    

SELECT name, facet, obj_name, expression  FROM msdb.dbo.syspolicy_conditions 
WHERE obj_name = DB_NAME()
 OR expression LIKE '%' + DB_NAME() + '%'

Read more tips about Policy Based Management here.

28. Set the database offline, keep for the warranty period or standard decommissioning period. Afterwards drop the database or just keep offline:
    

ALTER DATABASE [_Demo_Decomm_1] SET OFFLINE;

Phase 2 - Final Steps performed after decommissioning/migration

1. Backup database on the new server after migration (before users start using it).
2. Review the logs.
3. Setup the database audits/monitoring (if database has been migrated and if a special database audit/monitoring is required). Start monitoring only after all issues have been addressed.
4. If this was a database migration make sure application configuration/SQL Server alias/DNS records have been updated.
5. Delete on the old SQL Server all disabled jobs identified in Step 1 of Phase 1 that are related to the database.
6. Delete old disabled logins on SQL Server related to the database. See the Step 2 of Phase 1 to get the list of logins.
7. Delete other components identified in Phase 1 above (for example, extended events sessions, audits, etc.).
8. Delete database after warranty period. Bring it online and drop the database (make sure files on disks are deleted as well):
   

ALTER DATABASE [_Demo_Decomm_1] SET ONLINE;
GO
DROP DATABASE [_Demo_Decomm_1];

9. Update your Database Inventory.
10. Update other database related documentation (SQL Server Agent Jobs, backups, maintenance. etc.).
11. Delete old backup files (after monthly backup to the tape completion), remove database files (if you did not bring the database online before dropping the database).

If this is the last database on the server

1. Remove SQL Server from Utility Control Point.
2. Remove SQL Server from the Central Management Server and from other monitoring scripts if this is the last database on the server.
3. Remove the server from other monitoring tools (for example third party tools, SCOM, etc.).
4. Remove the server from automated patching software.
5. Remove DNS aliases id required.
6. Release IP address (or addresses).
7. Remove firewall rules.
8. Disable and delete service accounts (make sure they are not shared with other SQL Servers installations).
9. Decommission/shred disks.
10. Decommission/repurpose LUNs if SAN storage was used.
11. Disable tape backups.
12. Remove the server from Antivirus server (if applicable).
13. Perform other cleanup tasks that are applicable to your environment.
14. Disable and delete computer account from Active Directory.

Some steps from this tip also could be used in the following scenarios:

• Replacing an old version with a newer version of application (assuming this is not in-place upgrade or the new database will be created on the different SQL Server).
• Removing an application which is obsolete and is not used any more.
• Removing an application because it is replaced with another application.
• Migration of databases to new hardware.

Next Steps

• Use this tip when you decommission or migrate your databases.
• Create formal decommissioning documentation that will be applicable to your environment.
• Read this tip that has a database migration checklist including useful scripts.
• Here is a tip about migrating a SQL Server instance.
• Post comments to this tip if you perform other steps that are not listed in the tip.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips