Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

alter database is not happening in sqlserver 2000 Expand / Collapse
Author
Message
Posted Saturday, September 25, 2010 12:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Saturday, September 25, 2010 2:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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
Posted Saturday, September 25, 2010 3:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Saturday, September 25, 2010 3:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Saturday, September 25, 2010 5:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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
Posted Saturday, September 25, 2010 5:29 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 709, Visits: 1,423
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 »

Add to briefcase

Permissions Expand / Collapse