February 11, 2009 at 6:53 am
I'm looking for a tsql query to list the backups performed. Since I can see that info in the database properties I'm guessing there is a view or stored procedure that will give it to me in tsql.
February 11, 2009 at 6:59 am
Hi,
you can get the details from the below, filter it as per your requirement
select * from msdb.dbo.backupset
Sriram
Sriram
February 11, 2009 at 7:00 am
mike (2/11/2009)
I'm looking for a tsql query to list the backups performed. Since I can see that info in the database properties I'm guessing there is a view or stored procedure that will give it to me in tsql.
query the backupset table in the msdb database to see backups performed in past.
February 11, 2009 at 10:31 am
Something like this ?
SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days
--AND sysdb.name = 'MY_DB_NAME'
ORDER BY sysdb.name, bkup.backup_finish_date desc
February 11, 2009 at 10:39 am
This is pretty good. Can you show me a query that will tell me the latest backup file name for a given database in SQL Server 2005? I need to write an automated job that will look for the latest backup file name for a specific database, and generate a script to load it into another SQL Server.
February 11, 2009 at 10:39 am
I need the full path and filename for the backup file.
February 11, 2009 at 10:42 am
Maybe you can tweak it. I had that already built, but I don't have much spare time right now.
February 11, 2009 at 10:49 am
I can't find where that info is in the system tables/view in 2005. In SQL Server 2000 it was in the message filed of the msdb..sysdbmaintplan_history table.
February 11, 2009 at 1:05 pm
You can get this information from msdb.dbo.backupmediafamily and the column name is physical_device_name
February 11, 2009 at 1:18 pm
Perfect!!! Thank you!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply