Modify Database Operating System File

• Bhushan Kalla

  Default port

  Points: 1475

  More actions

  August 24, 2006 at 1:40 pm

  #114102

  How to change database operating system file name through T-SQL command? For example, I want to change the below data file name from "FILENAME = 'M:\Data\MSSQL.1\MSSQL\Data\SAMPLEDB08242006_data.mdf'" to FILENAME = 'M:\Data\MSSQL.1\MSSQL\Data\SAMPLEDB08242006_data_2.mdf'.

  I greatly appreciate if anyone can help me on this.

  Thanks,

  Bhushan

  --------------------------------------------------------------------------

  Ilustration:

  USE

  MASTER

  GO

  CREATE

  DATABASE SAMPLEDB08242006

  ON

  (

  NAME = SAMPLEDB08242006_DATA,

  FILENAME = 'M:\Data\MSSQL.1\MSSQL\Data\SAMPLEDB08242006_data.mdf',

  SIZE = 30,

  MAXSIZE = 100,

  FILEGROWTH = 5 )

  LOG

  ON

  (

  NAME = SAMPLEDB08242006_LOG,

  FILENAME = 'N:\Logs\SAMPLEDB08242006_LOG.LDF',

  SIZE = 10,

  MAXSIZE = 25,

  FILEGROWTH = 1 )

  GO

• sqlemail@yahoo.com

  SSC Eights!

  Points: 919

  More actions

  August 26, 2006 at 4:19 pm

  #657006

  Hey Bhushan

  Follow these steps:

  1. Ensure nobody is using the Database, for this you might want to use sp_who OR sp_who2 stored procedure, if there is anybody using this DB than you need to kill those connection(s).
  2. Now ensure you are in Master DB (preferably) or any other DB other than "SAMPLEDB08242006"
  3. Using T-SQL, you can first detach this databse (refer sp_detach_db Stored Procedure)
  4. Using xp_cmdshell from T-SQL, you can rename your file 'M:\Data\MSSQL.1\MSSQL\Data\SAMPLEDB08242006_data.mdf' to '\MSSQL\Data\SAMPLEDB08242006_data_2.mdf'. The only catch is "M" drive is the "M" drive of your SQL Server box & not your local machine
  5. Using T-SQL, you can then attach this databse (refer sp_attach_db Stored Procedure)

  Hope that helps

  Thanks

  PP

  
  paul

• Bhushan Kalla

  Default port

  Points: 1475

  More actions

  August 27, 2006 at 5:37 pm

  #657015

  Thank you very much Paul.

   

  Bhushan