 |
|
|
Home > Step 1: Planning your SQL Server backup |
|
|
|
|
|
|
|
Planning is the most critical stage, but it's the one that is often done on the fly. When facing a new situation, the choices below are often evaluated very quickly and then a decision is made. Although that approach addresses the issue at hand, it leads to inconsistencies in your overall process.
| Item |
Questions |
| Backup Type |
First, determine what types of backup you want to perform -- full, differential, transaction log or a combination of those types. The type you select will determine the recovery model for your database. Click here for help selecting a backup model. |
| Maintenance Plans, Scripts or Third-Party Approach |
Decide whether you are going to use maintenance plans, write your own backups scripts or use third-party software that has SQL Server backup capabilities. |
| Schedule |
You also need to consider when you will back up your databases. Again, this decision will be based primarily on the types of backup you will be performing. |
| Access Rights |
Identify the user account that will be used to perform your backups once the plan is implemented. Oftentimes, the SQL Server service account does not have the same rights the admin DBA might have, so make sure you identify the account and that it has the proper permissions and rights to back up the databases and write the backup file. |
| System / User Databases |
Think about which databases you want to back up. All databases that cannot be re-created from another source should be backed up. The more critical the data, the more frequent the backups should occur. For system databases, back up at least the master and msdb databases. It is critical to back up system databases because they do not exist in any other place. Whenever a change to a system database is made, a backup should be taken. However, it is much easier to just schedule the backups on a set schedule rather than try to remember when changes were made and then run a backup. |
| Tape or Disk |
Determine if you will back up directly to tape or to disk then tape. Some third-party backup tools allow you to write directly to tape, but it is often a better idea to write to disk first and then archive to tape. Writing your backup file to a disk subsystem is the fastest and best method for doing SQL Server backups. In most cases, disk is much faster than tape and it provides you with a recent backup copy in case you need to restore. For emergencies, usually the latest backup is required, so keep it close at hand. |
| Safeguard Media |
Do you need to encrypt your database backups? Are backups secure when they are stored offsite? These are things to think about after the backup has been completed. The backup is a complete set of the production database, so safeguard both your production system and any backup copies of the database. |
| Native Backups or Third-Party Tools |
Decide if you want to use native backup formats or third-party proprietary formats. Several tools on the market now allow you to compress your backups, but often they are often stored in a proprietary compressed format: The advantage to using third-party tools is that they allow you to compress your backup files on the fly, which means backups will finish faster and be smaller than native backups. The disadvantage is that, because they are in a proprietary format, you must use the same software to restore the file. As long as you have the third-party software on hand, this should not be a problem -- and the benefits far outweigh the risks. It is good practice to store this software on the same archive tapes along with your backup files. |
| Create Priority List |
When backups are put in place, people often think of just the server on hand. You must think about all your servers and, if there was a failure, which servers or databases need to come online first. It is a good idea to classify all of your servers and databases, so when the time comes to execute a recovery, you tackle your most important servers first. |
How to properly back up a SQL Server
 Home: Introduction
Step 1: Planning your SQL Server backup
Step 2: Developing and testing your SQL Server backup
Step 3: Implementing your SQL Server backup
| ABOUT THE AUTHOR: |
|
Greg Robidoux
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.
Copyright 2005 TechTarget
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
| |
