Maintenace Information Script Help....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Maintenace Information Script Help....

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-11-05 : 03:42:54

Hi,

i need some help with the folllowing script.

What i am trying to do is query my servers to gain some more information about the
maintenance plans installed on them.

i have the following script (sorry for the mess; but it works) what it does is
output information about the backups on my server; thats fine.....

but i only need to know the backup locations, and times the backups startfor each DB. so need
to group the results.

thats where i get stuck......
hope that makes sense

please help.

CREATE TABLE #Maint_Info (
[ServerName] Varchar(100),
[DatabaseName] sysname,
[Backup_start_date] datetime,
[Backup_finish_date] datetime,
[Expiration_date] datetime,
[Backup_type] nchar(50),
[Backup_size] varchar(max),
[logical_device_name] varchar(max),
[physical_device_name] varchar(max),
[backupset_name] varchar(max),
[description] varchar(128)
)

Insert INTO #Maint_Info(
[ServerName],
[DatabaseName],
[Backup_start_date],
[Backup_finish_date],
[Expiration_date],
[Backup_type],
[Backup_size],
[logical_device_name],
[physical_device_name],
[backupset_name] ,
[description]
)

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() - 100)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

select *
from #Maint_Info
order by DatabaseName

DROP TABLE #Maint_Info

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources