Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Steps to restore a database that has a SQL Server Audit defined


By:   |   Read Comments (1)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

When restoring a database, that has audit specifications defined, on a different server, the audit events are defined but the audit mechanism is not able to capture audit data.  In this tip we walk through the steps you need to take to enable capturing of audit data for the database.

Solution

In this example, I will be using two SQL Server 2008 servers named SQLTEST1 and SQLTEST2.

Let's create the Audit definition on SQLTEST1 for database TEST.

CREATE SERVER AUDIT [Test_Audit]
TO FILE
(    FILEPATH = N'C:\AUDIT\'
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 90
    ,RESERVE_DISK_SPACE = ON
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO

USE TEST
GO

CREATE DATABASE AUDIT SPECIFICATION [Test_Audit_DB]
FOR SERVER AUDIT [Test_Audit]
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DELETE ON DATABASE::[TEST] BY [dbo]),
ADD (INSERT ON DATABASE::[TEST] BY [dbo]),
ADD (UPDATE ON DATABASE::[TEST] BY [dbo]),
ADD (SELECT ON DATABASE::[TEST] BY [dbo])
WITH (STATE = ON)
GO

Here we can see that the Audit Specification has been defined.

sql server audit specification

In order to verify that my audit trace is working, I ran the following code and verified that Audit information was captured.

sql server create and validate audit information

Let's backup the TEST database so we can restore it on the SQLTEST2 server.

sql server create database backup

Let's restore the TEST database on SQLTEST2 as SQLTEST2TEST.

RESTORE DATABASE SQLTEST2TEST FROM DISK='c:\SQLTEST2\AUDIT\TEST.bak'

After the restore, we can see that the database has been restored and we can also see the Audit Specifications defined.

sql audit specification defined

Even though the audit definition is defined, the server level Audit definition is not present on SQLTEST2 and therefore audit data is not captured. The reason for this is that when restoring or attaching a database that has an audit specification defined, but the specific GUID for that audit specification does not exist in the system tables this causes an orphaned audit specification.  In our case, since an audit with a matching GUID does not exist on server SQLTEST2, no audit events will be recorded.

Fixing the Orphaned Audit Specification

In order to correct this situation, we need to issue the CREATE SERVER AUDIT command on SQLTEST2 to create a new server audit with the specific GUID from SQLTEST1.

If the oringial server is available, we can script out the Audit definition as follows:

script out audit definition using SSMS

In my case the script generated the following output.

CREATE SERVER AUDIT [Test_Audit]
TO FILE
(    FILEPATH = N'C:\AUDIT\'
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 90
    ,RESERVE_DISK_SPACE = ON
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
GO

to make the Audit work on SQLTEST2 I need to issue the following command, making any adjustments necessary such as the FILEPATH in the below example.  Also, note that the AUDIT_GUID value is the same for both SQL Server instances.  This is so the audit can be tied correctly again.

CREATE SERVER AUDIT [Test_Audit]
TO FILE
(    FILEPATH = N'C:\SQLTEST2\AUDIT\'
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 90
    ,RESERVE_DISK_SPACE = ON
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
GO

If the original server is not accessible then you can get the AUDIT_GUID by runing the following command on the new server:

select * from sys.database_audit_specifications

This will return output simliar to below.

select SQL Server audit guid from system tables

When we have have the AUDIT_GUID we can issue the CREATE SERVER AUDIT command such as the following to set this up:

CREATE SERVER AUDIT [Test_Audit]
TO FILE
(    FILEPATH = N'C:\SQLTEST2\AUDIT\'
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 90
    ,RESERVE_DISK_SPACE = ON
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
GO

Once this has been done your database should begin collecting audit data again. 

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, November 09, 2012 - 11:13:28 AM - K Back To Top

Thanks so much.  This tip definitely saved me some time!


Learn more about SQL Server tools