Friday, September 4, 2015

Dropping a SQL Server Database

Hi Guys,

Last post I have shared an Error that I was unable to start my SQL Server Database Engine. Today here I will share a small finding which I thought of worth sharing it.

In one of my migration project what I observed was "Even after dropping a Database the Data files and Log files were still exist". Ideally this should not happen because if we drop a Database the data file and log file associated with it should be dropped as well. But lets see what happened exactly that the database files are not dropped.

So what are the reasons for this?

Following are various ways to remove or Offline the Database from the SQL Server Database Engine:

a. Drop a Database
b. Detach \ Attach
c. Offline \ Online

General syntax for dropping a Database is: Drop Database Test

Let's see the scenario what exactly happened:

For the production migration what we were suppose to do was, remove then Test migrated Database and the replace it with the new Production migration Database. Following is the sequence which I followed:

Current Production Server : SQLServer-Current
New Production Server : SQLServer-New
Database Name : Test
  1. The test migration for the Database "Test" was done on "SQLServer-New" from "SQLServer-Current" Server.
  2. After proper testing on "SQLServer-New" the Databases was put Offline.
  3. On the day of actual Production Migration, we dropped the database so that we can create a fresh copy of the Database.
  4. Once it was dropped we started restoring the Database on the location were we have kept the data and log file.
  5. It popped out an error message that the mdf and ldf files are exist. Either I should rename by new file name or I need to change  the path.
  6. Since I cannot change the Database path, I was forced to change the file names.
  7. After that I was able to restore the Database.
  8. But I was wondering how can the files are still exist if I have dropped the Database. So once the migration activity was completed successfully I started troubleshooting it and here I found something new to learn.
"If you put the Database in Offline state and then drop a Database; you will always find the file associated with the Database present in the defined location"

Share you comments or any queries below to discuss more.