Checklist: How to archive SQL Server backups

Archiving database backups is probably not high on most DBA to-do lists, but, unfortunately, it's one of those things that has to be done. Some reasons why DBAs may begin archiving data include:

• "I was told I have to do it."
• "Everyone else is doing it, so I figured I should do it, too."
• "We have to meet legal and compliance standards."
• "It just makes good business sense."

Whatever the reason may be, good or bad, I will discuss some points to consider when archiving your database backups for long-term storage. In most cases, long-term archiving is not the DBA's responsibility, but someone in the organization needs to make sure it happens. So you should be aware of the process in place and ensure all of the pieces you need are there when you need to recover an archived database.

In all honesty, you will probably never use an archived backup to restore your production systems, but I bet there will be a time when you need to recover some data from an archived backup. I once had the chore of recovering data from two years of archives needed for auditing. When the time came to recover the data, several things had to take place.

1. Get the tapes.
2. Restore the backups.
3. Pull out needed data

In the following two checklists, I'll first walk you through the basic steps of recovering from an archive and then I'll review long-term database archiving considerations.

Checklist: How to restore from archive

Step 1: Get the tapes

Since all the tapes were stored offsite using a company that specializes in offsite tape management, we were able to retrieve them all. However, one thing we did not know was

only one complete copy per month was long-term archived. We assumed all data was recoverable. Not being part of the process, we didn't learn this until the data was needed.

Step 2: Restore the backups

During the restore, we were lucky all tape formats were identical, but faced problems reading some of them. We tried several tape drives, but some tapes were just unusable.

Step 3: Pull out needed data

Querying the data was the easy part. Although we had to stage when the databases were restored, this step was completed without a hitch.

Although this exercise probably doesn't happen often, there are times that it does so being prepared is key. The situation could have been a lot worse with different versions of SQL

in the mix, different tape formats, different backup products, etc.

The following are points to think about in your long-term archive process. Some of this may already be in place, and you just need to familiarize yourself with what's occurring, or you may need to implement a completely new solution.

Checklist: Long-term archiving considerations

Media selection

You must select the media that you will use for long-term archiving of your database backups. Some options include CD, DVD, tapes, Internet online storage and disk-to-disk.

storage. All of these options have their pros and cons. Determine which works best for your environment, which will depend on the size of your databases, your budget and how high

tech you can make the solution.

Storage options

You must also consider where data will be stored. All of your options have different costs, while some are more low tech and others are more mainstream. Some options include:

Online storage Internet: Several companies offer online backups via the Internet.

Internal hardware: Use your own local servers. Internal hardware at a different location: Copy files to a different site.

Offsite storage facilities Offsite tape vaulting: Ship tapes to an offsite facility for storage

Another office location: Ship tapes to another office With you: Take tapes with you.

Storage of software media

There are several backup solutions for SQL Server including Idera SQLsafe, Quest LiteSpeed for SQL Server, Red-Gate SQL Backup, as well as SQL backup agents for

Symantec BackupExec, CA Brightstor, etc. Keeping a copy of software used to create your SQL Server backup could prove useful in the future. You may also need to store

various versions of SQL Server used as well. At this point you can use SQL Server 2005 to restore 7.0, 2000 and 2005, but it will not restore a 6.5 or earlier database. This may be

an issue in the future, so having the media available with the tapes vaulted some place could be critical.

How much needs to be stored

This is a good question to ask business users. As mentioned above, long-term backups will probably be used to audit for legal or compliance regulations. Data changes on

a daily basis in your databases, so how quickly your data changes and whether you physically delete data could determine how often data should be archived and remain

archived. In the exercise above I was only able to recover one backup per month -- and some backups proved useless. Check with your business users and put together a

service level agreement (SLA) that outlines what can and can not be recovered from your archived solution.

Backup types

Full backups are a must for long-term storage, but depending on what types of other backups you are doing (differential, transactional and filegroup) and if they are archived,

they may not make a bit of difference. The reason for this is that you will need a full backup to restore differentials and transactional backups, and you will need transactional

backups to restore filegroup backups. So if only a full monthly backup is saved, these file backups will probably never be used for restoring archived versions of your data.

Protection

Protect data in your backup files, whether onsite or offsite. There are several options you can take to prevent data misuse, but the best bet is to start with either a password-protected

backup or, better yet, an encrypted backup. When using native SQL Server backups and writing them to disk you are leaving yourself exposed to a possible security hole. This

is where third-party backup tools to encrypt your backup files come into play. Most if not all off them offer some type of backup encryption.

Costs

Cost is always something to consider. You may have the best possible approach to archive data, but your budget may not allow for it. Review your options and choose a solution that

matches your wallet. It is better to have some type of process in play than none, yet many companies still leave themselves vulnerable by doing nothing.

Implement

After you have determined your plan of attack, you need to implement your approach. At this time you should pull together documentation on how things work, checklists to follow and

procedures to recall your archived data copies. Take the time to understand what can and can not be done and communicate this to your business users.

Test

You may never have to retrieve data from an archived copy of your database, but it is better to be prepared versus finding out you are unable to do something that could have been

prevented if you only tested. Put together a test drill in which you randomly pick some tapes and/or files to recall, go through the restore process and see how successful your

approach is. It is difficult to determine what will happen in the future and if older tapes are still usable, but it is wise to have at least walked through the drill to fix any problems that can be fixed now.

Summary

Retrieving old archived copies may not be a primary concern to you as a DBA, but you must be thinking about these things. Although long-term storage is typically performed by a different group -- that disconnect could become disastrous if there is a compelling reason to restore older data. Take the time to investigate how long-term storage actually works and see if you will be prepared if and when the time arises.

SQL Server Checklists offer you step-by-step advice for administering, tuning and managing your SQL Servers. E-mail the editor to suggest additional checklist topics.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

More information from SearchSQLServer.com