Introduction
SQL Server Log Shipping allow to transfer data from one database to a lot of databases. So in Log Shipping we have 3 servers
- Primary
- Secondary
- Monitor (Optional)
*The secondary database can be more than one database.
Log Shipping involves copying a database backup and subsequent transaction log backups from the primary server and restoring the database and transaction log backups on one or more secondary servers
Steps
Create a folder in primary server (LogShipping_Primary) and also in secondary server (LogShipping_Secondary).This two folders must be sharing.
Take full and transaction log backup from the database in the primary Server
Restore this database on secondary server with stand by option.
On primary server right click >> properties >> Transaction Log Shipping >> Enable Log Shipping
Configure the backup setting for the transactional log backups
- Network backup path folder: the folder name LogShipping_Primary in primary server put the network path for this folder \\Servername\Foldername
- Local path: the local path for the folder in primary server like C:\users\DBA\Desktop\LogShipping_Primary
Configure Scheduled job to backup database
Add Secondary Server
Connect to select your secondary server
Initialize Secondary Database
- If you want to take full backup and restore it to the secondary server.
- If you want to restore the existing backup from primary server to secondary server.
- if you have manually restored the database on secondary server.
Copy Files
In this table you will put the path if the folder in the secondary server \\Servername\\Foldername
Configure the schedule to take the backup files copy from the primary server to secondary server
Restore transactional log : we will select Standby option and Disconnect all user in restore option
Configure the schedule to restore the backup taken from the primary server
Click OK
Optional (Check on monitor sever instance to monitor the backup status and the job status for the jobs in primary server and secondary server)
Last step in Log Shipping Configuration
After you finish from the Log Shipping go to the Primary Server to Check the new jobs
Return to the secondary server to check the new jobs
Note: Check the functionality of Log Shipping by executing all the SQL Jobs which have been scheduled during log shipping. All the jobs should execute successfully.
