{"id":29,"date":"2012-06-13T16:29:10","date_gmt":"2012-06-13T22:29:10","guid":{"rendered":"http:\/\/s1095387061.online-home.ca\/?p=29"},"modified":"2013-02-21T16:04:22","modified_gmt":"2013-02-21T22:04:22","slug":"backup-default-sql-server-trace-files","status":"publish","type":"post","link":"https:\/\/databaserefresh.com\/blog\/backup-default-sql-server-trace-files\/","title":{"rendered":"Backup Default SQL Server Trace files"},"content":{"rendered":"

Default Trace first has been introduced in SQL Server 2005 and could be really useful for Database Administrators in troubleshooting.<\/p>\n

There is really great article about what Default Trace is and what you can do with it if you want to learn more:<\/p>\n

http:\/\/www.simple-talk.com\/sql\/performance\/the-default-trace-in-sql-server—the-power-of-performance-and-security-auditing\/<\/a><\/p>\n

Today I would like to share with you my solution on how to set up an automated trace files\u2019 backups.<\/p>\n

How many times you thought: \u201cIf I set this audit last month I would know who changed the server configuration\u201d. Unfortunately sometimes we are too busy with our day to day work or do not think that we will ever need to check who changed that specific option on SQL Server and when it was changed. One of the reasons could be that nobody has access to the SQL Server except you. Or you know what you do and documented every one of your steps. But what about those new applications that require sysadmin privileges on SQL Server to install applications and databases? You ask vendor what are the requirements and their response is: \u201cSQL Server 2005 or higher\u201d. And then you discover that installation process actually changed your SQL Server configuration to enable CLR.<\/p>\n

This is not real life example, but very close. It took me a couple of hours to go through the tape backups to find out when I changed the server configuration, because I could not remember changing it at all! So, it WAS an application installation.<\/p>\n

The first problem when you go through the tape backups is that it is very time consuming. The tape could be offsite; you need to involve backup guys. The second problem is that the trace file we are looking for may not even be on the tape, especially if you backup trace files only once a day. The number of trace files is limited to five files and the new file is generated when file\u2019s size reaches 20 MB. At the same time the oldest file is deleted.<\/p>\n

There are lots of solutions out there to have specific records imported to a database from the trace file. One of the latest articles by Aaron Bertrand could be found here:<\/p>\n

http:\/\/www.mssqltips.com\/sqlservertip\/2695\/maintaining-sql-server-default-trace-historical-events-for-analysis-and-reporting\/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=201257<\/a><\/p>\n

But this does not quite work for me. I do not know what I may need next time, so capturing just specific records do not really work for me. Considering that I use DDL triggers for audit and that I need to check old trace files probably once a year, I do not want to import all records to the database, because this is another database to support and this includes backups, maintenance etc.<\/p>\n

To save myself time when I needed to check something the next time I came up with solution to backup trace files. This solution will allowed me to make sure that ALL trace files are on the tape backup as well as allow me to will keep an archive of the trace files on the server for the quick access. The solution has been designed and tested for SQL Server 2008 R2.<\/p>\n

We want to have all archived Default Trace files on the same server, but in different folder. The files that are archived would also be compressed (zipped).<\/p>\n

A little challenge here is that we cannot backup the latest trace file because it will be still in use. We can will only backup \u201cclosed\u201d traces.<\/p>\n

Components\/Requirements:<\/strong><\/p>\n

    \n
  1. Archive folder on the server (make sure that SQL Server account or account that will be used to run the archiving job has rights to create and delete files in this folder). To make the scripting more generic I have created the folder under the \u201cLog\u201d folder (location of the Default Trace on your SQL Server instance).<\/li>\n
  2. WMI SQL Server Alert.<\/li>\n
  3. SQL Server Job to respond to alert (modify job to backup files).<\/li>\n
  4. SQL Server Agent \u201cReplace tokens for all job responses to alerts\u201d option is enabled.<\/li>\n
  5. Database mail set up.<\/li>\n
  6. Operator exists to get test e-mails.<\/li>\n
  7. Gzip utility to \u201czip\u201d archived files.<\/li>\n<\/ol>\n

    Note<\/span>: 5 and 6 only required for the testing. You may skip testing part as this is just to demonstrate files rollover process and response.<\/p>\n

    Implementation Steps (including testing):<\/strong><\/p>\n