1

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

  • Why are you concerned about your backup network being down for 30 minutes when you're only taking transaction log backups every 6 hours? – Scott Hodgin Oct 30 '17 at 15:13
  • I dont know when the network back up will be down but it will be between 12 am to 6 am and may or may not take more then 30 minutes so either my full or network back up will fail . Full back ups are taken at 12 am and t log every 6 hours. – SqlNovice Oct 30 '17 at 15:16
  • So for example my log back ups are scheduled from 12 am every 1 hr and the network storage is down till but it comes back at 12.15 am , will the back up starts automatically at 12.15 am or it will be failed and only start from 1 am and onwards ? – SqlNovice Oct 30 '17 at 15:19
  • 1
    Well - if you don't have access to the network backup location (and you want to ensure your FULL and TLOG backups run succesfully), then you will need to modify the target location for those backups. Changing the location of where the backups are stored won't break the 'backup chain'. As long as you can access the locations should you need to restore, you should be fine. – Scott Hodgin Oct 30 '17 at 15:19
1

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.

  • Thank you for the wonderful explanation...Appreciated... – SqlNovice Oct 30 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.