SQL Server Backup Files – Safe to Delete?

backupsql serversql server 2014

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?

Best 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.

Related Solutions

Sql-server – Restore System Databases from MDF/LOG files

msdb and model you may be able to recover data from by following the same process as you would if moving them. Use a test/dev instance for a dry run.

Stop SQL
Start with trace flag 3608 (master only recovery mode) NET START MSSQLSERVER /f /T3608
ALTER DATABASE msdb|model MODIFY FILE( NAME=xyz, FILENAME='filepath')
Stop SQL, move files in to place, restart SQL

KB914171 details one possible error that may be reported and a fix/workaround by:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go

Sql-server – Backup Disappearing

Ok, so it turns out I didn't know that I couldn't roll back to an earlier version of my newly created database until a full backup was made. This is why I couldn't see any options to roll back to before midnight in the restore dialogue.

Best Answer

Related Solutions

Sql-server – Restore System Databases from MDF/LOG files

Sql-server – Backup Disappearing

Related Question