alter database is not happening in sqlserver 2000

• Pritam Salvi

  SSC Eights!

  Points: 978

  More actions

  September 25, 2010 at 12:24 am

  #224427

  Hi all,

  I am using sql server 2000

  I am trying to use alter database option to move datafiles.

  But it showed me error below-

  Server: Msg 5037, Level 16, State 1, Line 1

  MODIFY FILE failed. Do not specify physical name.

  Look at the my qry

  ALTER DATABASE NewTestDB

  MODIFY FILE (NAME = NewTestDB_Data, FILENAME = 'D:\SQLData\NewTestDB_Data.mdf');

  ALTER DATABASE NewTestDB

  MODIFY FILE (NAME = NewTestDB_Log, FILENAME = 'D:\SQLData\NewTestDB_Log.ldf');

  Whats wrong in this?

  Pls let me know why alter database is not happening.

  [font="Verdana"]Regards,

  Pritam Salvi
  SQL DBA
  Mumbai. India[/font]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 25, 2010 at 2:57 am

  #1227353

  The ALTER DATABASE ... MOVE was not an option for any database other than TempDB on SQL 2000.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Pritam Salvi

  SSC Eights!

  Points: 978

  More actions

  September 25, 2010 at 3:17 am

  #1227357

  Thanks for reply.

  Is there any way to do that except of doing detach/atach while copying from windows.

  [font="Verdana"]Regards,

  Pritam Salvi
  SQL DBA
  Mumbai. India[/font]

• Pritam Salvi

  SSC Eights!

  Points: 978

  More actions

  September 25, 2010 at 3:19 am

  #1227358

  I want to move data/log files from C drive to E drive.

  Is there any other option in SQL Server 2000 except of detach/atach

  [font="Verdana"]Regards,

  Pritam Salvi
  SQL DBA
  Mumbai. India[/font]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 25, 2010 at 5:32 am

  #1227370

  Nope.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Joie Andrew

  One Orange Chip

  Points: 27361

  More actions

  September 25, 2010 at 5:29 pm

  #1227412

  Nope. Sorry, you have to detach/attach. You do not have to do it through the GUI though; you can use SPs to accomplish it if you were wanting to script it out.

  How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

  Joie Andrew
  "Since 1982"