SQL Server Backup and Restore

Question

Hi All,

Currently we are taking two backups for the same DB - one from sql agent (custom script without copy_only option) and another one from third party backup tool (without copy_only). The backup strategy is same for both. ..

Full backup: every day night (12 AM - SQL agent, 3:30 AM - third party backup tool)

Log backup: every one hr (start from 00:00 for SQL agent, start from 00:30 for third party backup tool)

DB size: 750GB

My question: Will log shipping or point in time recovery work if we use either any one of the backups (ex: apply only SQL agent full backup, log backups).....

Answer 1

Yes, you can take full backups from two different places without COPY_ONLY as long as you are not taking diff backups as well.

But the log backups must only be taken by one source. Else you will have a terrible mess when restoring.

Also keep in mind, just taking backups is not enough. You also test doing a RESTORE, so that 1) you know that your backups are good. 2) you are familiar with the procedure.

Answer 2

yes you can but I suggest you to take transaction backups at less periods like 15 minutes (in accordance with log backup duration)

Answer 3

Hi @PraveenKumar,

Thank you for reaching out and welcome to Microsoft Q&A.

My question: Will log shipping or point in time recovery work if we use either any one of the backups (ex: apply only SQL agent full backup, log backups).....

Of course you can!

You can take a look at Erland and yunus's suggestions. Their methods are correct and very effective.

Feel free to share your issues here if you have any confusion.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 4

No, log shipping or point-in-time recovery will not work correctly if you use only one set of backups (e.g., apply only SQL Agent full backup and log backups) while another set of backups (e.g., from a third-party tool) is being taken without the COPY_ONLY option. This is because each backup method without COPY_ONLY affects the Log Sequence Numbers (LSNs), disrupting the backup chain required for both log shipping and point-in-time recovery. For a reliable backup strategy, use a single backup solution consistently or ensure the secondary backups use the COPY_ONLY option to avoid interfering with the LSN sequence.

Using two different backup methods (SQL Agent and a third-party backup tool) without the COPY_ONLY option can lead to complications with log shipping and point-in-time recovery. Here’s why:

Backup Chain

When you take a backup using a method without the COPY_ONLY option, it becomes part of the database's backup chain. SQL Server maintains a sequence of log backups that are essential for point-in-time recovery. Introducing a second backup method that is not synchronized with the first can break this sequence.

Log Sequence Numbers (LSNs)

Each log backup has a unique Log Sequence Number (LSN). The SQL Server uses these LSNs to keep track of the backup chain. When you take a log backup using one tool, it changes the LSN sequence, which the other tool does not account for. This results in each tool's log backups not being able to follow the LSN sequence correctly, causing issues with restoring the database to a point in time.

Implications for Log Shipping and Point-in-Time Recovery

Log Shipping

Log shipping relies on a consistent chain of log backups. If you use only the SQL Agent backups for log shipping:

1. Full Backup: You will need to restore the full backup taken at 12:00 AM.
2. Log Backups: You can restore the log backups taken every hour starting from 12:00 AM by the SQL Agent.

However, if the third-party tool takes a log backup at 12:30 AM, it will alter the LSN sequence, breaking the chain. This would mean any log backup taken by the SQL Agent after 12:30 AM would be invalid for log shipping.

Point-in-Time Recovery

Point-in-time recovery also relies on a consistent chain of log backups. If you attempt to use only the SQL Agent backups:

1. Full Backup: Restore the full backup taken at 12:00 AM.
2. Log Backups: Apply the log backups taken every hour starting from 12:00 AM by the SQL Agent.

As with log shipping, if the third-party tool takes log backups, they will alter the LSN sequence, making it impossible to apply subsequent SQL Agent log backups for point-in-time recovery.

Recommendations

To ensure log shipping and point-in-time recovery work correctly, you should:

1. Use a Single Backup Solution: Choose either the SQL Agent or the third-party backup tool for all your backups. This ensures that the LSN sequence remains consistent.
2. Use COPY_ONLY for the Second Backup: If you must use both methods for redundancy, configure the second backup to use the COPY_ONLY option. This will prevent it from altering the LSN sequence, but note that these backups won't be part of the regular backup chain and can’t be used for log shipping or point-in-time recovery.

Log shipping and point-in-time recovery will only work correctly if you use a consistent set of backups from one method. Mixing backups from different methods without using COPY_ONLY will break the LSN sequence, making it impossible to achieve reliable log shipping or point-in-time recovery.