April 16, 2010 at 5:12 am
can any one send the query how can we find whether the dbs are backed up in tape or in to the local disk in sql 2005 /2000?
April 16, 2010 at 5:51 am
If you know the name of the job thats doing the backup operation then you can use the following
select command from sysjobsteps where job_id
=(select job_id from sysjobs where name='<name of job>')
** replace the <name of job> with job name
Or you can take a look to all the server jobs that are there using the following query
select * from sysjobs
and then the one that look relevant of doing the backup
select command from sysjobsteps
Hope it helps.
April 16, 2010 at 5:52 am
Most important step I missed
First step is
USE MSDB
🙂
April 16, 2010 at 8:56 am
see if this helps you; it produces results like this, where NULLS mean the db was never backed up:
Number of Days since last backup Backup type (D-database,L-log) backup_size database_name
0 NULL NULL DBDFA04142010
0 NULL NULL DB_900_TEST
246 D 4094808064 DB900Beta
the code:
select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
as 'Number of Days since last backup',
b.type as 'Backup type (D-database,L-log)', b.backup_size, d.name
as database_name
from master..sysdatabases d with (nolock)
left join msdb..backupset b with (nolock)
on d.name = b.database_name
and b.backup_start_date = (select max(backup_start_date)
from msdb..backupset b2
where b.database_name = b2.database_name
and b2.type = 'D')
where d.name != 'tempdb'
group by d.name, b.type, b.backup_size
union all
select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
as 'Number of Days since last backup',
b.type as 'Backup type (D-database,L-log)', b.backup_size, d.name
as database_name
from master..sysdatabases d with (nolock)
join msdb..backupset b with (nolock)
on d.name = b.database_name
and b.backup_start_date = (select max(backup_start_date)
from msdb..backupset b2
where b.database_name = b2.database_name
and b2.type = 'L')
group by d.name, b.type, b.backup_size
order by d.name,b.type -- optional
Lowell
April 16, 2010 at 9:35 am
This query should help you for what you're after;
It will give you the past weeks backup history for all databases on a server, including the device which was backed up to 🙂
(If you need to extend or adjust the week timescale just change the "7" after the GETDATE near the end to whatever suits)
---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply