0

My understanding of a SQL restore operation is that a full backup is required as well as all incremental backups performed since.

Likewise my understanding of Log Shipping is that it is using a SQL job to perform a transaction log backup which is then restored on another SQL instance.

Presumably any backups performed by an integrated solution (VDP, Networker, etc), are not aware of the backups performed by Log Shipping. This presumably results in a broken unrestorable chain. Is the conventional wisdom to revert to file level backups when using Log Shipping, or have I misunderstood something along the way?

Improve this question
6
  • Log shipping uses transaction logging and ships the transactions to the other server which has a database in standby with a scheduled job that runs and looks at those transactions and applies the ones that are not applied at that point which copied over from the primary DB server instance. As long as you are not doing something to break the log chain, all should be good using the other method of backup on the primary server in conjunction with log shipping. Typically you have to do a one time full restore on the secondary LS server and then start applying the logs from LS.
    – Vomit IT - Chunky Mess Style
    Mar 5, 2018 at 20:32
  • @PimpJuiceIT We're using SQL 2012, our incremental backups do truncate the database log. Presumably this is desired? Wouldn't our log grow indefinitely otherwise?
    – Derrick Moeller
    Mar 5, 2018 at 20:57
  • Read over my answer here: dba.stackexchange.com/questions/127297/… and see if this clarifies for you. If you have huge transactions that make the log file grow and grow then that LDF DB transaction file will not get the space used back unless you shrink it. Once the transactions are completed, the space within the LDF file is re-usable by other transactions. If you keep truncating the DB log then log shipping backups can be a nightmare otherwise you need to time a restore in standby of the secondary DB.
    – Vomit IT - Chunky Mess Style
    Mar 5, 2018 at 21:24
  • @PimpJuiceIT We're not actively shrinking the transaction log, it's a relatively fixed size. It is occasionally shrunk after maintenance operations, but not during "run normal". Let's say the log grows 1GB/hour, and is backed up every two hours. With the truncate I believe the log will use the same 2GB of disk space indefinitely, without the truncate I assume the log will grow every hour? At the moment we don't have a LS secondary, we're evaluating it as a solution against an AlwaysOn Availability Group as a reporting database, and I'm trying to understand LS' limitations.
    – Derrick Moeller
    Mar 5, 2018 at 21:40
  • I see.... there's nothing like trying man so if you have an opportunity to actually get it setup and tested to confirm which solution works best, I suggest just trying to see which works how or whatever for your specific needs and environment. If you complete transaction logs with a LS setup, the inactive transaction portion of the log file will be available for re-use at that point. If you setup for every 15 minute transaction dump, then every 15 minutes that will occur for example. I've never used AlwaysOn personally but LS works great for reporting and DR in my experience.
    – Vomit IT - Chunky Mess Style
    Mar 5, 2018 at 22:32

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .