locked
Daily Report for SQL database RRS feed

  • Question

  • Hello,

    I try to think a way to create a daily report for all SQL databases. The report should show the backup if it fails and any error has occured. How could I schedule the report in SQL management studio?

    Do you have any report or the idea please share with me?

    SQL database is new to me and I just try to get some helps from you who have experience on SQL database. I really appreciate you.

     


    Madison
    Wednesday, October 26, 2011 7:34 PM

All replies

  • Bears one question for me: How do you do your backups? Normally the easiest way to get a backup report is from the backup solution itself...

    Lucifer

    Thursday, October 27, 2011 5:19 AM
  • I would recommend take some course or training class for SQL Server if you are going to  deal with SQL

    http://www.sqlserver-training.com/database-backup-report/-


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 27, 2011 7:43 AM
  • Hi Madison,

    As you post this question in Database Mirroring forum, do you mean Monitoring Mirroring Status ?


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Peja Tao Friday, October 28, 2011 8:15 AM
    Friday, October 28, 2011 8:14 AM
  • Is this all sql databases on just one server or all sql databases across all servers which may be on different versions?

    If you could provide a bit more detail one of us is bound to come up with a cracking answer.

    If it's just one instance then you can query MSDB to find out information on backup history which you can then schedule in a SQL job. Multiple servers is a bit trickier and you might be better to look at a third party solution that can manage all of your backups and reporting for you, especially if you are new to the industry.

     


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich
    Monday, November 28, 2011 11:35 PM
  • In addition, any errors occur while backups failed you can see in ERROR.LOG file...

    --First param--0- current log, 1-archive 1

    --Second param 0-Error LOG,2 = SQL Agent log 

    EXEC sp_readerrorlog 0, 1, 'ERROR'  

    EXEC sp_readerrorlog 0, 1, 'deadlock' 

    EXEC sp_readerrorlog 0, 1, '2005', 'backup log'  

    EXEC sp_readerrorlog 0, 1,'backup'

    EXEC master.dbo.xp_readerrorlog 0, 1, 'ERROR',NULL, NULL, NULL, N'DESC'   


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 29, 2011 7:35 AM
  • We have an enterprise monitoring solution at my company. But I still like to confirm. 

    I have a little batch file that is scheduled (via task scheduler) to kick off at 6:00AM each day. You will need to create a text file called prod_inst.txt and list the instances you want to monitor. The log will be waiting for you when you get to work, and you simply go to one place to review. This script has been with me for some time now. It may not be pretty, but it does the job, and if it aint broke..... 

    These series of scripts require you to create the GetTime.bat batch file, and a text file named prod_inst.txt which lists your instances you want to monitor. 

    You will also require msql.exe - google it - and drop this in the root folder as well.

    ####################### check_backup.bat ####################

    @echo off

    set rootdir=D:\MorningMonitoring
    Call %rootdir%\GetTime

    Set LogDir=%rootdir%\Log
    Set Logfile=%LogDir%\Backup_%LogDate%.log

    echo Checking Previous Nights Backups > %LogFile%
    echo --------------------------------------------------- >> %LogFile%
    msql.exe -C%rootdir%\prod_inst.txt -Q"SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name WHERE B.name not in ('pubs','Northwind','tempdb') AND type != 'L' GROUP BY B.name ORDER BY B.name" -w100 -E >> %LogFile%

    ################################# END ################################

    ####################### GetTime.bat ####################

    rem @echo off

    for /F "tokens=1-2 delims=: " %%i in ('time /t') do (
       set Hour=%%i
       set Minute=%%j
    )

    for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
       set DayOfWeek=%%i
       set Day=%%k
       set Month=%%j
       set Year=%%l
       set Date=%%l_%%k_%%j
    )

    set LogDate=%Month%%Day%
    set LogTime=%Hour%_%Minute%
    set LogDateTime=%Date%_%Time%
    set FileDate=%Year%-%Month%-%Day%_%Hour%-%Minute%

    ################################# END ################################

    ####################### check_sql_jobs.bat ####################

    @echo off

    set rootdir=D:\MorningMonitoring

    Call %rootdir%\GetTime

    Set LogDir=%rootdir%\Log
    Set Logfile=%LogDir%\sql_jobs_%LogDate%.log


    echo Checking Previous Nights sql jobs > %LogFile%
    echo --------------------------------------------------- >> %LogFile%
    msql.exe -C%rootdir%\prod_inst.txt -Q"select run_date,[name] FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id WHERE run_date = (select distinct convert(char(20),getdate()-1, 112) from master..sysdatabases) AND enabled = 1 AND run_status = 0 AND step_id = 0" -E >> %LogFile%


    ################################# END ################################

    You should also google snapshot.hta. I found this a few years back and have found it very useful. There is also an audit.hta.

    Hope this helps.

     

     


    Scott H
    Saturday, December 3, 2011 12:21 AM
  • DECLARE @DBName SYSNAME
    SET @DBName = 'YourDatabase' 
    DECLARE @LatestBackupDate DATETIME, @BackedUpDBCreationDate DATETIME
    DECLARE @DBCreationDateNoMilliseconds DATETIME
    -- In msdb..backupset table, database_creation_date column is in datatime format
    -- but without milliseconds, so we remove milliseconds from database creation date.
    SELECT @DBCreationDateNoMilliseconds = 
        DATEADD(millisecond, -DATEPART(millisecond, create_date), create_date)
    FROM sys.databases
    WHERE [name] = @DBName 
    IF @DBCreationDateNoMilliseconds IS NULL
       BEGIN
           PRINT 'Database ' + @DBName + ' doesn' + CHAR(39) + 't exist on the server'
           RETURN
       END
    SELECT
       @LatestBackupDate = backup_finish_date,
       @BackedUpDBCreationDate = database_creation_date
    FROM msdb..backupset
    WHERE 
       backup_set_id = (
           SELECT MAX(backup_set_id)
           FROM msdb..backupset
           WHERE database_name = @DBName AND
           [type] = 'D' )
    IF @LatestBackupDate IS NULL
       BEGIN
           PRINT 'Full backup had never been taken for database ' + @DBName
           RETURN
       END
    IF @BackedUpDBCreationDate = @DBCreationDateNoMilliseconds
       PRINT 'Full backup exists for database ' + @DBName + '; Transaction log backups can be taken'
    ELSE
       PRINT 'Full backup exists for the current version of ' + @DBName + ' database  had never been taken. Existing backup refers to non-existing database with the same name'

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, December 6, 2011 7:55 AM