May 17, 2012 at 2:03 pm
Hi all,
I am looking for a small script that would simply display the status of all database backups
dbname
start
end
status ( sucess or failed )
servername
*** the important info is an actual column mentioning 'SUCCESS or FAILED '
Thx for the help !
May 18, 2012 at 5:21 am
Hi johnnyrmtl,
The backups related detailed information of completed backups is available in msdb.
However, Error/success can be obtained from sql server logs. Please try the following script.
GO
/*
Using sys.xp_readerrorlog. Please check MSDN for necessary permissions.
Remember all following conditions will be evaluated as AND operator.
*/
GO
CREATE TABLE #Temp_read_backup_log
(
LogDate datetime,
ProcessInfo varchar(255),
LogText varchar(max)
)
INSERT INTO #Temp_read_backup_log
EXEC sys.xp_readerrorlog
0,-- LogFile to read. Current=0, Archive1=1, Archive2=2
1,-- SQL Server (default) = 1, Agent = 2
'backup',-- Text1 to search
null,-- AND Text2 to search
'2012-May-16',-- Date Range Start
'2012-May-18',-- Date Range Ends
'ASC'-- Sort Order
INSERT INTO #Temp_read_backup_log
EXEC sys.xp_readerrorlog
0,-- LogFile to read. Current=0, Archive1=1, Archive2=2
1,-- SQL Server (default) = 1, Agent = 2
'backup',-- Text1 to search
'error',-- AND Text2 to search
'2012-May-16',-- Date Range Start
'2012-May-18',-- Date Range Ends
'ASC'-- Sort Order
/*
Following statements can be used to further narrow down the information.
Please change query parameter/variables according to your need.
*/
DECLARE @v_db_name varchar(255), @v_search_text1 varchar(255), @v_search_text2 varchar(255)
SET @v_db_name= '<Your DB Name>'
SET @v_search_text1 = 'Database backed up' -- OR 'Error'
SET @v_search_text2 = NULL
SELECT *
FROM #Temp_read_backup_log
WHERE
LogText Like '%' + ISNULL(@v_db_name, LogText) + '%' AND
LogText Like '%' + ISNULL(@v_search_text1, LogText) + '%' AND
LogText Like '%' + ISNULL(@v_search_text2, LogText) + '%'
ORDER BY LogDate
DROP TABLE #Temp_read_backup_log
GO
May 18, 2012 at 5:26 am
Hi johnnyrmtl,
You could use following script to get the details of your completed backups.
USE msdb
GO
/*
Following statements can be used to further narrow down the information.
Please change query parameter/variables according to your need.
*/
DECLARE @v_backup_start_datetime datetime, @v_backup_end_datetime datetime
DECLARE @v_database_name varchar(255), @v_backup_file_type char(1), @v_filesize_threshold_MB int
/* Default datetime is Yesterday */
SET @v_backup_start_datetime = convert(varchar(11), getdate()-1, 113) + ' 00:00:00'
SET @v_backup_end_datetime = convert(varchar(11), getdate()-1, 113) + ' 23:59:59'
/*Default DateTime is Last 24 Hours */
--SET @v_backup_start_datetime = getdate() -1
--SET @v_backup_end_datetime = getdate()
SET @v_database_name = '<Your DB Name>' -- OR NULL
SET @v_backup_file_type = 'D'-- 'D=data OR L=Log' OR NULL
SET @v_filesize_threshold_MB = 100
/* Check and Confirm parameter value */
--select
--@v_backup_start_datetime,
--@v_backup_end_datetime,
--@v_database_name,
--@v_backup_file_type,
--@v_filesize_threshold_MB,
select
bs.server_name,
bs.machine_name,
bs.database_name,
bs.recovery_model,
bf.logical_name,
bs.type,
bs.backup_size/1048576 size_MB,
bf.physical_name,
bf.state_desc,
bs.backup_start_date,
bs.backup_finish_date,
bs.checkpoint_lsn,
bs.database_backup_lsn,
bs.first_lsn,
bs.last_lsn,
bs.user_name,
*
from msdb.dbo.backupset bs INNER JOIN
msdb.dbo.backupfile bf ONbs.backup_set_id = bf.backup_set_id ANDbs.type = bf.file_type
where
(
database_name LIKE '%' + @v_database_name + '%'
OR
@v_database_name IS NULL
)
AND
(
bs.type = @v_backup_file_type
OR
@v_backup_file_type IS NULL
)
AND
(
bs.backup_start_date between ISNULL(@v_backup_start_datetime, '1900-Jan-01') AND ISNULL(@v_backup_end_datetime, '2999-Dec-31')
)
AND
bs.backup_size/1048576 > ISNULL(@v_filesize_threshold_MB, 10)
order by
bs.backup_size desc
Cheers.
May 18, 2012 at 1:25 pm
Thank you for the script ...
Although I'm not getting any output ?
I just want to return the results of all databases on the server.
May 20, 2012 at 3:22 pm
Please read the scripts and change the parameters where necessary.
Sometimes your required information might be in archived files. so change the xp_readlog paramter values to 1,2,3
I believe it should work. just ready and play with this script a bit.
Thanks.
May 22, 2012 at 7:05 am
OK I got the script to work by changing and commenting ! Thank you
Pardon my ignorance...
I still cannot get a column with a 'SUCCESS' or 'FAILED' to show up 🙂
May 23, 2012 at 3:36 am
Please put a case statement on logtext column,
CASE WHEN LogText Like '%error%' then 'Failed'
ELSE 'Success' -- "Database backed up."
END
Cheers.
May 23, 2012 at 9:08 am
The second script does work well with the required info ...but and like I said I would need that column with a status message 🙁
I can't seem to produce the requried results from first script though in executing the xp_readerrorlog
I need to implement a job which would send me this info on a daily basis
May 23, 2012 at 9:36 am
Note that when you write this, store the data, but don't report "success" to yourself on a regular basis. It's easy to start ignoring the failures if they occur rarely, and it wastes your time to check them.
I'd split out a separate report that you look at daily that only has failures.
May 23, 2012 at 9:45 am
Steve Jones - SSC Editor (5/23/2012)
Note that when you write this, store the data, but don't report "success" to yourself on a regular basis. It's easy to start ignoring the failures if they occur rarely, and it wastes your time to check them.I'd split out a separate report that you look at daily that only has failures.
That's indeed what I would need to do and I put this in the script which would I think give me a column with failed if it's NULL... ami i ok in doing this way ?
CASE WHEN bs.backup_finish_date is Null THEN 'FAILED'
May 23, 2012 at 10:55 am
I think finish_date is null while it's running as well.
What I was implying is that you need a script that only sends failures to you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply