The Five W 's of Database Restores
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.
Who
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 database restore.
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 restore.
What
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.
When
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.
Where
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.
Why
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.
Conclusion
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.
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.
Who
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 database restore.
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 restore.
What
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.
When
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.
Where
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.
Why
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.
Conclusion
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.