It’s that time of the month… the time when all of the T-SQL bloggers have a party and blog about a specific topic. Unfortunately, it’s been a few months since I’ve written a T-SQL Tuesday post. I’m hopeful that I can use this post to get back in the groove and participate in this monthly party on a, well, monthly basis.

Kenneth Fisher is our host this month, and the topic that he has selected is about “Backup and Recovery”. Kenneth states:

Backups are one of the most common things DBAs discuss, and they are at once one of the simplest and most complicated parts of our whole job. So let’s hear it for backup and recovery!

This is so true. Now, I want to get back to regular blogging – but this seems like a topic that is going to be pretty popular. I would venture that almost everyone will be talking about some aspect of the BACKUP or RESTORE command and working with the databases directly, so I would like to approach this from a different perspective.

Availability Groups and default XE Sessions

In a prior T-SQL Tuesday post, we were challenged to “Sharpen Something”, and I blogged about my deficiency in Availability Groups. Well, I’ve been working with them for a bit now. However, I’ve noticed that when things go wrong, there are lots of places that you can look at for finding out where the problem lies. And if the problem lies in the cluster, it becomes even trickier. However, the cluster writes out some .xel files – XE log files – by default to the SQL Server’s log directory. You won’t find this XE session running – it’s one of the hidden default sessions that Jason Brimhall blogged about.

In addition to these XE sessions being hidden, they only keep a small amount of data in just a few files. If you are looking back in time to see what had happened, it’s quite possible that you will find out that these files have rolled over on you, and what you’re looking for just isn’t in there anymore. I found that files only 8 hours old were being deleted because of this. If you’re trying to investigate what happened a few days later, well, you’re just out of luck.

Backups – more than just databases!

So how does this talk of AGs pertain to this T-SQL Tuesday topic? It should be pretty obvious – we need to periodically grab all of the .xel files generated by the cluster, and move them to a different directory, with a different retention policy. Yup… we need to back up these files. Sometimes, we need to be backing up things other than the databases themselves.

I created a PowerShell script that takes a few parameters, then moves the files from the source directory to the destination directory. And then it deletes files from the destination directory that are over x days old.

The next step is to schedule this script to run. I created a job, with a job step that executes a powershell script, and I call it with the desired parameters:

& "ScriptFullyQualifiedName" 
    -SourcePath C:\temp\source 
    -DestinationPath C:\temp\destination 
    -FileNamePattern "*.xel" 
    -DaysToRetainDestinationFiles 5

When I ran this job, it completed successfully. However, in looking at the source directory, all the files were still there. What’s up?

After a lot of investigation, it turns out that these particular files are created by the system, and all other users need administrator rights to work with the files. Even if the user has “Full Control” permission on the directory and all files in it. Since running the script from a SQL job with administrator privileges is a pretty difficult thing to do, what I ended up doing was to create a scheduled task at the OS level, where I can set the task to “Run with highest privileges” – aka administrator. Now the files are being copied out to a different location, and they are being retained with a different retention policy.

Just ensure that you have the disk space that you need. You don’t want to be causing further problems…

The post T-SQL Tuesday #85: Backup and Recovery appeared first on Wayne Sheffield.