Share via

Log shipping for SQL Server

  • Article

The steps describe how to perform log shipping using GUI.

1. Assume machine on which log shipping is performed as primary.

    PRIMARY = //MACHINE-NAME/ (or) //IP-ADDRESS/

    PRIMARY = SECONDARY but it can also be different.

2. Create a database => AdventureWorks on PRIMARY

   use AdventureWorks

   create table employee ( name varchar(20), age int);

   insert into employee values ('variable', 20)

   select * from employee

3. Create database => Adv1 on Secondary.

   Logshipping has to be done from Adventureworks to Adv1.

   Note: Log shipping can only be applied at the database level.

4. Setting recovery model

 

 

 

5. Setting the transaction log shipping

 

6. Setting primary server setting

 

For testing purpose set the scheduled time or 1 min and  in case of actual scenario leave it to default 15 min.

7. Adding secondary server

 

8. Secondary server = Primary server in our scenario

9. Restoring in secondary

 

 

 

 

 

 

 

Once jobs are running properly we have to perform breaking of logshipping.

Again go to properties of the database in PRIMARY SERVER. In Transaction Log shipping uncheck the check box which states enable transaction log shipping. This will disconnect the server. Once disconnected, the new database on the secondary will show restoring.

For bringing Adv1 online use the following command:

RESTORE DATABASE [Adv1] WITH RECOVERY

 Note: To test log shipping - insert a record in AdventureWorks just before breaking log shipping step and wait till the jobs complete. Once the jobs complete and we break log shipping the changes will get reflected in Adv1.