G’day,

I think it’s a safe bet that everybody here has issued a DROP DATABASE statement and knows what to expect.

Well, today I was experimenting with a development database. The script I was using employed simply reusable code to drop and re-create the database – or so I though.

The code I was using was a follows

USE tempdb;
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = 'TestDB')
BEGIN
PRINT 'Database does not exist at present';
END
ELSE
BEGIN
PRINT 'Database exists and will be dropped';
ALTER DATABASE TestDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;
END;
GO
CREATE DATABASE TestDB;
GO

Upon re-running this code – which I assumed would both drop the database AND remove the physical data and log files, I received an error informing me that the files could not be created because they already existed.

Strange, I though and I re-ran the code several times.

Then I noticed that I had written

ALTER DATABASE TestDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;

instead of

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;

A rather subtle difference – however one that, as I found, makes a huge difference.

You see, when a database is normally dropped using a DROP statement the following two things happen

1. The database is deleted from the server – ie the entry in sys.databases is removed.
2. The physical data and logs files are removed.

However in my case, because I first set the database to OFFLINE the physical files on disk were not removed.

Books Online was very clear about the difference in behavior for a database in an OFFLINE state.

And it is good to now be totally clear on the difference because in certain situations – such as development – we might not care about the database, so deleting it constantly may be ok, so the following code may apply

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;

However, there are certain times when we may want the database files to hang around after deleting the database.

Such times could include, when removing a production database and we wish to keep the physical files as another form of backup (I’d always additionally take a traditional backup – as well as verifying the backup is good by physically restoring it to another server) or when wanting to rename the physical files of a database.

Additionally, we should note that if the database is online, but certain files are OFFLINE then those files will not be deleted either.

anyway, that was just my piece of wisdom for the day that I thought I’d share with you all.

Have a nice day.

cheers

Martin.