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 o
n
> 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...[vbcol=seagreen]
> 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:
>|||> 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...
>