Log Shipping for SQL Server
Log Shipping is a basic level SQL Server high-availability technology
that is part of SQL Server. It is an automated backup/restore process
that allows you to create another copy of your database for failover.
Step 1: Check the Status of databases
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'CMT'
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
Step 2: Enable and start the SQL Server Agent Service
Step 3: Open SQL server management studio
On the primary server, right click on the database name in SSMS
and select Properties.
Then select the Transaction Log Shipping Page.
Check the “Enable this as primary database in a log shipping configuration” check box.
Step 4: configure and schedule a transaction log backup. Click on Backup Settings
Creating backups on a network share enter the network path or for the local machine you can specify the local folder path.
Step 5: we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database
Step 6: Go to Secondary Database Setting:
Initialize secondary Database:
create the data on the secondary server.
You have three options:
create a backup and restore it.
use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.
specify the destination folder where it copied
Restore Transaction Log Tab
1. Standby Mode ( readonly mode used for reporting purpose)
2. No recovery mode
specify the database restoring state information and restore schedule.
This will create the restore job on the secondary server.
Note: You can also add Monitor Server