locked
Log shipping secondary database created in the wrong location (2008 r2) RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Primary SQL Server 2008 r2
    Secondary SQL Server 2008 r2
    Secondary Database Default locations;
    Data: D:\SQLData
    Log: L:\SQLLogs

    From the primary server I'm setting up log shipping to a secondary server (letting the wizard create secondary database), for some reason the secondary database files (MDF & LDF)  are created in D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA  even though the default Data & Log location says to create them in another location.

    Does anyone know why this happens? or how to tell sql to use the default data & log locations?

    I know the work around is to manually create the secondary database prior to setting up the log shipping (or move the databases after the fact).  I'm worried that this step might be forgotten when a new database is added to the primary and needs to be log shipped to the secondary.  

    Wednesday, September 24, 2014 8:50 PM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote
    You can click on the "Restore Options" button in the Secondary Setting dialog to specify the custom locations. I would surmise the default location in while setting up Log Shipping is the location where these files resided on the primary server (for example, when you backup your DB and restore it on another server, it tries to restore it to the same location as that on the source server, unless you specify new locations)

    Satish Kartan www.sqlfood.com

    Wednesday, September 24, 2014 9:18 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You can set the default data and log locations at the instance level. 

    Right click on the secondary instance -> properties->Data base settings -> and at bottom right mention the default data and log file paths and click ok.

    Once you set this up,  the new databases you are going to create will be saved in those locations.

    Thanks,

    Bhanu

    Wednesday, September 24, 2014 8:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    @bhanu the default data & log locations have already been set.  I confirmed they were working by creating a new database.  It's just the log shipping wizard doesn't want to use this info. 
    Wednesday, September 24, 2014 8:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Got it.  If you already configured before setting up the log shipping it should use those default locations. 

    Did you configure default locations before setting up log shipping or after? It looks like you did it correctly.

    Here is the MSDN article explains the same.

    http://msdn.microsoft.com/en-us/library/ms189970.aspx

    Thanks,

    Bhanu

    Wednesday, September 24, 2014 9:04 PM
  • Question
    Sign in to vote
    2
    Sign in to vote
    You can click on the "Restore Options" button in the Secondary Setting dialog to specify the custom locations. I would surmise the default location in while setting up Log Shipping is the location where these files resided on the primary server (for example, when you backup your DB and restore it on another server, it tries to restore it to the same location as that on the source server, unless you specify new locations)

    Satish Kartan www.sqlfood.com

    Wednesday, September 24, 2014 9:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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 this below article.

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

    Or Follow the below link ...

    http://www.mssqltips.com/sqlservertip/2836/steps-to-move-sql-server-log-shipping-secondary-database-files/

    Raju Rasagounder Sr MSSQL DBA

    Wednesday, September 24, 2014 10:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Satish, but I still consider this to be a work around. It is much better than the ones I listed. 

    The Primary and secondary server use the same file structure.  

    I've setup other log shipping scenarios and have never had to specify the data & log location.   I'm sure there is something that is misconfigured.

    Thanks for the help!!!


    Thursday, September 25, 2014 1:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    So I have hit this bug (and it's a bugj) also.  No matter what paths specify in the restore options it still puts both the .mdf & .ldf in the same folder (the database file location).

    C.J. Morgan

    Tuesday, August 25, 2020 4:56 PM