As mentioned in the comments, backing up to an existing file name will, by default, append the latest backup to that file. For full and differential backups, at least, you don't want to do that.
For your purposes:
- Once you have a new full backup, you don't need previous full, differential, or transaction log backups.
- Once you have a new differential backup, you don't need previous differential backups or transaction log backups. Each differential backup contains all changes since the last full backup.
- Once you have a new transaction log backup, you still need previous transaction log backups. Transaction log backups record new log entries since the last transaction log was taken.
Note that, for maximum safety, you should keep your previous full and differential backups until a new one is finished. If you overwrite your previous full backup with the new one, then as soon as SQL Server starts writing your full backup until it finishes you have no backup. With the full backup they are based on gone, differential and transaction log backups are useless.
If you really want to take that risk: To write to the same backup file, overwriting any existing backups in it, use the following options:
WITH INIT, SKIP, FORMAT
My recommendation: Just as noted by sp_BlitzErik in the comments, use Ola Hallengren's free maintenance scripts, as they will do all the "heavy lifting" for you.
Just to cover the option, depending on the edition of SQL Server you're using, keep in mind that you probably have the option to set up SQL Server maintenance plans to do these backups. Those include a task that will remove files older that however much time you specify. However, I'm assuming that you haven't mentioned those because you either don't have access to use those, or don't want to use them.
If both of those options are out, but you can write a script (in the language of your choice) to delete files more than 2 days old, and can hold up to two days' worth of your backup files on disk:
Set up your jobs as planned, with one change: append the type of backup and the current date and time to the file names. For example:
BACKUP DATABASE [MyDB]
TO DISK = 'F:\MSSQLSERVER\BU\MyDB_FULL_'
+ REPLACE(REPLACE(REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-',''),':',''),' ','_')
This would create a full backup file named
F:\MSSQLSERVER\BU\MyDB_FULL_20170928_191216.bak in your chosen directory (if run today, at 7:12:16 PM).
I would use
MyDB_DIFF_ as the prefix for differential backups, and
MyDB_TRAN_ for transaction logs. Note that full and differential backups should be have the extension
.bak, while transaction log backups would have the extension
Then, set up a "clean-up" job to run right before you run your full backup. This will run your script that deletes any files in the
F:\MSSQLSERVER\BU\ folder that are more than 48 hours old. This should ensure that you have at least one full backup, and the differential and t-log backups that depend on it, even if the script was accidentally run at noon instead of midnight.
By running it right before you run your full backup, you also ensure that you have the maximum available space for that full backup.
You can always choose to be fancier with the "clean-up" script; since each of the three file types starts with the same prefix, you could delete differential and transaction log backups more than (say) 3 hours old, and still have a good cushion.
Unless you already have a script that would do the deletions for you around to use, I again highly recommend you use Ola Hallengren's scripts. Keep in mind that, unlike whatever you could whip up on your own, they've already been tested by a lot of people the world over.