none
Backup status logging RRS feed

  • Question

  • Hi,

      We are using sql server 2012. We have configured backup in our setup. We wanted to enable loggigng in such a way that when ever backup job runs it should log the status in error log. Please let me know how to enable this. As of now it is creating seperate log file in log location after every execution. We wanted them to be logged in Error Log file it self. Pls guide me.

    Thanks,

    Varun


    • Edited by Raje14 Tuesday, June 9, 2015 9:28 AM
    Tuesday, June 9, 2015 7:59 AM

Answers

  • Hi,

    By default, every failed/successful backup operation adds an entry in the SQL Server error log and in the system event log.

    However, if you set trace flag 3226 on, then SQL server turns off logging of all successful backups.

    So, make sure you have not set trace flag 3226 on.

    Use below command to find which trace flags are on

    DBCC TRACESTATUS

    Also, refer below link for more information

    Stop logging all successful backups in your SQL Server error logs


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Marked as answer by Raje14 Thursday, June 18, 2015 7:35 AM
    Tuesday, June 9, 2015 9:09 AM
  • Hello - Backup action whether Successful or unsuccessful makes an entry in SQL Server Error Log File automatically. You do not have to enable anything extra to get this information. See below screenshot, it covers both events when backup is executed by Maintenance Plan:


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Raje14 Thursday, June 18, 2015 7:35 AM
    Tuesday, June 9, 2015 8:54 AM

All replies

  • Hi Varun,

    How did you configure you backup job? You can modify properties of agent job and it will log message to windows event application log.


    Regards,
    Vishal Patel
    Blog: http://vspatel.co.uk
    Site: http://lehrity.com

    Tuesday, June 9, 2015 8:11 AM
  • Good Day,

    Database Backup status information will be in SQL Server Logs. Check the SQL Server log not the SQL Server Agent log. This logging information can be disabled using DBCC TRACEON(3226,-1) and enabled back on a restart or using DBCC TRACEOFF(3226,-1). Check whether DBCC TRACON 3226 is enabled .

    Thanks,

    Sajith


    http://sqllive.wordpress.com/

    Tuesday, June 9, 2015 8:16 AM
  • BACKUP DATABASE dbname TO DISK = N'C:\Temp\dbname.bak' WITH INIT, CHECKSUM,STATS = 10

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, June 9, 2015 8:37 AM
  • Hello - Backup action whether Successful or unsuccessful makes an entry in SQL Server Error Log File automatically. You do not have to enable anything extra to get this information. See below screenshot, it covers both events when backup is executed by Maintenance Plan:


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Raje14 Thursday, June 18, 2015 7:35 AM
    Tuesday, June 9, 2015 8:54 AM
  • Hi,

    By default, every failed/successful backup operation adds an entry in the SQL Server error log and in the system event log.

    However, if you set trace flag 3226 on, then SQL server turns off logging of all successful backups.

    So, make sure you have not set trace flag 3226 on.

    Use below command to find which trace flags are on

    DBCC TRACESTATUS

    Also, refer below link for more information

    Stop logging all successful backups in your SQL Server error logs


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Marked as answer by Raje14 Thursday, June 18, 2015 7:35 AM
    Tuesday, June 9, 2015 9:09 AM
  •   We are suing sql server 2012. We

    Varun it seems like you are trying to *Sue* SQL Server its hilarious please correct the spelling :).

    Instead if enabling logging which would fill up the drive you can refer to errorlog and filter it using powershell or CLR and get required information

    sp_readerrorlog,0,1,'backup'


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, June 9, 2015 9:15 AM
  • Varun,

    As mentioned by others, whenever backup succeed or failed it will be logged into the server SQL Server Error logs, and you don't need to take any other measures.  Can you please let us know what exactly you are looking for ? Want to be alerted on event  of backup failure ?


    Thank you
    Hemantgiri S. Goswami | SQL Server Consultant
    SQL Server Citation| Twitter | Facebook
    Author of book - SQL Server 2008 High Availability

    Wednesday, June 10, 2015 4:30 AM
    Moderator