Click here to monitor SSC
  • Av rating:
  • Total votes: 42
  • Total comments: 10
Grant Fritchey

SQL Server Backup and Restore for the Accidental DBA

24 May 2011

Not everyone who is tasked with the job of ensuring that databases are backed up, and easily restorable, consider themselves to be database administrators. If you are one of these  'Accidental DBAs' then Grant Fritchey has some good straightforward advice for you to ensure that things go well when a database has to be restored from backups

Here you are. Through some quirk of fate, you’re now responsible for your company’s databases. People are referring to you as the DBA. You’ve either volunteered or had the job thrust upon you. Either way, you’re expected to know what to do and, let’s face it, figuring out where to start is hard. Let me make a suggestion. The very first thing you should check on is the state of your backups. Yes, there are probably a million screaming issues and every single one of them is important, but if your database fails, which they do, and you have no backup…well, let’s just say that’s not a conversation you want to have with management.

So now you’re prepared to get started with backups, but you’re unsure of even where to begin. The good news is, this is a well-worn path. Database backups are not a mystery. Again, let me proffer several suggestions.

Start with the business

Getting your database back online and operational is first and foremost a business decision, not a technical one. Setting up the technical aspects of backup and recovery is relatively easy and extremely well documented, so your initial work needs to be with the business, understanding what you have to deal with there. This serves two purposes. First, you learn what you need to do and just how much infrastructure, planning, and work you have in front of you. Second, you can document what’s expected of you, so that if, at some later date, the business questions your technical decisions, you can point back to the discussions you had with them. Here are a set of suggested questions you might want to ask about any given database:

  • How important is this database to the company?
  • How much time and data can we afford to lose in this database?
  • Are there any regulatory requirements regarding keeping backups of the data I need to meet?
  • Are there any encryption requirements for data backups?

It’s the answers to these questions that begin to drive you down the path to setting up a backup and recovery plan for all the databases that you manage. The business will be able to answer some of these questions quickly and easily. Some they won’t. You may have to badger them to get the answers that you need, but you do need to get those answers. Also, you may not be able to work with some answers. For example, the most frequent answer to “How much data can we lose?” is “none.” While that may be true, attempting to set up a system with zero chance of data loss is extremely expensive and involves a lot more than simple backup and recovery. Usually you can get the business to agree that a day, or an hour, or 5 minutes would be reasonable expected losses.

Out of all this information you should create a set of documentation describing your backup processes in plain language. You may have a general process that’s applicable to all your systems and databases or you might have customized processes for individual databases or applications. Either way, write everything down.

Once you understand the task you have in front of you to satisfy the business, then you can begin to apply technology to the problem. Just remember, as your data expands, grows, and changes, so will the business. Plan on going back to them on a regular basis, at least once a year, to reassess and update your plans as needed.

Full Backups

All backup plans start with a full backup. A full backup is a low level copy of a database, not only the tables and data, but the indexes, their statistics, all the triggers and stored procedures, and everything that is stored in the database. There is no way for you to arbitrarily pick and choose objects to include in the backup, so the backup will always cover everything, and when you restore the backup, everything will be restored. There are lots of options for a full backup, but here are a few things to remember:

  • For recovery to a point in time, the full backup is the base.
  • Add CHECKSUM to partially validate backups as they occur.
    NOTE: This will have a noticeable performance impact on larger backup operations.
  • You should only backup the database after a consistency check (DBCC CHECKDB).
    NOTE: An exception to this would be if you use the backup itself for a consistency check.
  • The full backup does not truncate the log when the database is in FULL or BULK LOGGED recovery modes.

How often you run a full backup is very dependent on the needs of the business. In order to reduce the difficulty and time to recover a backup, for a full backup is recommended at least once a week, but only in conjunction with other backup processes. For small and medium sized databases, up to approximately 500gb, daily full backups might be a better plan.

Backups can be configured to stack multiple full backups into a single file or they can be configured to have each backup in an individual file. I recommend using individual files, preferably named by date as well as database name. It’s easier to see which backups are available and which are missing, if any. If a file becomes corrupt for some reason, you lose a single backup, not all of them.

Just remember, while there are all sorts of other types of backup operations, the foundation of most of them is the full backup. You will need to take that into account on most of your systems for most backup plans.

Log Backups

Some businesses are fine with only having a full backup and living with the possibility of data loss between whenever a problem occurs and the last full backup. Most businesses are reliant on much more up-to-date data. Because of this, you need to plan for the ability to recover a database to a point in time. This is possible through the use of the FULL recovery mode and a set of log backups. The database must first be set to either FULL or BULK LOGGED recovery. These modes of recovery cause the log to be kept intact until a log backup operation is performed. There are many different options around backing up logs, but a few should be noted:

  • In order for the committed transactions to be cleared, a log backup operation must be performed.
  • Log backups can use the CHECKSUM operation to partially validate the backup as it occurs.
    NOTE: This will have a noticeable impact on larger log backup operations.

The frequency with which log backups are taken must be determined by the business. Log backups are dependent on having a full backup in place as part of the recovery process. You must run regular log backups in order to truncate the committed transactions within the log. The minimum suggestion would be once per hour. Most businesses operate more in the neighborhood of once every fifteen minutes. I’ve even managed a system that ran a log backup once every five minutes. Less than that is counter-productive, because the amount of time needed to restore the data will be prohibitive.

Log backups, like full backups, can be configured to all go to a single file or to multiple individual files. Again, I recommend multiple individual files for the same reasons as before.

Automating the Process

The best way to automate your backup processes is to learn the T-SQL commands and go to work on setting up SQL Agent jobs yourself. Doing this gives you a very high level of control and flexibility. However, that requires a lot of time to develop a good set of knowledge, especially for the accidental DBA. Instead, you can take advantage of built-in automation processes called Maintenance Plans.

The Maintenance Plans have a wizard to walk you through the set-up. It will even schedule things for you. Here are a few notes about using Maintenance Plans:

  • Choose “Separate Schedule for each task” on the opening screen of the wizard.
  • Do not implement the Shrink Database step.
    NOTE: This is a long and involved discussion. For more, read this article.
  • Separate the backups from the other operations in order to minimize resource impact.
  • Enable “Verify Backup Integrity”.
    NOTE: This is the CHECKSUM option, so it may impact performance.

Once the wizard has created the Maintenance Plan, you can spend a lot of time and effort working with them directly through the GUI interface in SSMS. You have more control over these mechanisms than ever before. But, for extreme fine-grained control you will need to use T-SQL directly and work with SQL Agent to schedule the events.

SQL Agent can not only be scheduled to run any command you want, but it will retry the commands on an error. It can also be programmed to send you emails in the event of a failure. You can work with the scheduled items that you create yourself or even modify those created automatically for you through the Maintenance Plans wizard.

Full Restore

The most important part of setting up database backups is not the backup operation. The most important part of setting up database backups is being able to restore those backups. This means two things. First, that you verify that the backup files are valid , and can be restored. Second, that you know how to restore these files. Restoring databases is very much like being in a fight. If you practice boxing or some type of martial art you’ll know that you practice performing the moves in the air with other people slowly, and with other people at as close to full speed as possible without serious injury. This is all to learn how to move before you have to do it under stress. Restoring a database is exactly the same. There will be plenty of times that you may need to run a restore in a non-stressful situation, but you’ll remember the ones when the entire management team is standing in your cube waiting for you to save the company. That is not the time to be searching for a blog post on how to run restores.

Here are a few notes about restoring databases:

  • Practice, practice, practice.
  • Use the VERIFY ONLY command to partially validate backups.
    NOTE: This will only partially validate the file. The only true validation is a restore operation.
  • RESTORE FILELISTONLY will allow you to see the definition of files for the database that was backed up.
  • RESTORE HEADERONLY will give you information about the backup, other than the files.
  • MOVE allows you to create a copy of a database by moving the database files to other locations or other files.

Just remember that the full restore is not selective. You will get everything that was in the backup. If a particular row, table, or procedure is missing, that’s because it wasn’t in the backup.

Point In Time Restore

Because data may have changed since the last full backup, you configure your database to support log backups. When the time comes to restore the logs, you can restore to a specific point in time. Even more practice is required with the point in time recovery because so many more steps are involved. Here are some key points to remember:

  • Practice, practice, practice.
  • You must first restore a full backup, but it must be left in the Recovering state.
  • Until you reach the final log backup, each section of the log that is restored must be left in a Recovery state.
  • You must restore the logs in the order in which they were taken. You cannot skip sections of time.

Compression

One of the most important additions in recent years is the ability to compress backups. Compression helps conserve disk space but more importantly, it sacrifices some memory and CPU cycles to actually arrive at faster backup execution speeds. This is because less data is written to the disk and disks are the slowest part of the system. Because of this, unless your system is suffering extreme CPU or memory loads, it’s worth using compression on all of your backup operations.

The only problem with this is that backup compression is only available for certain versions of SQL Server. Compression was introduced with SQL Server 2008 Enterprise. When Service Pack #2 was released, they made compression available for SQL Server 2008 Standard. SQL Server 2008 R2 has the same restrictions, compression on Enterprise and Standard only. The Development version, since it’s effectively the Enterprise version, also has compression available. No other versions of SQL Server can create compressed backups or restore compressed backups.

Additional Backup Considerations

This introduction only covers the bare bones of standard backup and restore operations. There are an extremely large number of additional topics that you can add to the mix. This is a small sampling of the more important aspects:

Differential Backups

Full backups can take a lot of time, and log backups only involved transactions that have yet to be backed up. Differential backups provide a middle ground between the two. A differential backup retrieves all the committed data that has changed since the last full backup, and creates it in a backup, representing the difference between the current moment and the last full backup. Differential backups can then be used in restore operations to either bring the database up to the moment of the differential, like applying a log backup, or in conjunction with log backups. Like log backups, the full backup must be left in a recovering state in order to apply differential backups. For larger systems, these backup types can add additional flexibility to your disaster recovery planning.

File and File Group Backups

While it’s not possible to backup individual pieces of a database, such as just one table, there is a way to sort of get around this. It is possible to back up each file or file group independently of the others, either through a full or differential backup. These backups will back up everything that is on a given file or filegroup. You can then restore just the file or filegroup to the database. If only a single table is stored on that file group, then you can restore just that table. There are a number of restrictions around it, but again, for larger systems, this provides additional flexibility and power for planning your backup schedules.

Snapshot

Snapshot backups are not so much a part of disaster recovery as they are a mechanism of safety during specialized operations on the database such as deployments. Essentially a snapshot creates a moment in time copy of the database, very quickly. You can then, just as quickly, restore the snapshot to the database. These are not traditionally used in disaster recovery scenarios since the snapshot only contains changes made to the database. There is no way a snapshot can be restored unless the underlying database is already in place, the opposite of a disaster recovery scenario.

Copy Only

Since a full backup is the basis on which point in time recovery is built, whenever you take a full backup you introduce a new starting point for all the log and differential backups. Sometimes you might just want to take a backup of the database without affecting this chain. Using the COPY ONLY command allows you to do this.

Striping

You are not forced to write your backups to a single file on a single disk drive. You can set them up to stripe across multiple files on multiple drives. This is a way for larger systems to get a full backup in place and a mechanism for somewhat, but not drastically, faster backups.

Encryption

Most businesses require you to password-protect your systems. It’s also possible they may require you encrypt backups. To do this using SQL Server you have to encrypt the database, but then the backups will be encrypted as well.

Conclusion

Backups are a very important part of the DBA’s duties, but the most important part of backups is actually the restore. You need to know that you have good backups in place and that you can restore them. You must also work with the business in order to arrive at a good backup plan. Once you have it in place, make sure you validate your backups to ensure they work, because it is all on your shoulders. You’re the DBA.

The Checklist

  • Full backups running on a regular basis.
  • Full backup to individual files.
  • Use CHECKSUM with full backup.
  • DBCC run as part of full backup processing.
  • When database is in FULL or BULK LOGGED recovery, run log backups.
  • Log backup to individual files.
  • Use CHECKSUM with log backup.
  • Minimum frequency on log backups is one per hour.
  • Automate the backup process.
  • Practice restore operations frequently.
  • Validate backups by restoring database.
  • Use VERIFYONLY for partial validation of backups.
  • Use backup compression when available.
  • Document backup and restore processes.

Want to put Grant’s tips into practice?
If you’ve not yet tried SQL Backup Pro, you can download a free 14-day trial. Try it now to save yourself time and space on backup and restores.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 42 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Hats off
Posted by: Uma Mahesh (not signed in)
Posted on: Tuesday, May 31, 2011 at 1:20 AM
Message: Hi Grant Fritchey,

I Hats off to you for your idea to share good articles to the human world.

Subject: SQL Server Backup and Restore for the Accidental DBA
Posted by: saineymd (view profile)
Posted on: Tuesday, May 31, 2011 at 6:33 AM
Message: Excellent article Grant. Thank you for sharing.

Subject: Great Overview - Minor Problem/Technical Glitch
Posted by: Michael K. Campbell (not signed in)
Posted on: Friday, June 03, 2011 at 5:58 PM
Message: Grant,

Great overview of backups. I spend a lot of time sharing very similar information with many of my SQL Server Consulting clients. (I've also created a bunch of free videos that cover a lot of this info as well, at www.sqlservervideos.com/series/backups.)

However, I did notice one technical inaccuracy in your post.

In the section on 'Automating the Process' you mentioned the following:
Enable “Verify Backup Integrity”.
NOTE: This is the CHECKSUM option, so it may impact performance.

Sadly, that's not correct. The 'Verify Backup Integrity' option causes SQL Server to a) Backup, b) Run RESTORE VERIFYONLY against the media/backup you just created. It's a great option, but it's NOT the same as the CHECKSUM option.

The best practice is to do both (i.e. check/verify the backup using VERIFYONLY) and to use the CHECKSUM option during backups (Full, Differential, or Log) - because CHECKSUM will help detect errors/problems at the IO subsystem-level (i.e. previous write errors) and you can then use the STOP_ON_ERROR option in your BACKUP operation to make sure you're only saving GOOD backups (as you mentioned when you pointed out that backups are best after ensuring DBCC CHECKDB() has given them a clean bill of health).

I covered a bit of this in a presentation (sponsored by RedGate actually) a while back for SQL Server Magazine:
https://www.vconferenceonline.com/event/regeventweb.aspx?ID=248

And, one of the things that I pointed out in that presentation is that it's actually fairly hard to use the WITH CHECKSUM option with your backups in many 3rd party backup tools/solutions - because the option IS NOT exposed by most 3rd party UIs.

What I failed to cover/mention, is that SQL Server 'stinks' in this regard too - because there's no way of configuring a backup to use the CHECKSUM option either when using the Maintenance Plan/SSIS components (you have to drop to T-SQL).

Subject: Restoring specific tables?
Posted by: Keith Macdonald (not signed in)
Posted on: Monday, June 06, 2011 at 3:52 AM
Message: This maybe a seperate article, but in my humble opinion (from 20+ years of experience with SQL databases), 90% of the time we have to reach for the backup is not because of some catastrophic system failure, but usually because of some "sub-optimal" human activity. e.g. whoops, someone has just altered/deleted a live table, not the test version, they had logged onto the wrong server!

Having to do a full restore to get to a single table is often a massive effort. An article on options for selective restore of chosen database objects would be very useful to many people.

Subject: re: Glitch
Posted by: Grant Fritchey (view profile)
Posted on: Friday, June 10, 2011 at 2:51 PM
Message: Thanks Michael. Good catch on 'Verify Backup Integrity.'

Subject: re: Specific Tables
Posted by: Grant Fritchey (view profile)
Posted on: Friday, June 10, 2011 at 2:53 PM
Message: Funny you should ask because we've got an article coming out on just that topic shortly.

Subject: One other minor correction
Posted by: GilaMonster (view profile)
Posted on: Friday, August 26, 2011 at 9:01 AM
Message: Under 'Additional Considerations' you say

"Copy Only

Since a full backup is the basis on which point in time recovery is built, whenever you take a full backup you introduce a new starting point for all the log and differential backups. "

Full backups do not truncate the log, so the do not in any way affect the log chain. They reset the differential base, and a copy_only backup does not affect the differential base. I have a blog post on this, somewhere...

Subject: question about backup
Posted by: Helpme (view profile)
Posted on: Wednesday, January 23, 2013 at 10:22 AM
Message: Let me first say I am not a DBA. So if my question seems silly I apologize. I am actually feeling a little overwhelmed as I have become the go to guy with all related SQL work with no previous experience.
I believe I took my full backup in simply mode and not full (is there a way to check). I am getting ready to perform the restore of the full. Once the full restore is done I will take a differential and restore that on top of the full. My question is the following… because I did not run the backup in full mode will data be missing when I bring the database online after performing the differential backup and restore. Normally I would just take another full backup in Full mode, however time is of essence and the database is over 2.5tb. The last full backup took 5 days. I hope my question makes sense. Any insight is appreciated.
Thanks,

Subject: question about backup
Posted by: Helpme (view profile)
Posted on: Wednesday, January 23, 2013 at 10:38 AM
Message: Let me first say I am not a DBA. So if my question seems silly I apologize. I am actually feeling a little overwhelmed as I have become the go to guy with all related SQL work with no previous experience.
I believe I took my full backup in simply mode and not full (is there a way to check). I am getting ready to perform the restore of the full. Once the full restore is done I will take a differential and restore that on top of the full. My question is the following… because I did not run the backup in full mode will data be missing when I bring the database online after performing the differential backup and restore. Normally I would just take another full backup in Full mode, however time is of essence and the database is over 2.5tb. The last full backup took 5 days. I hope my question makes sense. Any insight is appreciated.
Thanks,

Subject: re: Question
Posted by: Grant Fritchey (view profile)
Posted on: Tuesday, January 21, 2014 at 7:57 AM
Message: Well, I'm sure you've resolved this by now, sorry for the late reply.

If you have full and differential backups, simple recovery doesn't really matter. You can only restore to the point of the last differential though.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.