Vijay
asked on
MS SQL DB backup
We having 1 standalone DB. WE scheduled full backup for every Friday @ 10 Pm and log backup for every 15 mins.
But when i check DB properties it showing as 10/18/2017 PM. it supposed to be 10/13/2017. What is the logic behind this or when run this query it was showing last full backup was on 10/18/2017 PM.
But when i check DB properties it showing as 10/18/2017 PM. it supposed to be 10/13/2017. What is the logic behind this or when run this query it was showing last full backup was on 10/18/2017 PM.
SELECT sdb.name as DBName,
MAX(CASE WHEN bus.type = 'D' THEN bus.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN bus.type = 'I' THEN bus.backup_finish_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN bus.type = 'L' THEN bus.backup_finish_date ELSE NULL END) AS LastLogBackup
FROM sys.sysdatabases sdb
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.name
ORDER BY LastLogBackup descZahid Ahamed
Go and check your backup location identify the backup for that db if any users might take full backup instead of copy-only full backup. By the way is it in Prod or Test?
ASKER
Hi Sagar,
This is PROD. I am 100% sure that ,excpet me no one has access to this DB.
This is PROD. I am 100% sure that ,excpet me no one has access to this DB.
Can you go to Backup job history and check when it ran last time. And also go to the backup drive in order to look for backup creation date. Check the error log too what happened on 10/18/2017 9.00.00
ASKER
Bakcup job history: last full backup 10/13/2017 @ 10 PM.
Backup path: Last created file 10/13/2017 @ 10 PM.
Backup path: Last created file 10/13/2017 @ 10 PM.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good recommendation.