none
Change the .mdf & .ldf files of the standby database

    Question

  • Hi,

      Im having the database configured log shipping.

       I want to change the location of .mdf & .ldf files, how can i do it, in standby database server.

       Do i have to move .tuf file also.

     Please provide me the detailed steps.

     Thanks

     

     

     

    Wednesday, October 09, 2013 5:30 PM

Answers


  • Steps to perform on primary:
    =============================

    In this case, you need to change the mode to NoRecovery mode. To do so, perform the below given steps:

    1) Do the following in the SQL Server Management Studio

    2) Right-click the ‘primary database‘ and select 'Properties'

    3) Click ‘Transaction Log Shipping‘, and then click Secondary server instances and database

    4) A dialog box Secondary Database Setting will open; switch to 'Restore Transaction Log' tab.

    5) Select No recovery mode. The recovery mode will be changed to 'NoRecovery' mode.

    Steps to perform on secondary:
    ===============================

    1) After the mode is changed, disable ‘Log Shipping Restore Job’ on the secondary server. Do the following:

    2) In SQL Server Management Studio, go to root -> ‘SQL Server Agent‘ -> ‘Jobs‘ -> ‘Log Shipping Restore Job‘.
       Right-click the job and then click ‘Disable‘.

       --Disable the copy and restore job

    3) Run the ALTER command on the secondary Log Shipping SQL Server. It will help you determine the new location for secondary database and log file.


    USE master;
    GO
    ALTER DATABASE <databasename>
    MODIFY FILE
    (
         NAME =<logical_name>
         FILENAME = N'<physical_name>
    );
    GO

    ALTER DATABASE <databasename>
    MODIFY FILE
    (
         NAME = <logical log name>,
         FILENAME = N'physical log name'
    );
    GO


    4) Stop all the instance services of secondary SQL Server by going to SQL Server Configuration Manager.

        --Stop SQL instance services

    5) Physically Move the files of Log Shipping Secondary database to any other location on the computer.
       
         Move the files from c:\ to d:\

    6) In SQL Server Configuration Manager, restart the instance SQL services that you stopped in Step 4.


    7) Apply the pending transaction logs on the secondary site.

    8) On the Secondary SQL Server, enable the SQL Server Agent Job.


    Steps to performing on secondary below need to be performed on primary:
    ================================================

    1) Do the following in the SQL Server Management Studio

    2) Right-click the ‘primary database‘ and select 'Properties'

    3) Click ‘Transaction Log Shipping‘, and then click Secondary server instances and database

    4) A dialog box Secondary Database Setting will open; switch to 'Restore Transaction Log' tab.

    5) Select standby/read only mode. The recovery mode will be changed now.

    .TUF file is essential for recovery of secondary server.

    Read more in the below article.

    http://sqlserveraid.wordpress.com/2011/03/09/what-is-tuf-file-log-shipping/

    Thursday, October 10, 2013 5:37 AM
  • Thursday, October 10, 2013 5:54 AM

All replies

  • Hi,

    Which version of SQL Server you are using?

    Wednesday, October 09, 2013 5:41 PM
  • MS SQL 2008 R2 on windows 2008R2
    Wednesday, October 09, 2013 6:08 PM
  • Hi,

      Im having the database configured log shipping.

       I want to change the location of .mdf & .ldf files, how can i do it, in standby database server.

       Do i have to move .tuf file also.

     Please provide me the detailed steps.

     Thanks

     

     

     

    Hello ,

    Please go through this article it will be helpful

    http://blogs.technet.com/b/mdegre/archive/2012/01/03/3272523.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, October 09, 2013 7:24 PM

  • Steps to perform on primary:
    =============================

    In this case, you need to change the mode to NoRecovery mode. To do so, perform the below given steps:

    1) Do the following in the SQL Server Management Studio

    2) Right-click the ‘primary database‘ and select 'Properties'

    3) Click ‘Transaction Log Shipping‘, and then click Secondary server instances and database

    4) A dialog box Secondary Database Setting will open; switch to 'Restore Transaction Log' tab.

    5) Select No recovery mode. The recovery mode will be changed to 'NoRecovery' mode.

    Steps to perform on secondary:
    ===============================

    1) After the mode is changed, disable ‘Log Shipping Restore Job’ on the secondary server. Do the following:

    2) In SQL Server Management Studio, go to root -> ‘SQL Server Agent‘ -> ‘Jobs‘ -> ‘Log Shipping Restore Job‘.
       Right-click the job and then click ‘Disable‘.

       --Disable the copy and restore job

    3) Run the ALTER command on the secondary Log Shipping SQL Server. It will help you determine the new location for secondary database and log file.


    USE master;
    GO
    ALTER DATABASE <databasename>
    MODIFY FILE
    (
         NAME =<logical_name>
         FILENAME = N'<physical_name>
    );
    GO

    ALTER DATABASE <databasename>
    MODIFY FILE
    (
         NAME = <logical log name>,
         FILENAME = N'physical log name'
    );
    GO


    4) Stop all the instance services of secondary SQL Server by going to SQL Server Configuration Manager.

        --Stop SQL instance services

    5) Physically Move the files of Log Shipping Secondary database to any other location on the computer.
       
         Move the files from c:\ to d:\

    6) In SQL Server Configuration Manager, restart the instance SQL services that you stopped in Step 4.


    7) Apply the pending transaction logs on the secondary site.

    8) On the Secondary SQL Server, enable the SQL Server Agent Job.


    Steps to performing on secondary below need to be performed on primary:
    ================================================

    1) Do the following in the SQL Server Management Studio

    2) Right-click the ‘primary database‘ and select 'Properties'

    3) Click ‘Transaction Log Shipping‘, and then click Secondary server instances and database

    4) A dialog box Secondary Database Setting will open; switch to 'Restore Transaction Log' tab.

    5) Select standby/read only mode. The recovery mode will be changed now.

    .TUF file is essential for recovery of secondary server.

    Read more in the below article.

    http://sqlserveraid.wordpress.com/2011/03/09/what-is-tuf-file-log-shipping/

    Thursday, October 10, 2013 5:37 AM
  • Thursday, October 10, 2013 5:54 AM
  • I am moving it to tools.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Friday, October 11, 2013 11:33 AM