1

I've got a back up process which initially writes to a local disk, then the disk gets backed up remotely. After each back up (log, diff and full) I'd like to delete all the files on the local disk. Will SQL Server notice this?

Does MS SQL read the bak files to know what has been backed up and what hasn't for example?

I've got a database on AWS with a disk used only for backups. After every backup a snapshot is taken of the disk (via an AWS CLI script). So now I have the data on the disk and in the snapshot. The disk costs $200+ dollars a month.

I want to change our back up process to create the disk, do the back up, take the snap shot and then drop the disk. Will SQL Server get upset if I mess with it's log files in this way?

1

I can't speak to 'via an AWS CLI script' specifically. But SQL does not care where the backups are. It knows they got taken and where they were written to.

Assuming you are using a system wide backup product like TSM to backup your SQL backups as well as the rest of the machine:

Don't delete the fulls and diffs, overwrite them.

If you run your full and/or differential backups at 6AM and you backup them up remotely at noon. You can safely overwrite them at 6AM the next day.

You can delete the local t-logs when they are at the remote location. You might want to leave 24-48 hours of them locally for "accident" (someone did something stupid) recovery.

It is important that ensure that your SQL backups are completed before the the remote (i.e. TSM) backup process runs. You create SQL backups, because products like TSM will not backup files in use, like your mdf & ldf files. It will also not backup, your SQL backups if they are being written to during the off site backup. I ran a 7 week experiment appending backup files, and TSM did not move the backups of site until the experiment ended.

Off site backups, It looks like my offsite backup solution (IBM Spectrum) is not backing up the trn file. I suspect this is because the file is constantly being edited. Source

Besure to regularly validate your backup solution with restores from your offsite location.

  • What's the logic/ reason for not deleting them? – Patrick Dec 2 at 19:26
  • If you're always overwriting the same files, even with copying them off to another location, how can you do a restore for an arbitrary point in the past? – alroc Dec 2 at 20:29
  • @alroc you restore the old copy to a sub folder and restore from there. – James Jenkins Dec 3 at 10:55
  • But how are you not overwriting the files on the off-site copy when you're using the same names all the time? I can't help but think that most of this would be moot if one uses Ola Hallengren's backup scripts – alroc Dec 3 at 13:04
  • 1
    I agree with your strategy in general, but I wouldn't recommend anyone put themselves into a position to prematurely destroy a good backup especially when mechanisms to copy backups remotely can also fail (because they do). Disagreeing with your statement isn't the same as saying your wrong, so that's why I didn't downvote you, but I think overwriting backups in general is a bad approach. Just personal preference based on what I've come across during my career as a DBA. – John Eisbrener Dec 3 at 17:37
1

After each back up (log, diff and full) I'd like to delete all the files on the local disk. Will SQL Server notice this?

Unlike other database solutions, such as Oracle, SQL Server does not natively have a way to cross-check if backup files still exist on disk or not. Backup file metadata is stored in the backup history tables in msdb where these tables are populated when backups are performed, so any changes you make after the backup commands complete (e.g. deleting the files from the OS, dropping the drive, etc.) won't be tracked here.

So to answer your question, so long as SQL Server can create the files, it doesn't care what you do with them after.

I want to change our back up process to create the disk, do the back up, take the snap shot and then drop the disk. Will SQL Server get upset if I mess with it's log files in this way?

Again, so long as you can create your backups to the drive (e.g. permissions are correct, the drive has enough space, etc. etc. etc.), SQL Server does not care what you do with the drive or files after the fact.

What I would caution you on though is will you care that those files aren't readily available in the event you need to restore the database? As my comment on the other question states, the primary job of a DBA is to always be able to recover a database. I would caution doing anything that prevents you from quickly recovering a database in the event of a failure.

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.