Log Shipping in Simple Steps
Log Shipping was introduced in SQL Server 2000 , The main advantage of this is maintain multiple servers as standby server,robust ,we can decide the time interval.
Requirement for Implementing Log Shipping
Both the All the SQL Server Services should be started with the same domain User Name,if individual server then Start them with Same User Name and Password.Folder level Sharing should be enabled between the Server, SQL Server Agent Services should be started . SQL Server Express Edition is not Possible For Implementing Log Shipping because SQL Server Agent Services are not available.User must be Sysadmin to configure Log Shipping
Internally when Log Shipping is configured,there are 4 jobs create between Primary Server and Secondary Server, they are Backup Job,Copy Job,Restore Job Alert Job
This job is created on Primary Server,this job takes the transaction log backup of the Database on a scheduled time
This job is created on Secondary Server,this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.
This job is created on Secondary Server,this job restored the copied transaction log backup on the Secondary Server.
This job is created on Primary Server and Secondary Server.and gives the alerts as email, when there is a delay in backup ,copy,restore jobs.
Log shipping has disadvantages too ,network usage consumption.
Take a Full Backup and a transaction log Backup from the Principle server and Restore it on the Standby by Server with No recovery mode or Standby Mode.
SQL Query command to take backup
1. Use Master
3. Backup Database MyDatabase to Disk = ‘D:\MyDatabase_Full.bak’
4. Backup log MyDatabase to Disk = ‘D:\MyDatabase_Tran.trn’
Go to the Primary Server right click on the database ->Task_> Ship Transactin Logs..
Click in “Enable this as a primary Database in a Log Shipping configuration”
Give the network path and the Physical path of the shared folder of the database, to know the IP of your server run ipconfig /all command in the command prompt. give the backup time schedule,alert schedule, if using SQL Server 2008 you can even select the Compress Backup Option.Click on schedule and the windows opens as below
Click on the add Secondary Server button Option. connect to the Secondary server .As we have Restored a Full Backup and a transaction log backup. so leave the first tab.
click on Copy Files tab,give the physical path of the folder in Secondary Server.and set the schedule for restore Transaction log too.
Now Lo Shipping has been successfully configured ,to monitor the log shipping you can use
Step7 : To Recovery Log shipping database.
1 Use Master
3 Restore database DBname with Recovery
Now, log shipping database is ready to use.
Thanks to the post: http://sqlservr.blog.com/2012/05/27/log-shipping-in-simple-steps/