sp_detach_db works, re-attaching it doesn''t!

Question

sp_detach_db works, re-attaching it doesn''t!

NadJ

Ten Centuries

Points: 1031
More actions
October 26, 2006 at 1:28 am

#192885

I would be very grateful for some pointers...
I am trying to relocate our database log files from one drive to another (the way it should be). When I inherited the server all the databases and their log files (that is .mdf and .ldf files) were on the S: drive. My aim is to slowly move the .ldf's to the R: (Log) drive.
The server is a SQL 2000 Cluster (Active/Passive), has two volumes S: and R: in addition to C:\ with the corrects paths/folders already created.
The below works without any problems in our test environment (non cluster). However in the live environment (cluster) we get the below highlighted errors. The file names have been verified a million times. Changing the log file path back to it's original works without problems, therefore filename must be correct)
sp_detach_db 'Northwind'
...works fine.
..At this point I cut and paste the.ldf to it's new location on the R: Drive
sp_attach_db 'Northwind','S:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.mdf',
'R:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.LDF'
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'R:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.LDF' may be incorrect.
Server: Msg 5170, Level 16, State 1, Line 1
Cannot create file 'R:\Program Files\Microsoft SQL Server\MSSQL\Data\\Northwind_log.LDF' because it already exists.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'Northwind'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'R:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.LDF' may be incorrect.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 1

It's best not to relocate the log file using detach/attach. Try using the ALTER DATABASE MODIFY FILE ... command instead

ALTER DATABASE northwind MODIFY FILE (name = 'Northwind_log',

FILENAME = 'R:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_log.LDF')

Check Books Online for further information

NadJ Ten Centuries Points: 1031 More actions · Answer 2

Thanks for the suggestion, however after trying that I am getting:

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

MODIFY FILE failed. Do not specify physical name.

Just googled the error and found this link:

http://www.issociate.de/board/post/239918/Moving_the_distribution_database.html

Where somebody claims:

According to Books Online, FILENAME is permitted for tempdb only.

For a regular database, one can always detach it, copy it and attach

it from the new location. Yet an alternative is to add files, and

then use DBCC SHRINKFILE to empty the first and then remove that

file, a fairly cumbersome operation. But whether that is possible for

distribution, I have idea.

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 3

Yeah, you're right - I'm thinking in SQL2K5 mode!

I've just tried the detach/re-attach method, and it works for me (just as it does in your test environment).

You can't delete the primary logfile for a database, only additional logfiles, so I'm not sure that the method proposed above will actually work.

The only comment I would make is that in my installation, the physical logfile is called northwnd.ldf, not Northwind_Log.ldf

Johan Bijnens SSC Guru Points: 135550 More actions · Answer 4

Check your cluster service settings for sqlserver !

I suspect you didn't create a dependency for your sqlserver instance with the R-drive ! Therefor sqlserver cannot reach the drive !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

NadJ Ten Centuries Points: 1031 More actions · Answer 5

Cluster config is correct and dependencies all correctly setup. The few databases that were set up correctly do have their logs in the R: drive already!

Johan Bijnens SSC Guru Points: 135550 More actions · Answer 6

in that case also copy the path to the ldf file from explorer.

maybe the best way is to rightclick and hold and drag to the run window

(start\run) clear the existing ttext and then drag the file to the dropdownbox and then copy the full string.