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.
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.
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.
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:
