alter database is not happening in sqlserver 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » alter database is not happening in sqlserver...

alter database is not happening in sqlserver 2000

Rate Topic

Display Mode

Topic Options

Author

Message

Pritam Salvi

Posted Saturday, September 25, 2010 12:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53

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.

Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]

Post #993207

GilaMonster

Posted Saturday, September 25, 2010 2:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #993228

Pritam Salvi

Posted Saturday, September 25, 2010 3:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53

Thanks for reply.

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

Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]

Post #993233

Pritam Salvi

Posted Saturday, September 25, 2010 3:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:38 PM
Points: 102, Visits: 53

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

Regards,

Pritam Salvi
SQL DBA
Mumbai. India[size="7"][/size]

Post #993235

GilaMonster

Posted Saturday, September 25, 2010 5:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

Nope.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #993250

Joie Andrew

Posted Saturday, September 25, 2010 5:29 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 553, Visits: 1,032

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"

Post #993312

« Prev Topic | Next Topic »

Permissions