Delete a mdf and ldf files using T-SQL

• Jaya Chitra

  SSCommitted

  Points: 1916

  More actions

  April 24, 2009 at 6:16 am

  #130281

  Hi all,

  In my application, when a database already exists in the database, in the procedure itself the database will be deleted and a new database will be created.

  This works fine if the database is listed in the database list of Management Studio.

  In one scenario, the database is not listed but the mdf and ldf is in the data location of the MSSQL.

  Now I need to delete or move or replace the existing with the new mdf and ldf files.

  For this I have tried the below statements:

  use master

  go

  Alter database Test modify file (name = test, filename = 'd:\test.mdf')

  go

  Alter database Test delete file (name = test, filename = 'd:\Test_log.ldf')

  go

  It changes the path in the system catalog but not physically. If we restart the SQL instance also it is not working.

  Can any one tell me how to do this?

  Thank you a lot in advance.

  [font="Arial"]Nothing is impossible with Hard Work[/font]:)

• Mohit K. Gupta

  SSChampion

  Points: 12130

  More actions

  April 26, 2009 at 4:12 pm

  #984138

  Hi Jaya,

  I think of two options...

  1) If the files can be attached, then attach it and delete the database. This will delete physical files.

  2) Delete the files manually using xp_cmdshell extended stored procedure.

  Thanks.

  Mohit.

  [font="Arial"]---

  Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
  Microsoft FTE - SQL Server PFE

  * Some time its the search that counts, not the finding...
  * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

  How to ask for help .. Read Best Practices here[/url].

• Jaya Chitra

  SSCommitted

  Points: 1916

  More actions

  April 26, 2009 at 8:52 pm

  #984161

  Dear Mohit,

  Thank you for your reply.

  Can you tell me how to do the same with the both ways?

  Thank you a lot in advance.

  [font="Arial"]Nothing is impossible with Hard Work[/font]:)

• Anam Verma

  SSCertifiable

  Points: 5365

  More actions

  April 26, 2009 at 10:13 pm

  #984167

  Hi Jaya

  If your requirement is only to create the database, then why don't you use the approach

  check if database exists then restore the database with replace. If you want to move the files onto different disk location then use MOVE clause

  RESTORE DATABASE [testanam]

  FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\testanam.bak'

  WITH FILE = 1,

  MOVE N'testanam' TO N'M:\anam\testa1.mdf',

  MOVE N'testanam_log' TO N'M:\anam\testa1_log.ldf',

  NOUNLOAD, REPLACE, STATS = 10

  The problem using the above command is that it will create new data and log files at new location but it will not delete the old data file. Yes only data file as log file gets deleted. Now you may have to manually delete this file.

  if database is new, create it with files at the disk location you want.