I have this maintenance plan with differential backup and maintenance clean-up task in it. The plan runs once daily, and the clean-up task is deleting backup files older than 1 week. There is no other full backup plan or subplan there in SSMS.
I ran the following script day before yesterday, yesterday and today to find out where is the base full backup for this differential backup.
SELECT TOP 1
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bmf.physical_device_name AS FullBackupLocation
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id
WHERE
bs.type = 'D' -- Differential backup type
AND bs.database_name = 'DbName1' -- Replace with your database name
ORDER BY
bs.backup_start_date DESC;
The result was of 29/07/2024, the start date, finish date and the file name. And the path was the same path in which this differential backup is taken daily.
D:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\DbName1_backup_2024_07_29_221353_1217431.bak1
Most strangely, this file was already deleted by the clean-up task on 05/08/2024. That's 3 days before I ran this scrip for the first time!
My questions are:
August 11, 2024 at 3:14 pm
1 NO
2 It isn't. Multiple types of file can be sent a single physical file in SQL Server backups.
3 Look for the last full ('D') backup immediately preceding the 'I' (diff) backup. You can also right-click on the database in SSMS and select "Properties" and you'll see the last backup date(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 12, 2024 at 6:32 am
1. Thank you for confirmation!
2. I am afraid I didn't understand what you mean.
3. It's my bad. I apologise! The backup of 29 was full. But since it was deleted by the clean-up task. How can it still be used as a base for differential backups? And what does this mean for the usability of these differential backups?
August 12, 2024 at 2:20 pm
3. SQL doesn't "know" you've deleted the backup file. Otherwise you could use it as a base. SQL will assume, reasonably enough, that if you take a full backup and then a diff that you held onto the full. Diffs are only useful to you if you keep the full backups.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 12, 2024 at 2:27 pm
To take the first differential backup, what does SQL require? I mean does it require a full backup that it took earlier?
August 12, 2024 at 2:29 pm
Yes, that's it, that's all that's required. The full could have been a month (or whatever) ago for all SQL cares. It's up to you to keep any backups you need to fully recover your dbs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 12, 2024 at 5:27 pm
So, when the full backup was taken and while it's still available, the first differential backup became possible. And after the first differential backup was taken, subsequent differential backups can now be taken forever, even after deleting that full backup, regardless of how useful they would be after deleting the full backup. Is that correct?
And can I make two sub-plans, one takes a full backup once a week on Sundays with the file extension .bak1 and a 14-day retention period, and the other takes differential backups on the other days of the week with the file extension .bak2 and a 7-day retention period?
Correct. How would SQL ever know you've deleted the backup file? It doesn't know, shouldn't know and shouldn't care.
I don't use maintenance plans (I'm a DBA, we use scripts), but in general, yes. You can do a full backup on whatever schedule you're comfortable with and diffs on non-full-backup days. This is a very common setup. Typically all backups use the extension .bak, but SQL doesn't actually care about that either. You could make it '.zzz' if you wanted to.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 12, 2024 at 5:59 pm
I heard you can make it .mp4.
September 17, 2024 at 6:20 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply