SQL FULL/Differential/Transaction LOG backup to share (Availability Group)

The Case

Today I will be blogging about SQL database backups and the different methods of creating those backups.
SQL has a build in feature for backups and maintenance, however, when you are working with a “custom environment” the standard features sometimes are not sufficient.
We build a new System Center Operations Manager (SCOM) environment in Azure, the environment contains seven servers, and connects to multiple customer environments.

The Environment

The following servers are in place for the environment:
1- GWnode01 (SCOM Gate way node directly connected to the internet, the customer gateway server report through this node to SCOM)
2- GWnode02 (SCOM Gate way node directly connected to the internet, the customer gateway server report through this node to SCOM)
3- MS01 (SCOM management server, handles the connections to the databases and hosts the console)
4- MS02 (SCOM management server, handles the connections to the databases and hosts the console)
5- SQL01 (Database server that contains the 2 databases, this server is part of an availability group)
6- SQL02 (Database server that contains the 2 databases, this server is part of an availability group)
7- REP01 (Server dedicated for replicas and backups. The quorum and cluster configuration for the SQL servers is also stored here.)

As mentioned we use an AlwaysOn configuration for the SQL servers, and the connection was made with a listener.
Everything is functioning perfectly, the last step was setting up the backups.

The Backups

We decided to go with three types of backups:

1- Full backup (through a schedule this backup runs weekly on Sunday evening, we overwrite the existing backup from the previous task)
2- Differential (through a schedule this backup runs daily, we keep a week of backups in place, a maintenance task removes all backups older then a week)
3- Transaction LOG (through a schedule this backup runs every eight hours and 3 times a day, we overwrite the existing backup from the previous task)

The reason we do three Transaction LOG (TLOG) backups a day is the AlwaysOn configuration.
AlwaysOn requires that all databases are in a FULL backup setting, with the setting in place the TLOG will keep growing infinitely.
At a given moment the DataWarehouse database TLOG was around 800 GB large…

Luckily there is a very simple method of shrinking the database size, just run a TLOG backup and the TLOG will release his content (after that you can shrink the TLOG database size).
I have tried to create the TLOG backup task with the maintenance plan (MP) wizard, and execute the task, but the backup never arrived at the share (to a disk is of course no problem).
So, I tried it directly from a query. It runs, and the query is executed successfully, but my backup never arrives at the share…

The Commands

Last chance, the trust worthy PowerShell!
I executed the following command from PowerShell:

Backup-SqlDatabase -ServerInstance -Database -BackupFile \filepathfilename.TRN -BackupAction LOG

It worked, the backup is created and written to the share, it is finally working.
At last, we can work in SCOM without reports not being created or tasks taking minutes or sometimes hours :)

Now for the schedule of the task, MP does not support PowerShell commands. So there we had an issue.
A quick search on the internet turned up with a suggestion, create a SQL server agent job, attach it to the MP, and run it with a schedule.
So, on an adventure we went.

First, I created a SQL server agent job, gave it a name and started creating steps. I made two steps, so both the TLOG backups were created (make sure to choose PowerShell by “Type”).
Save the job and create a MP, make the schedule according to your requirements and attach the new job. (Use the toolbox to create an “Execute SQL Server Agent Job Task”)
Save all your settings (!!!) and execute the MP. Navigate to your share, and smile with pride.

For the FULL and Differential backups I used the same steps, only selecting Transact-SQL (script) in the “Type” field.
For my FULL backup I used the following TSQL script:

BACKUP DATABASE
TO DISK = '\filepathfilename.bak'
WITH FORMAT, COPY_ONLY;

For my Differential backup I used the following TSQL script:

DECLARE @var nvarchar(max) ='\filepathfilename ' '+replace(rtrim(convert(char,getdate())), ':',',')+'.bak';
BACKUP DATABASE TO DISK = @var WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, COPY_ONLY, STATS = 10

The FULL backup script is pretty straight forward, backup the database to the share. For the Differential script I included some other options that will add the date/time when the backup was made.
Easy for figuring out when the backup was really made, and no overwrite when you create multiple backups in one folder.

Now I hear you thinking, I also have an AlwaysOn configuration and I want the backup tasks run on the secondary node (keeping unnecessary load of the primary node).
Well, that is possible, and very simple. When looking at your availability group you can see what the primary and secondary node is.

I added the following lines to the scripts and everything was in place:

If sys.fn_hadr_backup_is_preferred_replica('') = 0
Begin
-- This is not the preferred replica
raiserror( 'Backup is not preferred on this replica',10,1)
end
else
Begin
-- Backup this database
PLACE YOUR TSQL CODE HERE
end

What this script does is query through TSQL for the secondary node using the line “sys.fn_hadr_backup_is_preferred_replica”.
With the feedback he gets from the query the following lines decide if you can run your commands or quit with the specified message.

For the TLOG backups we created identical back up tasks on both nodes (with a different location on the REP01), with the amount of data we get we let the two nodes run their own backups, with a time difference of four hours.
This keeps the TLOG size to a minimum and SCOM fast. If you want to use a PowerShell script with build in replica node check, I suggest the following site: https://msdn.microsoft.com/en-us/library/hh710053.aspx