January 14, 2007 at 8:30 pm
Hi,
If I have some scripts that I need to run after every backup or restore,
is there any automation trigger that I can configure it at SQL enterprise Manager
or sql analyzer by script without having to do it manually ?
January 15, 2007 at 10:19 am
there's no event you can use, but you can check to see if soemthing has been backedup since a certain date, and do your special process from there (i assume it is a zip or file move function?)
the script below might help you; this is just checking for x number of days since last backup, but you can see there is a backup date in there if you need to modify it.
You could create a scheduled job that checks if anything has been backed up since the last time the job was run, and do your special processing based on that.
sample results: note that one db has never been backed up.
| Number_of_Days_since_last_backup | Backup_type_(D-database,L-log) | backup_size | database_name |
| 0 | NULL | NULL | DFAFM |
| 5 | D | 84363776 | DFAHDS_TEST |
| 45 | D | 23330304 | HAHDSDATA |
| 150 | D | 140955136 | DB_AUG17 |
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply