SQL Server log shipping is one of the DR  solutions which involves two or more SQL Server instances; the source server is called the Primary Server and the destination is called Secondary server(s). Log shipping involves the transferring of the transaction log file from the primary server to the secondary server. Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. This is automated by three SQL jobs

Advantages are:

• SQL Server log shipping is primarily used as a disaster recovery solution
• It’s reliable and tested in details
• It’s easy to set up and maintain
• Log shipping can be combined with other disaster recovery options such as Always On Availability Groups, database mirroring, and database replication
• Low cost in human and server resources

Disadvantages are:

• Need to manage all the databases separately
• There isn’t possibility for an automatic failover; must be manual
• And secondary database isn’t fully readable while the restore process is running

setup

its advised to create Ad service account for log shipping

i will create user (log) add the user to domain administrators group

open ssms and got to security then login right click on login and select new login

give the user the below roles

premission for sql server service account must have sysadmin premission

go to sql configuration manager’

below services must have services account with domian admin right

strating log shipping

create a database

create a table

create a shared drive in other server

now go to server one to start log shipping

enable primiray database

the transaction backup will accoure by defult every 15 min but you can changed it
as showing below

click backup setting

put the network path for backup to be dumpted

next we need to add secendary instance

in the below is asking location go to db2
and copy path you want to store loges
create folder in db2 and shared on the network copy the network path and put below

resotre transaction log tab

you have two option

• List itemno recovery mode : the datae on secendray mode will not be accseable and you will not be able to qurey on secerndary modem
• standy mode allow you to qurey on secendray mode

i will select standbay mode

go to db2

you will se the database ahmed we created but in read only mode

if you go to jobs you will find jobs

i have created new table i will start the job manualy

go to serverdb2

and start job copy then restore

manual failover

When managing backups and restores in a Disaster Recovery (DR) scenario, particularly in SQL Server environments, it’s crucial to ensure that the transaction log sequence numbers (LSNs) are correctly aligned. Here’s a rewritten process for managing backups and restores, focusing on LSN matching, job execution, and database restoration:

1. Verify LSN Match in Backup and DR Reports:

First, check the backup reports to confirm that the Last Log Backup LSN matches the LSN on the DR site. This ensures that the transaction logs you’re about to apply to the DR database are in sequence and no log data is missing.

2. Run the Backup Job:

Execute the backup job to create a fresh backup. This could include both full and transaction log backups, depending on your backup strategy.

3. Disable the Backup Job:

Once the backup job completes successfully, disable it temporarily. This is usually done to prevent further backups from interfering with the restoration process you’re about to perform.

4. Copy and Restore Job Execution:

Next, copy the backup files to the DR site and execute the restore job there. Ensure that the restore operation is using the most recent backups.

5. Disable the Restore Job:

After successfully restoring the backups at the DR site, disable the restore job. This step is to prevent the job from running again unintentionally.

6. Restore the DR Database and Bring Online:

Finally, restore the DR database using the copied backups and bring the database online. you can use the below command to bring database from recovery mode to online .
restore database [database name] with recovery

Additional Notes:

Transaction Log Management: Ensure that transaction logs are being managed correctly, especially if you’re using Full or Bulk-Logged recovery models.
Monitoring and Validation: Continuously monitor the restore process for any issues. Validate the integrity and consistency of the restored database.
Documentation and Automation: Keep this process well-documented, and consider automating steps where feasible to reduce the risk of human error.
LSN Consistency: Pay special attention to the LSNs during this process. They are crucial for ensuring data consistency and a successful restore process.
This process outlines a methodical approach to managing backups and restores in a DR scenario, with a particular focus on ensuring LSN consistency and minimizing downtime.

monitor log shipping

detail note about log shipping monitoring
to monitor first type is through log shipping reports
go to db2
and follow the below

most important information is to look at the last restored logs what is last file copy and what is last log restored

on primary server the report will show only the last file backup

this is useful when there is issue with sync

you can use the below query for to get report to

Use master
Go
sp_help_log_shipping_monitor

also you can use the below

Use master
Go
sp_help_log_shipping_monitor_primary
@primary_server =  'AHMEDDB1', --<<  PROVIDE THE PARAMETER SERVER NAME
@primary_database = 'AHMEDDB2'    --<<  PROVIDE THE PARAMETER DATABASE NAME

below qurey will show jobs logs

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]

below are qureys you can use

Stored procedures (Run on Primary server)

--EXECUTE ON PRIMARY

Use master
Go
sp_help_log_shipping_monitor

Use master
Go
sp_help_log_shipping_monitor_primary
@primary_server =  'server1', --<<  PROVIDE THE PARAMETER SERVER NAME
@primary_database = 'test'    --<<  PROVIDE THE PARAMETER DATABASE NAME


SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'


EXEC xp_readerrorlog 0,1,"Backup", Null


Stored procedures (Run on Secondary server)


--run on secondary server

Use master
Go
sp_help_log_shipping_monitor

Use master
Go
sp_help_log_shipping_monitor_secondary
@secondary_server =  'server2',
@secondary_database =  'test'


SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'

EXEC xp_readerrorlog 0,1,"Restore",Null




Tables in MSDB database

SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6

SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'

SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'





SQL Server Error Log


Select * from sys.sysmessages 
Where description like '%shipping%' 


--Can execute on both servers

EXEC xp_readerrorlog 0,1,"Error",Null

EXEC xp_readerrorlog 0,1,"Shipping",Null

-- execute on Primary server

EXEC xp_readerrorlog 0,1,"Backup",Null

-- execute on secondary servers

EXEC xp_readerrorlog 0,1,"Restore",Null