CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rajesh
Rajesh

Reputation: 7876

Consequences of a full DB Backup on LSN - SQL Server

What are the consequences of a full DB Backup on the LSN?

Will it break the LogShipping that is already configured and running when a full DB backup is complete (if it changes the LSN).

Also what is the best way to perform a DB resilience.

  1. Just have LogShipping configured
  2. Have LogShipping configured (15 min interval) with weekly full backups?
  3. Have a incremental backup every 15 mins and then a weekly full backup

Are there any great tools that would simplify the above process?

Upvotes: 1

Views: 1105

Answers (2)

Tom Stickel
Tom Stickel

Reputation: 20401

LiteSpeed works fantastic for your full backups, I would run a full backup daily and NOT weekly. Log shipping with or without Litespeed then to be done every 15 minutes is very typical. If you wait too long then Data from your primary to your secondary is too out of sync and you end up with errors based on what SQL Server has.

I always loved Red Gate products, but I have not used their log shipping / backup tools , only their data compare and their schema compare etc..

I believe Quest makes LiteSpeed and Quest I think is owned by Dell.

Currently we are stuck having to monitor the log shipping and it is not full proof. I would prefer Mirroring or Replication.

Upvotes: 0

Jon Boulineau
Jon Boulineau

Reputation: 451

Full backups have no effect on the log chain except for the first full backup taken after database creation or after a change to the FULL recovery model. It will not break log shipping. What could break log shipping is if you took a manual log backup and did not apply that to your secondary database.

The best recovery strategy for your database depends completely upon your situation. How much data loss is acceptable? How long of an outage can you reasonably allow for a restore to occur? It's not possible for someone on an online forum to provide an answer that you should rely on for your recovery strategy. You need to research backup and recovery very carefully, understand it inside and out, then apply the strategy that is best for your particular situation. There are many tools out there for backup from companies such as Red-Gate, Idera, Dell, etc.

That being said, just having log shipping is absolutely not good enough.

Also, @Anup ... COPYONLY only prevents the differential bitmap from being reset. It does not impact log backups.

Upvotes: 2

Related Questions