SQL SERVER – Backups are Non-negotiable Lifeline for DBAs

I have had my share of DBA friends who have told me a number of horror stories from their experiences. Thinking back, these look like moments to laugh away but there are some serious learnings we get from these experiences. I am a personal believer of learning something in everything that we do. That is one of the reasons I have been able to sustain active blogging every day for so many years. Being like a kid, with the inquisitiveness to learn, should never stop within us if we have to survive in the IT industry.

If you were to ask me to pick one single laughing and learning moment that stands out among the rest, I don’t think I could pick just one. Having said that, I do see one common theme when it comes to databases and backups. I am a regular visitor and contributor to SQL Server Bangalore UG and I learn a lot from my friends there. A number of them have asked how you restore system databases once it is corrupted. Do you ever take system databases backups proactively, as a just-in-case practice? How many of us do this in our testing, development and other integration servers today? I can bet you, not many of us.

In a recent user group meeting, someone asked me to simplify how a normal backup happens. For a second all the parameters involved in the UI screen came to my mind and I held my thought. I said, “Buddy it is simpler than you can think.” I told the following flow:

This is the fundamental building block as backups happen. Now we can add spice to this workflow with options like compression, verify file, encryption and others. But the basic structure cannot be simplified more than this.

SQL Safe Backup

There is never just one way to do things. I had the opportunity to look at Idera’s SQL Safe Backup and some of the things that caught my attention are listed below. You need a structured method to automate and build a process for your backups if you are working in an enterprise.

First impressions – Starting a backup

I started the backup wizard to see what it can give me for a starter. The below screenshot shows the set of databases on a particular instance which can be configured for backups. It can be Full, Differential, Log or File backups. All these are out-of-box capabilities. What I saw immediately was the data about when the last backup was done. This is nifty and nice information to start when taking manual backups.

Four settings that surprised me

The wizard has a number of settings that stumped me. For instance the CopyOnly backup option as part of General tab was a pleasant surprise. To add to it, on selection it gives me important information about how a CopyOnly doesn’t break the Log chain etc. If you have a Junior DBA in your organization, these tips will surely help.

The next two settings are great best practices for me. The “Enable Checksum” is something I advocate everyone when working with versions of SQL Server 2005 and above. It is great to see as an option as part of the wizard.

The icing on the cake the tool gave was “Network Resiliency.” The attached screen shot shows how the retry logic happens and for how long when the backup needs to be placed in a network share (UNC path).

Finally, if these were not enough. There is a way to script the whole thing and run it as command line option or TSQL script. This can later be integrated with custom applications or even with SQL Agent. A typical script for my above system databases backup looks like:

Command line script:

“C:\Program Files\Idera\SQLsafe\SQLsafeCmd.exe” Backup master msdb model “C:\Backup\%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe” -CompressionLevel ispeed -RetryWrites 10 300 60 -Server sqldude

T-SQL construct

DECLARE @ResultCode INT
EXEC @ResultCode = [master].[dbo].[xp_ss_backup] @database = N'master',
@database = N'msdb',
@database = N'model',
@filename = N'C:\Backup\%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe',
@compressionlevel = N'ispeed',
@retrywrites = N'10 300 60',
@server = N'sqldude'
IF(@ResultCode! = 0)
RAISERROR('One or more operations failed to complete.', 16, 1);

Though all these got me interested, I wish the tool had more options to integrate with native settings. Some of the options I love to see would include – utilizing the native compression technique of SQL Server, MaxTransferSize setting, BlockSize setting, BufferCount settings for backup can be really handy. Also for SQL Server AlwaysOn configuration databases, it would be great to add options or settings to check for secondary servers in case we want to create a backup. The restore process is simple and the wizard is really handy here too. Let me move to the next interesting part.

Defining Policies – Making a blueprint

Every organization has its own retention policies when it comes to backups. They build their own strategy of backups based on RTO and RPO requirements of the business. Having a strategy to recover is the most important task. So Idera’s SQL Safe Backup did give me some great ideas. The wizard for creating the policy can be for backup, restore or for log shipping. The basic backup wizard allows us to select instances, DB’s and create a plan accordingly.

What I loved about the wizard is that, I can create a consolidated single plan in one shot for Full, Transaction logs and Differentials for my organizational mission critical database in one go. The above schedule is a classic screen shot of how you can schedule in one screen how the backups would be taken.

What I would love to see is some graphical representation on a week’s timeline to how backups are taken. This will help the DBA take a call if their strategy is meeting their SLA or not. I am so used to backup timelines with SSMS that I thought this would be a great addition. Apart from this, in enterprises we have a need to define our own custom encryption with SQL Server using Backup Encryption. I hope the feature to import a custom certificate for encryption would help many with the tool.

Final note

All tools evolve, and their fundamental motive is to make life of people easier. SQL Safe Backup is surely a tool geared in that direction. It uses the standard VDI interface to take backups. I wish as the new versions are released, the tool also aligns with utilizing the capabilities of new functionality. Some that I have pointed included external Encryption, native Compression, AlwaysOn secondary backups, backup to URL etc. For today’s deployments, a lot of them require these tools to organize their backup strategy efficiently. I think of SQL Safe Backup as like a Swiss army knife in the DBA’s pocket.

You can download SQL Safe Backup from here.

Reference: Pinal Dave (https://blog.sqlauthority.com)