DROP DATABASE problem

I am facing a trouble with DROP DATABASE command. Let me explain it this way:
Actually I am building an installation package which executes a series of SQL scripts to do the database changes on the target machine. Steps are briefly listed below:
1 - Create a database with simple CREATE DATABASE command
2 - Restore the database created above with RESTORE DATABASE command (with replace option)
3 - Configure other objects such as logins etc etc.
Now, everything works fine as long as all the scripts execute correctly.

However, I have seen instances when RESTORE DATABASE fails with "timeout expired" error. Well, I overcame this problem by simply increasing the timeout limit. But in that course what I observed is that when a restore database command "fails", then deleting the database immediately after that (as a part of rollback process) does not delete the database files (i.e., I can't see them thru management studio but..... physical *.mdf and *.ldf files still remain) Normally, when I use DROP DATABASE command with a database in "ONLINE" state these files are perfectly deleted, but when the database is in a "RESTORING" state, the physical files aren't getting deleted...
I don't know what's going wrong. Any ideas?
Any help will be greatly appreciated

Can't you just add a check for the files in your setup package, and if it's in a rollback state, just delete those files if found..?

/Kenneth

|||I can certainly do that. But I am keeping that as the last resort. Mainly because, that will involve querying the registry to get the instance names, then the data folders for those instances and then going to that location to check for the existence of mdf files. This would do my job, but then I beleive this is not the standard way to do that. Such processes may go out-of-sync very easily.

I was just trying to figure out why these files aren't deleted when database is in a restoring state. Or is it possible to correct this in any way?|||

I'm having a bit of trouble reproducing this...

Assuming SQL Server 2k 8.00.760 Dev Ed, and assuming 'Loading' is the same as your 'Restoring' state...

If I restore a db with norecovery option, it's in Loading state.
When I do a DROP DATABASE on that db, the files are deleted also.... as they should

It seems to be working ok from this end...

/Kenneth

|||Kenneth, I am not sure about "Loading" state but I was able to reproduce this error on two machines running instances of SQL Server 2005.
Method I used to reproduce the "restoring" state was writing a program (in my case C#) which connects to the DB and executes the "restore database" command with extremely low command timeout (i tried with 1 and 3 seconds, since database backup file's sixe is around 300 MB so it certainly takes more than that and a timeout occurs). So, when timeout occurs, the database is left in "restoring" state.... then I use the "drop database" command.. database entries are deleted but files remain. Probably, with "Loading" state this problem is not occurring. I am still not able to figure out what's wrong. Could you try reproducing that with database in "restoring" state?|||

I haven't had the time to do any actual testing, but if I was to speculate a bit, I believe that this is the intended behaviour..

If I understand it correctly, the 'problem' is after a 'failed' restore - that is, the restore process has been abnormally terminated for some reason..?
I would then assume that the state of the db would then be considered as 'offline'.

If that is indeed the case, then this quote from BOL:

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.

...my guess is that the above is what you're experiencing.
It does 'feel' reasonable, I think.

/Kenneth

|||

I think that the process you outlined for locating the database files (querying the registry etc...) may be a little over-complex.

As long as the database is attached to the instance then you can query the master.sys.master_files Catalog View (or master.dbo.sysaltfiles in SQL Server 2000) to determine the locations of the database's files prior to dropping the database and deleting the files (via xp_cmdshell).

Chris

|||Kenneth, In that case the only option I have is to ensure myself that the database files are deleted after every failed restore.
Chris, I just found out the method you described to find out the db filename. In fact, am new to SQL Server and so less aware of admin things.
Anyway, Thanks to both of you for all the time and help extended to me.