Know Backup Date using DB backup file

Question

Post reply

Know Backup Date using DB backup file

Trouble Shooter

SSC Eights!

Points: 902
More actions
August 26, 2010 at 11:18 pm

#226948

Hi,
I want to know on which date i had taken backup of selected database using backup file .
Plz. Help
Thank in Advance
Pratik Asthana

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

SELECTt.name as [DB Name],

t.user_access_desc as [Access State],

t.state_desc as [Online/Offline],

((SELECT (CASE t.is_in_standby WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Other' END))) as [In Standby],

(COALESCE(Convert(datetime, MAX(u.backup_finish_date), 101),'Not Yet Taken')) as [Last BackUp Taken],

ROUND((((COALESCE(Convert(real(256), MAX(u.backup_size), 101),'NA'))/1024.000)/1024.000),3) as [Backup Size in MB],

(COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup],

(COALESCE(Convert(varchar(12), MAX(u.type), 101),'NA')) as [Backup_type],

(COALESCE(Convert(varchar(12), MAX(u.user_name), 101),'NA')) as [User Name]

FROM SYS.DATABASES t

INNER JOIN msdb.dbo.BACKUPSET u

ON t.name = u.database_name

GROUP BY t.Name,t.is_in_standby, t.user_access_desc, t.state_desc

ORDER BY t.Name

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 2

Hi,

Many thanks for reply.

this query is new for my knowledge.

but this query not solve my problem yet ,actually i have problem that i have backup file on my local hard disk called "MyDBBackup.bak" .i want to know on which date i had taken backup using that "MyDBBackup.bak" file.

plz. help

Thanks in Advance

Pratik Asthana

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

in windows explorer you can view the modified date as a column header.

activate it if it's not there by default (which it should be)...

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 4

Thanks for Reply.

this option i have and i know it .

is there any other option or tool to show backup date .

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 5

I am getting a bit confused about what you actually want....

Instead of giving your backups custom names, do default backups in sql server which will include a date and timestamp....example: MyDB_201008270828.bak

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 6

Hi,

let me explain my situation in detail.

I have one old backup file name called "MyDBBackup.Bak" . now i want to know on which date i had taken backup of Database with this name .

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Use RESTORE HEADERONLY. Should show start and finish dates for the backup

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Trouble Shooter SSC Eights! Points: 902 More actions · Answer 8

Hi,

Done.

Many Thanks to GilaMonster and also to all member who replies and try for my question.

Thanks

Pratik Asthana