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.
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.
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.
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.
, BUFFERCOUNT = 800
**NOTE – never take code from the web and execute it in production. Utilize this in a testing environment to see how it performs.
This little trick was picked up by watching the might Sean McCown at PASS Summit 2014 in one of his sessions.
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.
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
On a SQL Collaboration Quest
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.
thanks for the post. Although it's a bit of a repeated answer to a constantly repeated question it's the most important task any DBA should take on board. Just running a backup is just not good enough and that is a point that any DBA should point out to her's/his managers, because some are not technical people and when it comes to details the could be surprised as to how much time you need allocate to this kind of task. The main reason is because an SQL-Server is considered "easy to maintain". It's the worst assumption you can have.
From my life example I setup a backup of one of our SQL-Server systems. Normally I was hired to look after Oracle databases and that was my background. It was assumed that because the system based on an SQL-Server was delivered by a third company it was setup correctly without any need of any maintenance. The assumption was - this is a quote from the third party company and my manager - "we don't need a DBA to maintain the SQL-Server database, because we were told SQL-Server looks after itself".
Are you shocked? I bet you are. I was laughing.
And guess what? The system was recently upgraded from Sql-Server 2005 to 2008 and I was not involved. Two months later it was discovered that nobody setup any backup task.
Not only that. The previous old system backup was setup by a project manager, because he one was on a SQL-Server Admin course. He said it's very simple to set up. Well, we sat down together (I was an SQL-Server rookie at the time) and with a couple of clicks my colleague set it up. As the system is not sophisticated and it was accepted that in case of a loss of database we could recover from the last backup that would run at 1AM we used a very simple method and the backups were fine, because we frequently sent it to the third party for using and testing in a development environment. The problem was - why would one of the transaction logs be as huge as 500GB?
I'm not going into details because there is too much literature on the subject of making backups correctly and there's not much space here at the moment. I spent 2 months actually implementing and testing the whole backup strategy.
The main reason for my response is to make a point that you not only need to setup a backup, but you have to be very, very meticulous about it. You need to cover every aspect of the backup, check what type of backups you have (like Simple Recover Model vs. Full Recover Model).
You also need to understand what is a T-Log, what happens to it, why does it grow. You need to read about what happens to a T-log when you have the simplest recovery model. You would be surprised that even with a Simple Recover model you need to look after a T-Log. Many "accidental" DBA's probably don't take the time to study the concept behind how the backup really works.
I was once sent to attend an Oracle Backup and Recovery course and can you imagine how many days that was?