Backups – They Are Needed, Who Knew?

Backups are essential for a successful business model. That statement may or may spark some topics for debate, but at the end of the day if the data professional does not have a form of backup in place for his/her business needs you may, no you will, feel the pain. It may not happen today, tomorrow, next week, but you can with 100% certainty guarantee that at some point in ones career you will need a backup of your database.

Importance

Let me start off this way and ask a very simple question, “Do I have to take a backup?” The answer to that is yes, yes you do. If you are a data professional than you should care about your data enough to take a backup of it in some form or fashion.

Types of Backups

Full Backup – this type of backup contains all the data for a specific database.

Differential Backup – think about this backup as what it’s name states; contains only the data since it’s last differential base backup; you can find these backups to be smaller in nature versus the full backup methodology

Transaction Log Backup (T-Log Backup) – this type of backup is a record of all transactions that have been performed against the database since the transaction log was backed up. Most often times these types of backups are taken on a more frequent basis.

**Note** the differential and transaction log backups are both dependent upon the full backup initially being executed.

Disaster Recovery

Depending on how extensive your business model is some companies will rely on backups for their disaster recovery planning. Whether you log ship, utilize always on, restore databases periodically etc. backups can and will always be an essential part of disaster recovery.

Tuning Backups

Most people don’t realize that they can tune their backups. One of the ways you can do this is by turning on some trace flags and increasing some throughput. Below are two statements you can utilize.

DBCC TRACEON (3605, -1) and DBCC TRACEON (3213, -1)

What those two statements do is tell you (in your error log) what the settings are set to

The buffer count and maxtransfersize are the two settings you want to check. Make note of what the settings are initially; then when backing up your database, whether by a stored procedure or method of choice, you can include the following code.

WITH COMPRESSION
, BUFFERCOUNT = 800
, MAXTRANSFERSIZE=4194304

**NOTE – never take code from the web and execute it in production. Utilize this in a testing environment to see how it performs.

Wait, what? You mean I need to test my backups. Let me pose this question another way. If you take a solid backup and you store it for a certain period of time; then how do you know if you can restore it or not? Taking backups are only half the process; I used to think early on in my career that I was golden to have a backup versus the people who don’t take backups at all. Sure that is somewhat true but the flip side to that is I was missing the bigger picture; periodically test your backups. In a perfect world an automated process would restore backups to an isolated environment then fire off an alert if you find one that could not be restored. Most shops don’t or can’t go to that extent so at the minimum periodically test your backups for validity. Not only will it prove that your backups are working but will keep your skill set honed in the restoration process.

Wrap Up

Backups – they are important. As with anything in your data professional career; take this concept to be very important. If you aren’t backing up your data than I suggest you start. If you are backing up your data; then are you sure you can restore it? Are your backups taking forever; perhaps you can tune them? I tell you what…keep reading below and you can check out what some of my colleagues have to say around backups. Enjoy

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

As to whether/when/and which methods of backups are needed, one needs to know from the “business side”: Do Government requirements define duration? Do agreements signed by one’s company w/ the client define the minutes/hours of how long it will take to recover the client’s environment? Do we recover from an on-site backup of last night? an on-site backup of last week? Or an off-site backup from the last 2 hours/days/months??

When I have gone to a new environment, or one where there are not answers to “what we need,” I have modified DBs to full recovery mode, taken a full backup, then begun log backups every 15 minutes & scheduled differentials every evening. I

If the DB is healthy, free space on the log files is good, and the environment’s i/o reasonable, then of course this may be modified. Just a suggestion. welcoming more

Hi, Chris. It’s good to know that this is useful. (much of the experience came from a place that provided federal medical program support, and the govt requirements about recover-ability were new to me and critical for our company. BTW – I managed, alone, about 585 production DBs on about 45 mssql instances; there were no docs when I arrived.) This is not said to discourage others, but to encourage them that this can be done by them also. With help and support from colleagues. I will be happy to help any others, and answer any questions. best wishes