In this article I’m going to
deviate from my normal “How To” type of articles to discuss what to consider
when you restore a database. I will discuss the kinds of things each DBA
should go through as they are requested to perform a database restore. The
things that need to be considered fall into five different categories: Who,
What, When, Where and Why. I will discuss each one of these categories and how
to use the five W’s to help guide you in making the right choices when
performing a database restore.
The first thing to consider is
to ask yourself a couple of “who” questions. Who is requesting the database restore? Who will be impacted by the
restore? Who needs to know that a restore is occurring, or has occurred?
And who will be performing the restore?
When answering the “Who is
requesting the database restore?” question, there are a few things you need to
consider. You need to make sure the person requesting the restore is
authorized to request a restore. You should make sure that the restore request
is coming from the application/database owner, or someone the application/database
owner has authorized to request a database restore. You need to make sure the
person requesting the restore understands the system and the issues associated
with restoring the database or databases. It is your responsibility as a DBA
to educate the person requesting the restore about any problems or situations
that you know of that might arise from performing a database restore.
In addition, you need to
make sure the appropriate management authorization should occur if required. You
need to make sure that proper channels of communication are performed. For
instance if your company has a policy that the operation manager, CIO, and the
Director need to be notified and authorize an application outage that will occur
during the day, then you better notify these individuals prior to performing a
Prior to performing a restore,
you should identify all the applications that will be impacted by the restore,
as well as the customers that will be impacted. Once you have the list of
individuals that will be impacted, you need to notify them regarding the
database restore. Preferably, this communication should happen prior to the
restore if possible. This communication should identify what is being
restored, what data might be lost, if any, when the restore will occur, and who
will be affected by the restore.
Lastly, you need to identify
who will be needed to perform the restore. Depending on what is being restored,
potentially multiple individuals might need to perform the actual restoration.
For instance if your database backups have already been copied off to tape, and
deleted from disk, you might need the operations staff to first restore the
database backup files from tape, before you can actual perform the database
Next, you need to identify what
is being restored. When you are asked to perform a restore, you need to
identify exactly what is being restored. Do you need to restore a complete
database, or a number of databases? Or does just a single object within a
database need to be restored? Knowing what needs to be restored will help you
formulate a method or strategy for your restore process.
Not only do you need to
identify what needs to be restored, but you need to establish a point in time
to restore to. Are you going to restore to a specific day, or week? Or are
you going to restore to a specific time of day? The answer to these questions
will determine if you need to use a full, differential and/or transaction log
backup to perform your restore. Knowing the point in time to restore to will help
you determine what backup files you will need. Potentially some of the backup
files might be on tape, and/or offsite. Knowing the disposition of each backup
file will help you determine how long it might take to get all your backup
files together so you can perform the actual restore. If your backups are
offsite, your restore might take some time to perform, because someone will
need to retrieve the backup tapes from your offsite storage location.
The last thing you might
need to consider is what tools/scripts you might need to perform the restore.
If you are restoring a single backup, then maybe you can just use Enterprise
Manager to perform the restore. Although if you have a more complicated
restore that requires a full, differential and a number of transaction log
backups, then possibly your best approach to doing a restore might be to build
a T-SQL script.
As you are discussing what
needs to be restored, you need to ask when should the restore be performed. Most
likely, the restore will need to be performed right away, although potentially
the restore might need to occur at a specific time. It is always wise to ask
the person requesting the restore when they would like it to occur. Also
depending on your environment, it might be wise to consult or at a minimum
notify management when you are planning to perform a restore.
Once you have decided when
the restore will occur, you need to determine how long it will take to perform
the restore, and what kind of impact will the restore cause. You should
communicate the timeframe needed and the impacts the restore might cause to all
individuals that might be affected by the restore. This communication needs to
identify what data will be lost, if any, and how access to the system will be
restricted and/or impacted during the restore operation. Letting staff know up
front when a restore will occur and what will be the impacts, will help
minimize questions and concerns associated with doing a restore. This
communication will also give staff an opportunity to validate and approve the
restore requirements and impacts.
Now that you know the who,
what, and when for your restore operations you now need to determine where the
restore is to take place. If your shop has many environments, like production,
quality assurance, testing, and development, then you will need to determine in
what environment you will be performing your restore. Potentially you might be
taking a production backup and restoring it to your development environment so
application programmers can have the latest copy of production data to use.
Lastly, you should consider why
the restore is being performed. The person requesting the restore should
provide the reason why the restore is being requested. It is important to
understand why you are doing the restore. Depending on the situation that
caused the restore request, you need to determine if there is a way to prevent
this situation from occurring in the future. By knowing what caused
the restore request, you can determine if there is a way to prevent the
situation from occurring again. You should be proactive and try to provided or
build a preventive mechanism, if possible.
By asking the simple who,
what, when, where and why questions prior to performing a database restore you will
have the appropriate information to help make the right decision regarding a
restore request. Answers to these questions will help you determine what kind
of communication you need to perform and how customers and applications will be
affected. You can uses the answers from the five W’s to fully understand a
restore situation prior to actually performing a database restore. It is
easier on the nerves to be pro-active when it comes to performing a database
restore then being re-active to unexpected situations that might come up when
performing a restore without the appropriate forethought.