SQL Server Backup File Confusion – Log Shipping

I saw a post recently where a person working with SQL Server had log shipping setup. They were asking if they needed to run separate backups for their database in order to restore this server in the event that it failed. It took a few posts to understand this since it never occurred to me anyone would be confused about this. Maybe it’s because I remember when log shipping was something you had to build, not a wizard or feature of SQL Server.

There is nothing amazing about log shipping. Nothing special. The “feature” as it’s implemented in SQL Server 2008 is designed to automate and make the process of restoring log backups on another database easy.

Log shipping is a simple process:

  1. Take a full backup from DB1
  2. Restore the full backup to DB2 with norecovery or standby
  3. Take a log backup on DB1
  4. Restore the log backup on db2 with norecovery or standby
  5. Repeat steps 3 and 4

That’s it. That’s what is involved, and you can easily do this manually. You set script jobs to do this, or you can use the wizard in SQL Server 2008.  You can also set delays between steps 3 and 4 that give you the chance to recover data that might accidently be changed or deleted on the primary server.

However you enable it, the backups  (full and log) are normal SQL Server backups. You can use them to restore on the primary database, just as they’re used on the secondary database.

One last note, you can log ship to the same server instance if you like. You might do this to have a customer service or test instance on the same hardware. Or you might do it to keep a second, delayed copy of your development database to roll back changes.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog, Uncategorized and tagged . Bookmark the permalink.