Question :
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?
Answer :
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.
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.