Sql-server – Where does SSMS Last database backup date come from

sql server

The backup date for several of my databases do not appear in msdb.dbo.back upset yet the correct date appears in SSMS when I select a db name, right click, properties, there in the general tab is the correct date. Where does SQL Server get this date?

Best Answer

Where does SQL Server get this date?

SSMS behind the scenes uses msdb.dbo.backupset to populate the data in the Properties page Last Database Backup. Note that the SSMS properties page does not give info about filegroup backup. It only gives info about full, diff or log backup.

You can use this script to retrieve SQL Server database backup history and no backups details

Related Solutions

Sql-server – Error in SSMS when attempting to restore a database

From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:


USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO

That example gets rid of everything over 30 days old, so you may need to change the -30 for a value appropriate for your needs. But remember that you'll then have to build up any restores for backups over 30 days old from hand (may or may not be a problem for you, but thought I'd better mention it).

Going forward you can schedule that to run via a scheduled job to keep you small.

SQL Server – Fix Error While Taking Backup Using SSMS

This is not a SQL Login permission issue. Rather that SQL Server Service Account can't access the location for the backup. To fix this you need to grant access in windows to that path to the account that the SQL Server Service executes under.

Best Answer

Related Solutions

Sql-server – Error in SSMS when attempting to restore a database

SQL Server – Fix Error While Taking Backup Using SSMS

Related Question