SQL Server World

I am doing log shipping since SQL Server 2000. I did it with SQL Server 2005. But for the 4 years that I worked with SQL Server 2008 R2, I did not work with Log shipping. Nothing has changed in SQL server 2012. But to refresh my memory, here I am writing down simple steps for setting up log shipping.

1. Make sure database is in Full recovery mode. In this case primary server is PriSQL\Prod01 and primary database is PriDB.
2. Pre-initialize the database at secondary. It means take the full backup of PriDB and a transaction log backup of primary database SecDB and restore on secondary SecSQL\DR01 in Standby mode.
3. Right click the primary database, go to properties and select “Enable this as a primary database in a log shipping configuration”.
4. Select how frequently you are going to do log backups in “Backup Settings” button. Also provide the path where log backup goes. Some other settings on this page are set as follows:
   1. Delete files older than: 72 hours
   2. Alert if no backup occurs within: 2 hours
   3. Backup schedule: Every 15 minutes
   4. Backup Path: E:\SQLBackups\PriDB_log
   5. Job name: LSBackup_PriDB
   6. Backup compression: Use default server setting.
5. Add the secondary server. In this case it is SecSQL\DR01. Secondary database is SecDB.
   1. Tab- Initialize secondary Database:
      • Select database is already initialized.
   2. Tab- Copy files:
      • Destination folder for copied file F:\SQLBackups\LogShip
      • Set 72 hours for Delete copied files.
      • Copy job: LSCopy_PriSQL\prod01_PriDB
      • Copy Schedule: Every 15 minutes.
   3. Tab- Restore Transaction Logs
      • Restore job: LSRestore_PriSQL\prod01_PriDB
      • Restore Schedule: Every 15 minutes
      • Delay restoring: 1 minute
      • Alert if no restore occurs: 2 hours