Record Date Backup was run

[NickyJay]

Hi Guys,

I've trawled the threads and can't find any help with this - basically i have a scheduled backup running every month. The backup is used to run a webpage and we want to be able to show on the webpage the date the backup was taken - like a "last updated on..." thing.

So far i have a table in the database with two fields - one is the date field with GetDate() as the default value, the other just a description field.

In my scheduled backup i have the first step to insert "backup taken" into the description field and nothing into the date field (was told this would automatically populate this field with the date).

However, when i test the backup, it fails and doesn't write anything tot he table and doesnt provide me with any sort of error message!

Has anyone done anythin similar or have an idea to a better way around this?

Thanks

 

[tran008]

It may be better to look up the date from msdb.dbo.backupset.

 

[EriRobert]

Nicky

	SELECT Max(backup_finish_date ) as LastBackupDate
	FROM msdb.dbo.backupset
	WHERE
		database_name = @vchClientName

works for me

Regards

 

[mutley1]

Nicky,

This will give you dbname, size in bytes, kb, mb and GB (as not sure what the sizes of your DB's are) and the physical file name for all DB's on the server.

As per Eric's post, you can put a variable / where clause in for a DB name if you only want one DB.

USE MSDB
GO
SELECT
	a.database_name,
	a.backup_size,
	convert (decimal(30,2), (a.backup_size + SUM(1536)) / 1024) As SizeInKB,
	convert (decimal(30,2), (a.backup_size + SUM(1536)) / 1024 / 1024) As SizeInMBs,
	convert (decimal(7,3), (a.backup_size + SUM(1536)) /1024 / 1024 / 1024) As SizeInGBs,
	a.backup_finish_date,
	b.physical_device_name
	    
	    
FROM (
	SELECT database_name, MAX (backup_finish_date) as backup_finish_date
	FROM backupset
	--WHERE backup_finish_date > '2005-12-31'
	WHERE media_set_id IN
	(SELECT media_set_id FROM backupmediafamily
	 WHERE type = 'D')
	GROUP BY database_name) z

JOIN

backupset a on z.database_name = a.database_name
	    and z.backup_finish_date = a.backup_finish_date
JOIN
backupmediafamily b on a.media_set_id = b.media_set_id

GROUP BY a.database_name, a.backup_size, a.backup_finish_date, b.physical_device_name
ORDER BY a.database_name

HTH

M.