alter database is not happening in sqlserver 2000

Question

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]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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: 27360 More actions · Answer 5

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"