Moving Tran Log To Another Drive

I have SQL SERVER and all the data and log files (ldf's) for 20 DB's are on
the same drive E.
I have to move the Log files to the M drive for performance.
All the databases are used 24/7 for data viewing.
Mon-Fri 9-6 used for modifcations.
The only way i can do this is at night or over the weekend.
I have to run this script for all the databases
Is this script correct?
ALTER DATABASE DB1
SET SINGLE_USER
go
EXEC sp_detach_db DB1
<<MOVE THE FILES TO M DRIVE>>
EXEC sp_attach_db DB1,
@.filename1 = N'E:\SQL1DataFiles\DB1.mdf',
@.filename2 = N'M:\SQL1LogFiles\DB1_log.ldf'
Appreciate your helpOne potential issue is that, if there are users connected, set single_user
will wait until all the users disconnect from the database.
Why don't you just detach and attach the databases manually using Enterprise
Manager o Management Studio. You will not need to specify file paths and can
disconnect users if needed.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alex Sadykov" wrote:
> I have SQL SERVER and all the data and log files (ldf's) for 20 DB's are on
> the same drive E.
> I have to move the Log files to the M drive for performance.
> All the databases are used 24/7 for data viewing.
> Mon-Fri 9-6 used for modifcations.
> The only way i can do this is at night or over the weekend.
> I have to run this script for all the databases
> Is this script correct?
> ALTER DATABASE DB1
> SET SINGLE_USER
> go
> EXEC sp_detach_db DB1
> <<MOVE THE FILES TO M DRIVE>>
> EXEC sp_attach_db DB1,
> @.filename1 = N'E:\SQL1DataFiles\DB1.mdf',
> @.filename2 = N'M:\SQL1LogFiles\DB1_log.ldf'
>
> Appreciate your help
>
>
>|||Ben thanks for your help
If i sp_detach_db will all the users be kicked off the DB?
If yes i should probably do this at night?
That way the downtime will be minimal?
Is there a way to move log files from sql to a new location?
If not then i can just move them from explorer and give tht paths in the
sp_attach_db statement
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:C29FE6DF-D758-4667-BDE1-A2B12C643E6A@.microsoft.com...
> One potential issue is that, if there are users connected, set single_user
> will wait until all the users disconnect from the database.
> Why don't you just detach and attach the databases manually using
> Enterprise
> Manager o Management Studio. You will not need to specify file paths and
> can
> disconnect users if needed.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alex Sadykov" wrote:
>> I have SQL SERVER and all the data and log files (ldf's) for 20 DB's are
>> on
>> the same drive E.
>> I have to move the Log files to the M drive for performance.
>> All the databases are used 24/7 for data viewing.
>> Mon-Fri 9-6 used for modifcations.
>> The only way i can do this is at night or over the weekend.
>> I have to run this script for all the databases
>> Is this script correct?
>> ALTER DATABASE DB1
>> SET SINGLE_USER
>> go
>> EXEC sp_detach_db DB1
>> <<MOVE THE FILES TO M DRIVE>>
>> EXEC sp_attach_db DB1,
>> @.filename1 = N'E:\SQL1DataFiles\DB1.mdf',
>> @.filename2 = N'M:\SQL1LogFiles\DB1_log.ldf'
>>
>> Appreciate your help
>>
>>|||> If i sp_detach_db will all the users be kicked off the DB?
No, you need to kill all connections first
> Is there a way to move log files from sql to a new location?
Use copy-paste now that detaching the database
"Alex Sadykov" <alexsadykov@.gmail.com> wrote in message
news:u7J7XTKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Ben thanks for your help
> If i sp_detach_db will all the users be kicked off the DB?
> If yes i should probably do this at night?
> That way the downtime will be minimal?
> Is there a way to move log files from sql to a new location?
> If not then i can just move them from explorer and give tht paths in the
> sp_attach_db statement
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:C29FE6DF-D758-4667-BDE1-A2B12C643E6A@.microsoft.com...
>> One potential issue is that, if there are users connected, set
>> single_user
>> will wait until all the users disconnect from the database.
>> Why don't you just detach and attach the databases manually using
>> Enterprise
>> Manager o Management Studio. You will not need to specify file paths and
>> can
>> disconnect users if needed.
>> Hope this helps,
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Alex Sadykov" wrote:
>> I have SQL SERVER and all the data and log files (ldf's) for 20 DB's are
>> on
>> the same drive E.
>> I have to move the Log files to the M drive for performance.
>> All the databases are used 24/7 for data viewing.
>> Mon-Fri 9-6 used for modifcations.
>> The only way i can do this is at night or over the weekend.
>> I have to run this script for all the databases
>> Is this script correct?
>> ALTER DATABASE DB1
>> SET SINGLE_USER
>> go
>> EXEC sp_detach_db DB1
>> <<MOVE THE FILES TO M DRIVE>>
>> EXEC sp_attach_db DB1,
>> @.filename1 = N'E:\SQL1DataFiles\DB1.mdf',
>> @.filename2 = N'M:\SQL1LogFiles\DB1_log.ldf'
>>
>> Appreciate your help
>>
>>
>