300+ TOP Log Shipping Interview Questions [LATEST]

1. Which Edition Support Log Shipping?

• SQL Server 2005 – Enterprise, Standard, and Workgroup
• SQL Server 2008 – Enterprise, Standard, Web, and Workgroup
• SQL Server 2008 R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
• SQL Server 2012 – Enterprise, Business Intelligence, Standard, and Web

2. Who Can Setup Log Shipping?

Members of Sysadmin fixed server role

3. Required Recovery Models In Log Shopping?

Full or Bulk logged Recovery Model

4. Which Jobs Are Created In Log Shipping?

On Primary server
– Backup job

On Secondary Sever –
Copy, Restore and Alert Jobs

5. Whether Log Shipping Supports Automatic Failover?

No, it support only manual failover.

6. Login Can Be Transferred Through Manual Failover?

No, you have to create logins and users on secondary server.

7. Types Of Secondary Server?

Restoring Mode and Stand by mode

8. Difference Between Restoring Mode And Stand By Mode In Log Shopping?

Restoring mode –
Database is in restoring mode.  You can not access the database.

Stand by mode –
Database is in read-only node.  You can select the data from the database.  This database can be used for reporting purpose.

9. What Are The Benefits Of Log Shipping?

• Easy to setup/configure
• Easy to maintain
• Easy to failover
• Can have multiple secondary servers
• Can be configure with mirroring or replication.

10. What Are The Drawbacks Of Log Shipping ?

• Need to manage each database separately
• Does not support automatic failover.
• Logins can not be moved automatically

11. Which System Database Store Log Shipping Related Information?

Msdb

12. What Is The Difference Between Log Shipping 2000 And 2005?

Log shipping 2005 uses link Server to communicate between Primary, Monitor, and Secondary Servers.

For 2000, Check connectivity of Primary, Monitor, and Secondary Servers.

Please note, always check remote connections are enabled on all servers.

13. How To Monitor Log Shipping?

• Reports-
  Transaction log shipping standard report
• 3rd party tool
• SQL Server System tables and stored procedures

14. Default Time Of Log Shopping ?

15 minutes

15. Full Form Of Tuf?

Transaction Undo file

16. List Of Tables Used In Log Shipping?

Primary server Tables:

Log_shipping_primary_databases, Log_shipping_monitor_primary, log_shipping_monitor_error_detail, log_shipping_monitor_alert

Secondary server Tables:

Log_shipping_secondary_databases, Log_shipping_monitor_secondary, Log_shipping_monitor_error_detail, Log_shipping_monitor_alert, Log_shipping_monitor_history_detail, Log_shipping_secondary

17. Backup Can Be Compressed In Log Shipping?

Yes, SQL server 2008 onwards

18. How To Troubleshoot Log Shipping Issues?

• Check error log for issue
• Check backup, copy, and restore job history.
• Check Log shipping standard report