locked
Audit file delete, backup and restore RRS feed

  • Question

  • Hi,

    We have implemented audit for SQL Server 2012 instance. The audit record is being written to a file (.sqlaudit). We can query audit data from this file from SQL Server Management Studio.

    Overtime the file will grow large.  Can anyone tell me about below query-

    1) What is the way to delete/drop this file? Do I have to drop/delete it from SQL Server Management Studio?

    2) Is there any way to backup this file and if necessary can we restore it and query the audit file from  SQL Server Management Studio?

    Sunday, February 5, 2017 11:42 AM

Answers

  • Hi arifulhaq,

    >>So it is possible to backup (copy the audit file in 'E' drive) and if required copy the audit file back to 'D' drive and read the old audit data?

    No need to copy the file back to the original location, as long as SQL Server service account has read permission on the file(in E drive), it should be fine. But the thing here is, if the audit only uses one audit file, you may need to disable the audit for a while during the copy/move process.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 14, 2017 5:40 AM

All replies

  • Hi Arifulhaq,

    Refer 

    Deletion of audit file using Sql agent

    another way keep audit in Database 

    you can keep .Audit fila backup using sql agent and same deletion after some interval.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Sunday, February 5, 2017 12:01 PM
  • http://dba.stackexchange.com/questions/44780/how-do-i-maintain-more-audit-history

    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

    Sunday, February 5, 2017 12:07 PM
  • Hi arifulhaq,

    Please correct me if I’m wrong:

    >>1) What is the way to delete/drop this file? Do I have to drop/delete it from SQL Server Management Studio?

    It doesn’t seem like you could delete audit file from SSMS, you would have to delete the files from file explorer. You may have to temporary disable the audit if the file is in use.

    >>2) Is there any way to backup this file and if necessary can we restore it and query the audit file from  SQL Server Management Studio?

    Since the file is written on disk, the easiest way would be just cut/paste to somewhere else. And you can query the file from SSMS by using sys.fn_get_audit_file later. 

    But I’m still wondering how is your server audit configured(using rollover file or not) and how you gonna determine  which files needs to be backed up. Could you please elaborate more?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 6:23 AM
  • Hi arifulhaq,

    Please correct me if I’m wrong:

    >>1) What is the way to delete/drop this file? Do I have to drop/delete it from SQL Server Management Studio?

    It doesn’t seem like you could delete audit file from SSMS, you would have to delete the files from file explorer. You may have to temporary disable the audit if the file is in use.

    >>2) Is there any way to backup this file and if necessary can we restore it and query the audit file from  SQL Server Management Studio?

    Since the file is written on disk, the easiest way would be just cut/paste to somewhere else. And you can query the file from SSMS by using sys.fn_get_audit_file later. 

    But I’m still wondering how is your server audit configured(using rollover file or not) and how you gonna determine  which files needs to be backed up. Could you please elaborate more?

    If you have any other questions, please let me know.

    Regards,
    Lin


    Hi,

    Thanks for the reply. Please find feedback below-

    Currently audit file is being written in 'D' drive which is 100 GB in size. We did not use rollover option. 

    When 'D' drive will be full our plan is to backup (copy) the audit file in another drive (E drive which has 500 GB size) and continue the current audit file writing in 'D' drive. So it is possible to backup (copy the audit file in 'E' drive) and if required copy the audit file back to 'D' drive and read the old audit data?

    Is it possible?

    Thursday, February 9, 2017 12:17 PM
  • Hi arifulhaq,

    >>So it is possible to backup (copy the audit file in 'E' drive) and if required copy the audit file back to 'D' drive and read the old audit data?

    No need to copy the file back to the original location, as long as SQL Server service account has read permission on the file(in E drive), it should be fine. But the thing here is, if the audit only uses one audit file, you may need to disable the audit for a while during the copy/move process.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 14, 2017 5:40 AM