Transcript 17_backup
IMS 4212: Data and Database Administration Data & Database Administration • Database Backup • Backing Up in SQL Server – The Transaction Log – Differential Backup – Full Backup – Copying the Database Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 1 IMS 4212: Data and Database Administration Backing up Databases • Why should we back up? • What are the tradeoffs with backing up? • What is the worst thing that could happen to your data? – How likely is it to happen? – What would it cost? • What else could happen? – Likelihood? Cost? • What backup strategies are available? Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 2 IMS 4212: Data and Database Administration Backing up Databases • Database Backup Strategies – Mirroring – Offsite mirroring – Logging transactions – Differential backups – Rollback/Commit strategies for business transactions – SQL Server backup Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 3 IMS 4212: Data and Database Administration Mirroring • Mirroring is the practice of replicating transactions onto two different storage systems as they occur • Some DBMS will automatically implement mirroring • Can also be implemented at the application level – Create two connection objects to different DB – Execute each Update/Insert/Delete against both connections – Only query one – Second copy is available for full use if the primary becomes unavailable – Either copy can be restored from the other Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 4 IMS 4212: Data and Database Administration Off-Site Mirroring • Off-Site Mirroring is just mirroring with one copy of the DB located in a geographically distant location – "Distant" should be far enough away to not be affected by the same disaster • Network speed must support multi-site operations • Reverting to the remote copy for routine operations may be troublesome for queries with large result sets Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 5 IMS 4212: Data and Database Administration Backup in SQL Server • • • • The Transaction Log Differential Backup Full Backup Copying the Database Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 6 IMS 4212: Data and Database Administration The Transaction Log • The transaction log is a separate physical file created when the database is created • Every DB event since the last backup is recorded (mirrored) in the transaction log (as SQL statements) • In the event of failure: Backed Up Database + Dr. Lawrence West, Management Dept., University of Central Florida [email protected] Transaction Log = Database At Failure Point 7 IMS 4212: Data and Database Administration The Transaction Log (cont.) • The TL should be on a separate physical drive from the database – Faster reads and writes – Less likelihood of losing both data sources at once • The TL may also be backed up (which resets the active TL) Backed Up Database + Dr. Lawrence West, Management Dept., University of Central Florida [email protected] Transaction Log = Database At Failure Point 8 IMS 4212: Data and Database Administration The Transaction Log (cont.) Transaction Log • Making more frequent (smaller) TL backups: – Reduces the time needed for backing up – Keeps the TL physical file size smaller – Increases (slightly) the time needed to restore the DB Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 9 IMS 4212: Data and Database Administration Differential Backup • A differential backup is a backup of the database since the last time a full or differential database backup was performed • Smaller than a full backup • Good when some parts of the DB are more volatile than others Backed Up Database + Differential Backup = Database At Diff. B/U Point • TL backups can be applied on top of a differential backup Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 10 IMS 4212: Data and Database Administration Full Backup • A Full Backup backs up the entire database • Takes longer than the other backup strategies (obviously) • Creates a larger file size • May be used to copy or move a database Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 11 IMS 4212: Data and Database Administration Backup Strategies • How frequently is each type of backup performed? – Transaction log backup – Differential backup – Full backup • Where is data kept? • What is time needed to perform the backup – And performance degradation while backup is in progress • How long to restore the DB to full operating condition – Hardware replacement – DB backup Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 12 IMS 4212: Data and Database Administration Managing Backup Media • Know what media must be restored in what order in the event of a failure • Recycle media when its contents have been made redundant – Recommended to keep one backup of a backup • Manage age of media—destroy when too old • Manage storage of media • Manage media labeling Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 13 IMS 4212: Data and Database Administration Managing Backup Media (cont.) • You should manage backup media so that the same disaster does not burn up your operating data and your backup • At a minimum backup media should be removed from the room – Building • City – State • The abandoned coal mine repository Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 14 IMS 4212: Data and Database Administration Backing Up in SQL Server • Available in Tasks menu • You may select a backup file • Backup devices (tape drives) will be recognized if installed Dr. Lawrence West, Management Dept., University of Central Florida [email protected] 15