How to decommission database or SQL Server instance

How to decommission a database

Before decommissioning a database, try to find out which application the database is related to. Either talk to the application owner or run Profiler for some time and check if anyone is using the database.

Here are some steps to take:

  1. Check if the database is in use.
  2. run Profiler, sp_who2, cached plans, index usage statistics etc to check if there are currently any connections to the database. (Not getting data from the above mentioned methods does not mean that the database is not used once a month or once a year even, so beware.)

  3. Check if the database is participating in a replication, mirroring or log shipping. If it does, then you will have to make a plan to dismantle the activities.
  4. Take a note of the properties of the database and document them
  5. – number of filegroups and number of files in each filegroup; document the locations of the filegroups

    – note down the sizes of each file

    – note if there are Full text catalogs and filestream involved and document the names and sizes

    – note the SQL Server instance version, edition and service pack level

  6. Check if there are any SQLAgent jobs related to the database and disable them; back up the jobs and save them as .sql scripts. Also, check if the database is included in any Mainetnance plan jobs (for example Database backup, index rebuild etc) and remove it from the Maintenance plan.
  7. Script all SQL Server logins related to users in the database; keep in mind that scripting the logins does NOT script the real password, but a password placeholder only.
  8. Backup security certificates etc.
  9. Set the datbase in a SINGLE_USER mode and take a full backup of it.
  10. Place the database backup together with the scripted agent jobs, the documentation and the logins in a folder and zip it (and encrypt it).
  11. Find a good secure location for the zipped folder and place it there. Take a note of where you have saved the zipped file. And then take a note of where you have written the note. :)
  12. Either detach the database or delete it. Keep in mind that detaching the database leaves the files on the disk system and deleting the database deletes the files.


How to decommission an instance

  1. Perform the steps in How to decommission a database for each database on the instance.
  2. Take notes about the instance settings and zip them together with the databases.
  3. – pretty much all settings from the instance properties window (right-click on the instance name in SSMS and click Properties)

    – note the current size of tempdb

    – script all sysadmins and securityadmins

  4. Script linked servers, credentials, endpoints, trigers, replications, database mail, SQL Server logs, SQLAgent logs, default trace files
  5. Check if there are other services running (SSIS, SSRS, SSAS) and backup all objects.
  6. After all user databases have been detached or deleted, take a note of the location of the system databases for the instance
  7. Go to Run –> services.msc and document the settings of the services related to the SQL Server instance and the account names which run the services
  8. Stop the SSIS, SSRS and SSAS
  9. Go to the folder containing the system databases and copy the files of msdb, master, model and mssqlsystemresource databases to the backup folder, together with all other information from previous points above.
  10. Stop the SQL Server service.


How to uninstall SQL Server instance

  1. Go to Control Panel –> Programs and features, find the ‘MS SQL Server’ program.
  2. Click ‘Uninstall’ and follow the wizard.
  3. Make sure to uninstall the right insance, if there are a few on the server.

Note: these routines are not exhaustive, since there might be slight differences depending on SQL Server version and Windows OS version. Also, depends if the environment is clustered.


1 comment to How to decommission database or SQL Server instance