Post reply

Replication and Moving the DB Log

  • October 17, 2011 at 6:59 pm

    #261543

    Hi All,

    I have setup replication successfully. Now it has been running for 2 months and the need has arisen to change the DB log location. I am following the steps below to do this task.

    1) I stop replication

    2) Detach the DB

    3) Move the log to the new location and attach the Db back pointing to the new log location.

    4) Do I just start replication and it works fine ?

    My question is will moving the log location break replication ? Do I have to setup replication from the beginning ? Also what is the best way to stop replication ?

    Any help is appreciated.

    Thanks,

    Suri

  • October 18, 2011 at 3:57 am

    #1395760

    Hi Suri,

    To this this;

    1. Delete the subscription (guessing it won't let you detach the db until you do so)

    2. Detach the db

    3. Move the db accordingly

    4. Recreate the subscription (might be worth creating a new snapshot to sync with depending on the size of the db)

    Don't forget to test this 1st in a non-prod environment! 😉

    Cheers

    Vultar

  • October 18, 2011 at 4:09 am

    #1395765

    Another thing that's worth trying is backing up the database and restoring to the new location. This should obviate the need to delete the subscription. Vultar's advice still stands, though - try it in a test environment first!

    John

  • October 18, 2011 at 8:26 am

    #1396000

    Thanks, I will try this.

  • October 18, 2011 at 8:30 am

    #1396008

    There's no need to remove replication and detach, and backup/restore (while it works) is not exactly quick.

    Look in Books Online under ALTER DATABASE for the syntax for ALTER DATABASE ... MOVE. Run that for the DB to change the recorded location of the transaction log, then take the database offline (you can do that without removing replication), move the file to the new location, then bring the database back online.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 18, 2011 at 2:55 pm

    #1396352

    I finished the testing on this and it worked as suggested by Gila Monster. Thanks. I used a similar to this.

    USE master

    SELECT name, physical_name

    FROM sys.master_files

    WHERE database_id = DB_ID('testdb');

    ALTER DATABASE testdb

    SET offline

    GO

    ----ALTER DATABASE testdb SET OFFLINE ROLLBACK IMMEDIATE;

    ALTER DATABASE testdb

    MODIFY FILE ( NAME = testdb_log, FILENAME = "C:\testing\testdb_1.ldf")

    GO

    ALTER DATABASE testdb

    SET online

    GO

    USE testdb

    GO

    sp_helpfile

  • October 19, 2011 at 4:53 am

    #1396536

    Thanks for posting the code up, I didn't think of doing it that way.. much quicker than the way I suggested so I'll have to remember that one!

    Cheers

    Vultar

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

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