HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- MacIf you are able to detach/ attach succesfully, whats the
problem?
>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>|||I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> >--Original Message--
> >For performance reason, I have decided to move my
> database files around. I
> >have tried using Attach and Detach database to move
> physical location of the
> >files successfully.
> >
> >What I want to do is to move the transaction log file to
> a different
> >location or even better get rid of it and create a new
> log file in its new
> >location. I have tried dbcc shrinkfile to truncate the
> log file but does
> >not allow me to remove it or empty it into another log
> file that I have
> >created in a new location. Any suggestion on
> moving/removing database
> >files are appreciated.
> >
> >- Mac
> >
> >
> >.
> >|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file
to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> > If Im not mistaken, you would:
> >
> > Detach.
> > Turn off SQL.
> > Delete log file.
> > Turn on SQL.
> > Attach single file with sp_attach_single_file_db.
> >
> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.
>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
>> ChrisR wrote:
>> > If Im not mistaken, you would:
>> >
>> > Detach.
>> > Turn off SQL.
>> > Delete log file.
>> > Turn on SQL.
>> > Attach single file with sp_attach_single_file_db.
>> >
>> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>> I've never had any problems doing this. I'd certainly
make a copy of both
>> the MDF and LDF files before doing it, though.
>> John.
>>
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
> >I want to drop the log file altogether and start with a new one in a new
> > location. I could not do this with Detach/Attach as far as I can tell.
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> >> If you are able to detach/ attach succesfully, whats the
> >> problem?
> >>
> >>
> >> >--Original Message--
> >> >For performance reason, I have decided to move my
> >> database files around. I
> >> >have tried using Attach and Detach database to move
> >> physical location of the
> >> >files successfully.
> >> >
> >> >What I want to do is to move the transaction log file to
> >> a different
> >> >location or even better get rid of it and create a new
> >> log file in its new
> >> >location. I have tried dbcc shrinkfile to truncate the
> >> log file but does
> >> >not allow me to remove it or empty it into another log
> >> file that I have
> >> >created in a new location. Any suggestion on
> >> moving/removing database
> >> >files are appreciated.
> >> >
> >> >- Mac
> >> >
> >> >
> >> >.
> >> >
> >
> >
>