Moving The Transaction Log file location

Is there a way to safely move the transaction log file ? Tried to detach
the db, move the transaction log file, then tried to re-attach the database
with a the modified transaction log path, but that was not successful.
ThanksRob wrote:
> Is there a way to safely move the transaction log file ? Tried to detach
> the db, move the transaction log file, then tried to re-attach the database
> with a the modified transaction log path, but that was not successful.
> Thanks
>
There should be no reason that wouldn't work. What error message did
you get? I'm sure it was something other than "not successful".|||Thanks Tracy,
I should have taken a closer look... I was simply testing the process on
the Pubs db at the time...
The error message stated something to the effect that the transaction log
file was not correct and that a new one would be created... which it did
do.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ukMM34hlGHA.2180@.TK2MSFTNGP05.phx.gbl...
> Rob wrote:
>> Is there a way to safely move the transaction log file ? Tried to
>> detach the db, move the transaction log file, then tried to re-attach the
>> database with a the modified transaction log path, but that was not
>> successful.
>> Thanks
> There should be no reason that wouldn't work. What error message did you
> get? I'm sure it was something other than "not successful".|||Rob wrote:
> Thanks Tracy,
> I should have taken a closer look... I was simply testing the process on
> the Pubs db at the time...
> The error message stated something to the effect that the transaction log
> file was not correct and that a new one would be created... which it did
> do.
>
You still shouldn't have gotten an error, if you were indeed attaching
the correct log file. It is trivial to detach a database file or log
file, move to a new location, and then reattach.|||Rob wrote:
> Thanks Tracy,
> I should have taken a closer look... I was simply testing the process on
> the Pubs db at the time...
> The error message stated something to the effect that the transaction log
> file was not correct and that a new one would be created... which it did
> do.
>
Are you sure you ran a sp_attach_db and not sp_attach_single_file_db?
I'm not sure though but I'd assume that sp_attach_db wouldn't create a
new logfile.
Another work around could also be to use RESTORE DATABASE with the MOVE
option. You could restore to a database with another name and once it
was succeded you could delete the old one and then rename the new one.
It might be a bit more cumbersome but it's a safer method since you'll
always have a running version of the database. When you use sp_detach_db
you haven't even got your source db running in case the sp_attach_db fails.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||alter database <dbName> modify file (name=<dbName_log>, filename ='path_to_new_location')
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rob" <rwchome@.comcast.net> wrote in message
news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.comcast.com...
> Is there a way to safely move the transaction log file ? Tried to detach
> the db, move the transaction log file, then tried to re-attach the
> database with a the modified transaction log path, but that was not
> successful.
> Thanks
>|||> alter database <dbName> modify file (name=<dbName_log>, filename = 'path_to_new_location')
Hmm, but don't you also have to shutdown SQL Server and then move the physical file as well?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote in message
news:%23urBoQolGHA.3468@.TK2MSFTNGP03.phx.gbl...
> alter database <dbName> modify file (name=<dbName_log>, filename = 'path_to_new_location')
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Rob" <rwchome@.comcast.net> wrote in message news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.comcast.com...
>> Is there a way to safely move the transaction log file ? Tried to detach the db, move the
>> transaction log file, then tried to re-attach the database with a the modified transaction log
>> path, but that was not successful.
>> Thanks
>|||No. Only stop the database and start it back up. (ALTER DATABASE ... SET
OFFLINE/ONLINE)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUR0WyplGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename =>> 'path_to_new_location')
> Hmm, but don't you also have to shutdown SQL Server and then move the
> physical file as well?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:%23urBoQolGHA.3468@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename =>> 'path_to_new_location')
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Rob" <rwchome@.comcast.net> wrote in message
>> news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.comcast.com...
>> Is there a way to safely move the transaction log file ? Tried to
>> detach the db, move the transaction log file, then tried to re-attach
>> the database with a the modified transaction log path, but that was not
>> successful.
>> Thanks
>>
>|||> No. Only stop the database and start it back up. (ALTER DATABASE ... SET
> OFFLINE/ONLINE)
This is documented in SQL Server 2005 BOL in the topic "Moving User
Databases"
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13fb-457d-996a-66ffc2d55b79.htm).
I should point out that the procedures in this topic apply only to SQL
Server 2005. For SQL Server 2000 and earlier, you need to detach and attach
the database.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:uKuxeptlGHA.3468@.TK2MSFTNGP03.phx.gbl...
> No. Only stop the database and start it back up. (ALTER DATABASE ... SET
> OFFLINE/ONLINE)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OUR0WyplGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename =>> 'path_to_new_location')
>> Hmm, but don't you also have to shutdown SQL Server and then move the
>> physical file as well?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
>> wrote in message news:%23urBoQolGHA.3468@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename =>> 'path_to_new_location')
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Rob" <rwchome@.comcast.net> wrote in message
>> news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.comcast.com...
>> Is there a way to safely move the transaction log file ? Tried to
>> detach the db, move the transaction log file, then tried to re-attach
>> the database with a the modified transaction log path, but that was
>> not successful.
>> Thanks
>>
>>
>|||Hmm, now I'm confused. The BOL URL states you do have to stop the database, so does below script:
EXEC master.dbo.xp_cmdshell 'MD C:\TestDb'
GO
CREATE DATABASE TestDb
ON PRIMARY
(NAME = N'TestDb', FILENAME = N'C:\TestDb.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
(NAME = N'TestDb_log', FILENAME = N'C:\TestDb_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE TestDb MODIFY FILE (NAME=TestDb_log, FILENAME = N'C:\TestDb\TestDb_log.ldf')
GO
ALTER DATABASE TestDb SET OFFLINE
GO
USE TestDb --Error, as expected
GO
ALTER DATABASE TestDb SET ONLINE --Error
GO
SHUTDOWN WITH NOWAIT
--Start the service
ALTER DATABASE TestDb SET ONLINE --Error
--Move the file physically
ALTER DATABASE TestDb SET ONLINE --Success
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%23rwlmvulGHA.1208@.TK2MSFTNGP02.phx.gbl...
>> No. Only stop the database and start it back up. (ALTER DATABASE ... SET OFFLINE/ONLINE)
> This is documented in SQL Server 2005 BOL in the topic "Moving User Databases"
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13fb-457d-996a-66ffc2d55b79.htm). I
> should point out that the procedures in this topic apply only to SQL Server 2005. For SQL Server
> 2000 and earlier, you need to detach and attach the database.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote in message
> news:uKuxeptlGHA.3468@.TK2MSFTNGP03.phx.gbl...
>> No. Only stop the database and start it back up. (ALTER DATABASE ... SET OFFLINE/ONLINE)
>> --
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OUR0WyplGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename = 'path_to_new_location')
>> Hmm, but don't you also have to shutdown SQL Server and then move the physical file as well?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote in message
>> news:%23urBoQolGHA.3468@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename = 'path_to_new_location')
>> --
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Rob" <rwchome@.comcast.net> wrote in message
>> news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.comcast.com...
>> Is there a way to safely move the transaction log file ? Tried to detach the db, move the
>> transaction log file, then tried to re-attach the database with a the modified transaction log
>> path, but that was not successful.
>> Thanks
>>
>>
>>
>|||You gotta move the log file. ;-)
EXEC master.dbo.xp_cmdshell 'MD C:\TestDb'
GO
CREATE DATABASE TestDb
ON PRIMARY
(NAME = N'TestDb', FILENAME = N'C:\TestDb.mdf' , SIZE = 3072KB , FILEGROWTH
= 1024KB )
LOG ON
(NAME = N'TestDb_log', FILENAME = N'C:\TestDb_log.ldf' , SIZE = 1024KB ,
FILEGROWTH = 10%)
GO
ALTER DATABASE TestDb MODIFY FILE (NAME=TestDb_log, FILENAME =N'C:\TestDb\TestDb_log.ldf')
GO
ALTER DATABASE TestDb SET OFFLINE
GO
--MOVE THE LOG
exec master..xp_cmdshell N'move C:\TestDb_log.ldf C:\TestDb\TestDb_log.ldf'
go
ALTER DATABASE TestDb SET ONLINE --NO Error now
go
select *
into TestDb..tb1
from sysprocesses
go
drop database TestDb
go
-oj
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%239gCxM2lGHA.2392@.TK2MSFTNGP04.phx.gbl...
> Hmm, now I'm confused. The BOL URL states you do have to stop the
> database, so does below script:
> EXEC master.dbo.xp_cmdshell 'MD C:\TestDb'
> GO
> CREATE DATABASE TestDb
> ON PRIMARY
> (NAME = N'TestDb', FILENAME = N'C:\TestDb.mdf' , SIZE = 3072KB ,
> FILEGROWTH = 1024KB )
> LOG ON
> (NAME = N'TestDb_log', FILENAME = N'C:\TestDb_log.ldf' , SIZE = 1024KB ,
> FILEGROWTH = 10%)
> GO
> ALTER DATABASE TestDb MODIFY FILE (NAME=TestDb_log, FILENAME => N'C:\TestDb\TestDb_log.ldf')
> GO
> ALTER DATABASE TestDb SET OFFLINE
> GO
> USE TestDb --Error, as expected
> GO
> ALTER DATABASE TestDb SET ONLINE --Error
> GO
> SHUTDOWN WITH NOWAIT
> --Start the service
> ALTER DATABASE TestDb SET ONLINE --Error
> --Move the file physically
> ALTER DATABASE TestDb SET ONLINE --Success
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%23rwlmvulGHA.1208@.TK2MSFTNGP02.phx.gbl...
>> No. Only stop the database and start it back up. (ALTER DATABASE ... SET
>> OFFLINE/ONLINE)
>> This is documented in SQL Server 2005 BOL in the topic "Moving User
>> Databases"
>> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13fb-457d-996a-66ffc2d55b79.htm).
>> I should point out that the procedures in this topic apply only to SQL
>> Server 2005. For SQL Server 2000 and earlier, you need to detach and
>> attach the database.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
>> wrote in message news:uKuxeptlGHA.3468@.TK2MSFTNGP03.phx.gbl...
>> No. Only stop the database and start it back up. (ALTER DATABASE ... SET
>> OFFLINE/ONLINE)
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OUR0WyplGHA.4212@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename =>> 'path_to_new_location')
>> Hmm, but don't you also have to shutdown SQL Server and then move the
>> physical file as well?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
>> wrote in message news:%23urBoQolGHA.3468@.TK2MSFTNGP03.phx.gbl...
>> alter database <dbName> modify file (name=<dbName_log>, filename =>> 'path_to_new_location')
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Rob" <rwchome@.comcast.net> wrote in message
>> news:T4CdnQz5gZ9iVgfZnZ2dnUVZ_uydnZ2d@.comcast.com...
>> Is there a way to safely move the transaction log file ? Tried to
>> detach the db, move the transaction log file, then tried to re-attach
>> the database with a the modified transaction log path, but that was
>> not successful.
>> Thanks
>>
>>
>>
>>
>sql