Dropping (deleting) a production database…

Deleting a production database is not an easy task to accomplish. Well technically it may be easy but as a guardian of database, you need to make sure that you do not any one adversely by deleting a production database abruptly.

Hence I prefer to use below steps as a standard guideline to delete a production database:

1. After you get approval of database’s deletion from database owner, announce to the users of the server that you intent to take the database offline (and eventually delete). Inform a date and time (I prefer to give them at least 5 working day’s notice or follow the company’s police)
2. Make the database read-only on the day of taking it offline and then take a full backup. Make sure this backup is moved to tape and archived for maximum duration to ensure that the data will not be lost for ever after you drop it from server. (Some financial database needs to be archived as per Government mandate which may be much larger duration than standard company retention policy. So always make sure that you got a written confirmation from database owner agreeing with the retention policy of the database backup after it is offline/deleted)
3. Take the database offline. At this stage, advice users when the database will be permanently deleted from the server. (I prefer to wait for at least 5 working days unless company has a different policy). Also make sure to disable all jobs using this database, edit maintenances and monitoring plans as needed to ensure that no one gets pages at night for unnecessary alerts from this “offline” database.
4. On the day of final deletion, bring the database online and delete it. Also I prefer to delete all the backup/restore history information of the database while I delete the database.

Example code I use to delete the database:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'[Database Name]'

GO

USE [master]

GO

DROP DATABASE [Database Name]

GO

Note:

a.      Although it is not expected, you may need to kill all the connections to this database while deleting it. However in this case advice the SPID owner about the database’s deletion after you killed his/her connection. (He/she may missed the information about the database’s deletion and started a application/query pointing to the database coincidentally when you bough it online for deletion)

b.      Deleting backup/restore history may take a while depending on the volume of data present at msdb about this database. So delete may take longer time in this case. (This situation is often common for a database which was in production for a long time which has long backup/restore history)

5. After database is deleted, inform every one about the deletion.

Following this step will ensure that this deletion is well coordinated and did not created any outage on any application. Also having a defined retention period of last backup of the deleted database will guarantee that you can recover the database or data in case of some unforeseen issue later.