Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Steps to Move SQL Server Log Shipping Secondary Database Files

MSSQLTips author Jugal Shah By:   |   Read Comments (2)   |   Related Tips: More > Log Shipping
Problem

With SQL Server is it possible to move the secondary database involved with Log Shipping to a different drive without disturbing the Log Shipping configuration?  If so, what are the steps to accomplish this task?  Check out this tip to learn more.

Solution

There are scenarios where you have to move the Log Shipping secondary database to different drive due to disk space issue or to do the maintenance of the drive. In this tip I will show you how we can move the Log Shipping secondary database without disturbing/reconfiguring SQL Server Log Shipping.

To configure the Log Shipping please check Step by Step SQL Server Log-Shipping Setup

Before we start moving secondary database files, let's take a look at the secondary database restore mode. In Log Shipping the secondary database can be configured for Standby or NoRecovery mode.  Here is a brief explanation:

  • Standby Mode: In Standby mode a user can execute SELECT commands and the transaction logs restores need to be coordinated with the SELECT commands.
  • NoRecovery Mode: In NoRecovery mode secondary database status is always in restoring state and users cannot issue SELECT commands.

You can execute the below query on the Log Shipping Secondary SQL Server to determine the restore mode information:

--Execute the below script on the secondary server
declare @databaseName varchar(300)
set @databaseName = 'myLogShip' -- Secondary Database Name
-- 0 = Restore log with NORECOVERY.
-- 1 = Restore log with STANDBY.
select secondary_database,
case restore_mode 
when 0 then 'No Recovery'
when 1 then 'Stand by' end AS 'restore_mode'
from msdb.dbo.log_shipping_secondary_databases 
where secondary_database = @databaseName

To check the current location of the secondary database files location, execute the below query on the Secondary SQL Server.

--Execute the below script on the secondary server
declare @databaseName varchar(300)
set @databaseName = 'myLogShip' -- Secondary Database Name
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(@databaseName);

To move the database to different drive, we will use the ALTER DDL command.

ALTER DATABASE database_name 
MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

To move the secondary database files, your secondary database restore mode must be in No Recovery state. If the database state is Standby Mode, your ALTER statement to move the database files will fail with the below error.

Msg 5004, Level 16, State 4, Line 1
To use ALTER DATABASE, the database must be in a writable state 
in which a checkpoint can be executed.

To avoid the above situation you need to change the database restoring mode to NoRecovery as shown below as Step 1.  If the secondary database is already running No Recovery mode than you can ignore this step.

Step 1: In SQL Server Management Studio, right click on the primary database -> select Properties -> click on the Transaction Log Shipping page -> click on the Secondary server instances and database configuration ellipse (...) as shown below and it will open the Secondary Database Setting dialog box which is the second image below.

Transaction Log Shipping page

Click on the Restore Transaction Log tab and select No recovery mode radio button in Secondary Database Setting dialog box as shown below. Database recovery mode will change to No recovery mode on next transaction log restore.

Secondary DataBase Settings

Step 2: Once the secondary database state is changed to No Recovery Mode, disable the Log Shipping Restore Job on secondary server. To disable the job in SQL Server Management Studio, navigate to root | SQL Server Agent | Jobs | Log Shipping Restore Job then right click on the job and click the Disable option as shown below.

Disable LS Restore Job

Step 3: On the Secondary Log Shipping SQL Server, execute the ALTER database command as shown below to identify the new location of the secondary database and log file.

-- Execute the below script on secondary server 
-- Specify the secondary database, file name and new location
ALTER DATABASE myLogShip 
MODIFY FILE ( NAME = myLogShip, FILENAME = 'D:\SQLMonitor\myLogShip.mdf' );
ALTER DATABASE myLogShip 
MODIFY FILE ( NAME = myLogShip_Log, FILENAME = 'D:\SQLMonitor\myLogShip_Log.ldf' );

Step 4: Stop the Secondary SQL Server instance services. Go to SQL Server Configuration Manager and stop the secondary instance services.

Step 5: Move the Log Shipping Secondary database files to the new location in Windows Explorer as mentioned in step 3.

Step 6: Restart the secondary instance SQL Services in SQL Server Configuration Manager.

Step 7: Enable the Log Shipping database restore SQL Server Agent Job on the Secondary SQL Server see step 2 as a point of reference.

Step 8: Verify the log shipping SQL Server Agent jobs are working properly.

Next Steps
  • Document your SQL Server Log Shipping configuration for all servers.
  • Monitor your storage utilization and validate there is enough storage on the target location to move the SQL Server database.
  • Check out these related tips:


Last Update: 12/28/2012


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, January 09, 2013 - 6:40:30 AM - Karthick Read The Tip

This is very useful topic. thanks a lot.


Wednesday, February 20, 2013 - 12:40:28 PM - PAPA SARR Read The Tip

Thank you for your post.  It is very useful.

However, I have another question related to log shipping.  I am trying to add another secondary database.  Would you know the steps to follow?  I believe I will need a full backup of the primary database, and I don't know if it will break the LSN of the transaction log.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.