backup

A database backup intro for: Accidental DBAs, Juniors, Developers, Managers, etc.

Backing up SQL Server databases has been documented in thousands of books, Microsoft documentation and blog posts since the product was released in the 90s.

I’m not about to try and tell you HOW to backup your SQL databases. Rather, I want to take you through the basic considerations of deciding what to back up, when, how often, etc. in clear language that non-SQL folks can work through.

Some basic terminology:

• Database backup – sending all of the data, structure, objects, etc. into a different location (file/tape/virtual device). This is at the database level. Not Server or Instance wide.
• Device – formerly called a dump device, this is the target for your backup.
• Full backup – all of the contents, data and code.
• Differential – Everything that has changed since the last Full
• Transaction Log backup – All new entries in the .ldf file since the last t-log backup
• Recovery model – 3 choices, depending on how much data loss you can tolerate (Full, Bulk-Logged, Simple)
• RPO – Recovery Point Objective – the goal of what point you need to be able to recover to.
• RTO – Recovery Time Objective – how fast you need to be able to restore the data.

Decisions you really need to make…simplified:

• Do I need to back up this database?
• How often?
• What is my tolerance for data loss?
• How long can we be down if we have to restore?
• Where should I store my backups?
• How long should I keep them, based on business and regulatory requirements?
• Should I test my backups?

One by one:

Do I need to back up this database?

Most likely.  Unless this is a completely stale copy of read-only data and you can re-generate it with a few clicks, then yes…back it up.   Restoring from backup is generally much faster than re-generating.  System databases as well, in case you have to build up a new server.

How Often?

A very common standard in the industry to start with is to do a Full backup weekly, Differentials daily and T-logs throughout the day.   This speaks directly to your tolerance for data loss.   Adjust based on your specific business needs.  Slowly changing data can tolerate fewer backups than a highly active system such as order fulfillment.

What is my tolerance for data loss?

The more “nines” you want, the more expensive your backup solution will be.   Be realistic, and sort out if you can handle a minute, a day or a month.  I’ve seen all of the above as the appropriate answer.   There are not ANY generic backup solutions within SQL Server that will guarantee zero data loss.   That is a whole different conversation.

How long can we be down if we have to restore?

Commonly referred to by management in terms of how much money are we losing, due to lost productivity, lost sales, etc.   If you can be down for an hour and one person is affected, that is wildly different than your online store database being down for an hour when you are the size of an Amazon or eBay.  Be realistic.   Test restore your databases to determine the restore time.

Where should I store my backups?

Somewhere else!  Industry standard is to have “hot” backup file(s) local to the server (different drives than the database files), and copies of them somewhere else…tape, SAN, etc.  Again, the more resilient the solution needs to be, the more expensive.  Cloud storage is getting cheaper and cheaper all the time.

How long should I keep them, based on business and regulatory requirements?

I’ve had customers trash backups after a week, and one that had to keep 30 years.   True story.  Ask your auditors and legal…you don’t want to be the one that has to explain why your backups only go back a year when 3 were required by law.

Should I test my backups?

I forget who, but someone well respected in the SQL Server world said this years ago (paraphrasing): “If you don’t test your DR solution, you don’t have a DR solution.  You have a DR Hope.”  Do test restores on another server so you know the files are valid, and so you know how long the restores take.

Other considerations:

You do not need a 3rd party solution or expensive toolset to implement basic backups.   SQL Server has a Maintenance Plan wizard that will walk you through getting a basic plan in place.   The more complex you needs, the less likely this is to be sufficient.

Setting up backups is a bare minimum starting point for any organization. Depending on industry, organizational and regulatory requirements you may have to go above and beyond.   You may need to introduce compression, encryption, off-site storage, etc.

If your database/application/website uptime requirements have a lot of nines…you will be looking into HA (High Availability) solutions, of which there are a variety.  DR (Disaster Recovery) starts with backups at the database level.   Depending on the level of disaster you are trying to be able to recover from, you may need to protect more and more.   I start this conversation from small to large:  Database deleted, server dead, server room fire, building collapse, city offline.

This was not meant to be an exhaustive list of every possible backup and restore scenario in the SQL Server world.  But I do hope that if you are not a DBA you now how a basic understanding.  If you have a DBA, go ask her about your current strategy.  If you don’t have anyone doing this for you, it may be time to bring someone in to get you set up…and now you have a starting point for that conversation.

Please leave me any follow up questions in the comments!

Kevin3NF