moving the log to a different location

Question

Post reply

moving the log to a different location

ElizabethHague

SSC Enthusiast

Points: 187
More actions
May 2, 2002 at 10:44 am

#357527

currently my data file and log are in the same physical location. i would like to move the log to a different physical location. i tried using the move database wizard and the sp_attach_file_db command, but they do not seem to be able to move ONLY the log. any suggestions? thanks
Elizabeth

Elizabeth

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

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

Hey Elizabeth,

You could run sp_detatch_db and then sp_attatch_db and specify the mdf and ldf locations to seperate drives.

Clive

Clive Strong

clivestrong@btinternet.com

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Try sp_detach_db to detach both and then sp_attach_db where you can set the path of both files when reattaching to the server.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

ElizabethHague SSC Enthusiast Points: 187 More actions · Answer 3

ok...maybe i am just not seeing it...i can detach with out a prob. when i reattach how do i specify where the new location will be? In this syntax the path seems to be where the files are located. is that correct? how do i specify a new location for the file?

thanks

EXEC sp_detach_db 'pubs'

EXEC sp_attach_db @dbname = N'pubs',

@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',

@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

Elizabeth

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Move the file thru Windows Explorer then just alter the @filename path associated with it to match.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Clive Strong SSChampion Points: 11222 More actions · Answer 5

Hi Elizabeth,

You just need to modify the @filnameX parameters. For Example...

EXEC sp_detach_db 'pubs'

GO

EXEC sp_attach_db @dbname = N'pubs',

@filename1 = N'e:\MSSQL7\Data\pubs.mdf',

@filename2 = N'l:\MSSQL\Logs\pubs_log.ldf'

GO

In this instance, after you detatch the pubs db, move the data file to the E drive and the log file to the L drive and then reattatch the file as above (using your own drive & path specifications!!).

Hope that helps!

Clive

Clive Strong

clivestrong@btinternet.com

Levi Justus SSCommitted Points: 1923 More actions · Answer 6

make sure you have move the files to the location where you want them to be before you attach.

The sp attach can be simple as this

sp_attach_db 'pubs','C:\newlocation\pubs.mdf','D:\newlocation\pubs.ldf'

Jesus My Saviour