SQL Server 2008 R2 – How to Back Up Chain LSN

backupmaintenance-plansnetbackupsql-server-2008-r2

If I move my network back up to local then will it break the back up chain ?

My network drive will be unavailable for 30 minutes and there is no specific time. My logs are taken every 6 hrs and full back ups at 12 am. Back up location- network only. I was thinking of changing the t logs every 30 minutes to offset any full back up failure and keep the location to network as i might not have the space locally. Any thought? Thanks

Best Answer

If you're using third-party software to do the backups, then the answer may vary.

In general, if you're using SQL Server jobs to run the backups, the job will fail if the location where the backups are written isn't available. The job will run on its normal schedule, and each scheduled run will fail or succeed as appropriate.

So, if:

  • Your full backup is scheduled to run at 12AM
  • You've got transaction log backups running every 15 minutes, starting at 12:10 AM.
  • Your server cannot access the location where the backups are written from 12 AM until 12:41 AM

Then: - Your full backup will fail; - Your 12:10, 12:25, and 12:40 transaction log backups will fail; - Your 12:55 (and following) transaction log backups will succeed (presumably).

The 12:55 transaction backup will be bigger than usual, as it will include any changes wince the last successful transaction log backup (presumably at 11:55 PM).

Your backup change from the previous day's full backup will be intact. As long as you keep that full backup and all the transaction log backups since, you would be able to restore your database to any point in time since that last successful full backup.

If your maintenance plans include a step to delete full and transaction log backups that are more than 24 hours old, then you'd have an issue, because the full backup you'd have to start from (yesterday's) might be deleted, as might some of the transaction log backups (from, for instance, 12:55 and earlier yesterday morning), and that would leave you with a broken log chain.

I would generally allow for at least two full backups and their associated transaction logs before anything gets auto-deleted, so if one full backup fails, you've still got one good one to work from.

You could also consider modifying the full backup's schedule, adding (for example) up to three retries, each an hour apart.

If the backup destination is unavailable at 12AM, then it would try to do the backup again at 1AM; if still unavailable, then again at 2AM and 3AM. If all 4 attempts failed, then the job would fail; otherwise, the job would succeed.

Note that this may cause issues with other jobs, or with backups from other servers in your network, since you're normally not running this backup at the time it would wind up running.

Related Question