Changing SQL Log location

Question

Changing SQL Log location

paeast

SSC Eights!

Points: 908
More actions
February 10, 2004 at 1:57 am

#65929

Is it possible to change the location of the log file on SQL Server 2000
I have ran out of space on my server and have just added a new drive. If I can get the log file on this new partication all of my space prolems will be stopped

thanks Paul

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

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

jima SSCrazy Points: 2192 More actions · Answer 1

Hi Paul, The way i do this ( may not be the best way, but it works ! ), is to backup the database & then use a script to restore it, with some extras options on the restore command that alters where the data and/or log file are located e.g; RESTORE DATABASE dbname FROM DISK='disk_location\dbname.dmp' WITH RECOVERY, MOVE 'dbname_Data' TO 'disk_location\dbname_Data.mdf', MOVE 'dbname_Log' TO 'disk_location\dbname_Log.ldf'. Hope that helps!. Any questions let me know. Regards, j

SQLBill SSC Guru Points: 51440 More actions · Answer 2

You can do as Jima suggests or just detach the database, move the .ldf to where you want it to be and then when you attach the database just tell it the log files new location.

-SQLBill

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

If you want to change the location of SQL Server error log file, change it from properties --> general tab --> startup patameters.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 4

Here is the steps and the code I use for years... Hope it helps...

USE DB_Name

go

sp_helpfile

go

--it'll give you files locations like:

E:\datafolder\MSSQL\Data\ENT_DATA_Data.MDF

E:\datafolder\MSSQL\Data\ENT_DATA_Log.LDF

--detach theDB

USE MASTER

go

SP_DETACH_DB 'DB_Name'

GO

--Minimize QA and copy AND PASTE THE FILES TO THE NEW LOCATION

--Copy and paste the path bellow

USE MASTER

go

SP_ATTACH_DB 'dbname', 'E:\MRE-CCA-SQL_Data\MSSQL\Data\ENT_DATA_DATA.mdf',

'G:\MRE-CCA-SQL_LOG\MSSQL\LOG\ENT_DATA_log.ldf'

GO

--Check for the new location

USE ENT_DATA

go

sp_helpfile

go

--Check if everything was OK and the Db is on-line - status field

USE MASTER

go

sp_helpDB 'dbname'

go

jima SSCrazy Points: 2192 More actions · Answer 5

Hi Paul, Allen's reply got me thinking, did you mean you wanted to relocate a Db log file or the Sql Error Log file ????. j

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 6

I understood - SQL transaction log files. To change them in the Server property will reflect only newly created Dbs. The script I posted shows how to move user DBs logs of already existing Dbs.

MJ