1

I currently have a number of SQL Server (2008 R2) databases that are receiving tlog and differential backups.

The backups are currently being stored on a NAS that is going to be repurposed. I need to start sending the backups to a new NAS that has a different path from the current NAS.

What I would like to do is forklift all of the current files from the current NAS to the new NAS, and reconfigure the current backup jobs to begin saving to the new NAS.

I know this won't break the restore chain, but I'm concerned this move will break SQL Server's awareness of where to find backup files in the backup chain. (It will try to find old backup files in in the old NAS location, not the new NAS location.)

Can this be accomplished by updating the records in the backupmediafamily table alone, or is there more to it than this?

2 Answers 2

4

If your retention period for backups is 1 month or something relatively limited, I would suggest the following approach.

  1. Retain the OLD NAS until the expiry of your retention period for those backups.
  2. Create your directory structure on the NEW NAS and redirect your backups there.
  3. Once your retention period on the OLD NAS expires, then drop use of the OLD NAS.

As you delete old backups the SQL Server will still have the OLD NAS path on the backups that went there. So it will progressively delete those files without any further action from you.

I generally recommend against updating system tables.

When we have to move files, we just move them and cope with an extra script to handle the moved files until they are purged. If you have a week or two on NEW NAS the likelihood of needing the older backups is small.

3
  • The backup cycle is just two weeks, so I think I'll go in the direction you are suggesting and just manually handle the moved files until they are purged. We have tlogs set to a fairly high frequency, so a manual script will be a PITA. Here's to hoping we don't need to do any restores in the next two weeks! Thanks.
    – Joe
    Sep 19, 2014 at 19:40
  • @Joe you could also consider copying all backups to both locations (perhaps with a background job, or with MIRROR TO) while you're in transition. Or taking full backups more frequently during this period (say, every night), such that the log backups even from yesterday become far less relevant. Sep 19, 2014 at 20:01
  • @AaronBertrand, I like that idea. Thank you for sharing.
    – Joe
    Sep 19, 2014 at 20:06
0

but I'm concerned this move will break SQL Server's awareness of where to find backup files in the backup chain. (It will try to find old backup files in in the old NAS location, not the new NAS location.)

You need to first update your script to point to new location. Then you need to make sure SQL Server service account has read write permission on the new NAS where you would be taking backup. You also need to make sure that SQL Server can see the NAS otherwise if there is no connectivity you would not be able to backup there. A simple test can be done, create a test database on SQL Server and try taking backup using TSQL command, you were using to backup on previous NAS, if you succeed well and good if not resolve the error.

2
  • 2
    You can add rows to the backupmediafamily table via RESTORE VERIFYONLY FROM backup_device WITH LOADHISTORY and remove outdated ones with sp_delete_backuphistory Sep 19, 2014 at 19:39
  • Yes correct..edited the answer
    – Shanky
    Sep 19, 2014 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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