slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Support PSS NC Microsoft PowerPoint Presentation
Download Presentation
Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Support PSS NC Microsoft

Loading in 2 Seconds...

play fullscreen
1 / 42

Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Support PSS NC Microsoft - PowerPoint PPT Presentation


  • 394 Views
  • Uploaded on

Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Support PSS NC Microsoft Corporation Agenda Importance of backup/restore Database architecture overview File/filegroups Recovery models Discuss transaction log (log sequence number)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Support PSS NC Microsoft' - Jeffrey


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Understanding Backup Restore in SQL Server 2000Pankaj AgarwalSQL Server Support EngineerSQL Support PSS NCMicrosoft Corporation

agenda
Agenda
  • Importance of backup/restore
  • Database architecture overview
  • File/filegroups
  • Recovery models
  • Discuss transaction log (log sequence number)
  • Discuss various types of backups available in Microsoft® SQL Server™ 2000
  • Tape backup
  • Media family and media sets
  • Restoring backups, partial database restore
  • Factors to consider when designing a backup strategy
  • Backup restore best practices
  • Case studies
  • Troubleshooting backup/restore errors
  • References
importance of backup restore
Importance of Backup Restore
  • Consider backup/restore planning to be the primary recovery mechanism
  • This method of recovery, in most situations, requires less time and provides the most reliable results
  • Unfortunately, in many cases, a regular backup strategy does not satisfy recovery needs, so even regular backups are unusable
  • A backup recovery plan should typically be considered from the restore perspective
    • If a restore has to be performed, the available time and resources would determine the time and effort spent on the backup strategy
database architecture overview
Database Architecture Overview
  • SQL Server databases have two kinds of files:
    • Data files (.mdf/.ndf extension)
    • Transaction log files (.ldf extension)
  • A SQL Server database is always made up of at least one data file and one transaction log file
  • Database files hold the data added to the database
  • Transaction log files keep a record of all modifications made to the database
database architecture overview 2
Database Architecture Overview (2)
  • There may be multiple files of each type in a single database
    • By default, a new database is created with one data file and one transaction log file
  • Data and transaction log files cannot be shared between databases
  • The first file for the database is called a primary data file (with .mdf extension)
    • All other data files are secondary and may be created with a .ndf extension
  • Data files are always grouped together into filegroups
  • Transaction log files cannot be members of a filegroup
files filegroups
Files/Filegroups
  • SQL Server database files are always grouped together in a filegroup
  • SQL Server creates a new database with a default filegroup called a Primary
    • Other filegroups may be added when you create the database or later on
  • A new SQL Server data file may be added to an existing filegroup when the new file is created
    • An existing file cannot be added to a new or existing filegroup
  • SQL Server uses a round-robin algorithm for space allocation when there are multiple files in a filegroup
files filegroups 2
Files/Filegroups (2)
  • Implementing filegroups in a SQL Server database presents an opportunity to use the filegroup backup/restore features
  • Using filegroups may improve performance
  • Implementing filegroups requires a complete physical design
  • Implementing filegroups for a production database may require downtime because when you move a table/index between filegroups, you must create a new table, move the data, and re-create the indexes
recovery models
Recovery Models
  • SQL Server 2000 introduced a new concept called recovery models
  • This model simplifies the database settings related to transaction log and bulk load operations
  • Three possible recovery models:
    • Full
      • All update operations are logged, including bulk load operations
      • Take transaction log backups for maximum recoverability
recovery models 2
Recovery Models (2)
    • Bulk Logged
      • Bulk load operations are logged partially
      • If a bulk load operation is carried out, perform a complete backup for complete recoverability
    • Simple
      • Transaction log is maintenance free
      • Bulk load operations are minimally logged
      • Perform complete and differential database backups
      • Transaction log backups are not permitted
  • For more information, see the WebCast:
    • SQL Server 2000 Database Recovery: Backup and Restore
transaction log
Transaction Log
  • Each SQL Server database contains a transaction log
  • The transaction log can have several physical files
    • By default, these files have the extension .ldf
  • The transaction log records all modifications made to the database to protect against an unexpected shutdown
    • The transaction log is also used when implementing Log Shipping and Replication
  • The level of detail logged for modifications depends on the recovery model designated for a particular database
  • The transaction log is internally organized into several logical blocks called virtual log files
  • The transaction log does not contain any data, it is only a record of the changes that were made to the data
slide11
Understanding the Contents of the Transaction LogInstructions to Drive from Lawrence Street to Court Street
  • Start here
  • Take first right
  • Take first left
  • Take first right
  • Take third right
  • Take first left
  • Take first right
slide12
Understanding the Contents of the Transaction Log (2)Instructions to Drive from Lawrence Street to Court Street
  • Start here
  • Take first right
  • Take first left
  • Take first right
  • Take third right
  • Take first left
  • Take first right

Instructions do not mean much without a starting point

Data in the database : Starting point

Transaction Log : Instructions

log sequence number
Log Sequence Number
  • The log sequence number (LSN) is a unique number that is designated to entries in the transaction log
    • Each entry is called a log record
  • A simple transaction may contain several log records, so all log records for a transaction are linked to each other
  • The LSN is system generated and cannot be assigned or viewed for a certain transaction
  • The concept of LSN is important to understand because it is used in backup restore operations
  • Each transaction log backup has a starting LSN and an ending LSN
    • Consecutive transaction log backups should have the same LSN value for the LastLSN and FirstLSN
different types of backups available in sql server 2000
Different Types of Backups Available in SQL Server 2000
  • SQL Server 2000 supports these types of backups for a database:
    • Complete backup
    • Differential backup
    • Transaction log backup
    • File/filegroup backup
    • File/filegroup differential
  • Transaction log and file/filegroup backups are not permitted if the database is in a Simple recovery model
complete database backup
Complete Database Backup
  • Backs up the whole database in a single image
  • Is self containing and does not depend on any other types of backup
  • May be restored to the same database or a new one on any server
  • Perform at regular intervals for all production databases
complete database backup 2

backup database db1

to disk = 'd:\db1_complete.bak'

db1

backup database db1

to tape = '\\.\tape0'

Complete Database Backup (2)
differential database backup
Differential Database Backup
  • Backs up all extents modified since the previous complete backup
  • Cumulative backup
    • Most recent backup contains changes from previous differential backups since the last complete database backup
  • Excellent alternative to performing a complete database backup for systems that are large and not modified very frequently
  • Enhanced in SQL Server 2000 – uses a bitmap to track modified extents
    • This speeds up the differential backups because the backup process hops directly to the modified extent indicated by the bitmap
differential backup 2
Differential Backup (2)

backup database db1

to device1

with differential

Differential Backup

Bitmap of

modified extents

transaction log backup
Transaction Log Backup
  • Backs up transactions since previous transaction log backup (or previous complete backup if no previous transaction log backups were performed)
  • Only permitted when the database is in the Full or Bulk Logged recovery model
  • Truncates the transactions that were backed up, unless the transactions are pending propagation to subscribers in transactional replication
  • May be used to recover the database to a point-in-time or to point-of-failure
  • All transaction log backups in the backup sequence should be available while performing recovery
transaction log backup 2

update table1

set field1 = 1

where field2 = 2

Transaction Log Backup (2)

Transaction Log

db1

backup log db1

to device1

filegroup backup
Filegroup Backup
  • Backs up individual files/filegroups in a database
    • Does not back up a portion of transaction log (like database complete backups and differential backups)
  • Requires transaction log backups to bring the recovered database back online
  • To recover from a filegroup failure using filegroup backups you must:
    • Perform a restore of the filegroup backup on the same database where the backup was performed
    • Apply all transaction log backups, including the tail of the transaction log
      • Q253817, “How to Back Up the Last Transaction Log When the Master and the Database Files Are Damaged”
filegroup backup 2
Filegroup Backup (2)
  • Partial database restore functionality permits you to restore only the backed up file/filegroup to a new database
  • Speeds up the restore process by requiring only the file/filegroup that was backed up to be restored instead of the whole database
filegroup backup 3

Primary Filegroup

fg1 Filegroup

fg2 Filegroup

Filegroup Backup (3)

backup database db1

filegroup = fg1

to device1

file filegroup differential backups
File/Filegroup Differential Backups
  • Combines the differential backups and the file/filegroup backups
  • A file/filegroup differential backup backs up all changes made to a particular file/filegroup since the last file/filegroup backup
  • Similar to any file/filegroup backup, it does not back up a portion of the transaction log, so it is required that all transaction log backups be applied if recovery is required for the particular file/filegroup
tape backup
Tape Backup
  • Microsoft SQL Server uses Microsoft Tape Format to store the backups
    • SQL backups may coexist with any other backups performed using MTF (for example, Windows backups)
  • Provides the added benefit of restarting an incomplete or interrupted backup/restore operation by using the RESTART option with the BACKUP and RESTORE commands
  • Introduces the concept of media sets and media families
media set and media family

device1

device2

device3

device4

Continuation Media

Initial Media

Media Family

Media Set and Media Family

Media Set

back up database db1

to device1, device2,

device3, device4

restoring databases
Restoring Databases
  • Partial database restore functionality permits you to restore only the backed up file/filegroup to a new database
  • Actions:
    • Restore a complete database backup
    • Apply any differential and transaction log backups
    • Recover the database
restoring databases 2
Restoring Databases (2)
  • If recovery is required only for a specific file/filegroup, and backups exist for this file/filegroup, you may take the following actions:
    • Perform a transaction log backup of the tail
      • Q253817, “How to Back Up the Last Transaction Log When the Master and the Database Files Are Damaged”
    • Restore the file/filegroup backup
    • Apply any file/filegroup differential backups
    • Apply all transaction log backups since the latest file/filegroup backup
      • Apply the tail of the transaction log from step 1
    • Recover the database
partial database restore
Partial Database Restore
  • Restores a file/filegroup on its own into a new database
  • Useful for situations where only a portion of the database is required during recovery
  • Use the PARTIAL option with the RESTORE Transact-SQL command
  • Restores all tables in the primary filegroup
  • See Q281122, “Restore File and Filegroup Backups in SQL Server”
partial database restore 2

restore database db2

filegroup = fg1

from device1

with partial

Primary Filegroup

Primary Filegroup

fg1 Filegroup

fg2 Filegroup

Partial Database Restore (2)

fg1 Filegroup

fg2 Filegroup

fg2 marked offline

factors to consider when creating a backup strategy
Factors to Consider When Creating a Backup Strategy
  • How much information can you afford to lose? How critical is the information in the database?
  • What is the availability of resources governing the backup/restore strategy?
    • For example: finance, time, administration
  • What is an acceptable time period to perform maintenance when a disaster happens?
  • Is this a 24x7 database?
  • How large is the database? What type of data is stored in this database?
    • For example: OLTP or warehouse?
backup restore best practices
Backup Restore Best Practices
  • Perform regular restores of backups
    • Currently, the only way to verify the restorability of a backup is to actually restore it.
    • If possible, use a redundant computer. This computer may be used as a standby computer if the production server has unexpected hardware failure.
  • Schedule time for a simulated disaster
    • This will give maintenance team members a fair idea of how well the current disaster recovery strategy holds up.
backup restore best practices 2
Backup Restore Best Practices (2)
  • Document the recovery procedures
    • These procedures should be easily accessible by anyone involved in maintaining the server.
  • Periodically check the Windows NT® event logs for any hardware problems
    • These may be reported by SQL Server, but it might be too late.
backup strategy case study 1
Backup Strategy Case Study 1
  • Consider a 24x7 .com database (about 5 GB) on SQL Server 2000
  • Perform a complete database backup on the first Sunday of every month between 1:00 A.M. and 4:00 A.M.
  • Perform a differential backup every night at 1:00 A.M.
  • Perform transaction log backups every 30 minutes around the clock
backup strategy case study 1 2
Backup Strategy Case Study 1 (2)
  • Recovery path will involve loading the complete backup, the latest differential backup, and all transaction log backups following the differential backup
  • Provides a means to perform point-in-time restore and point-of-failure restore
    • Point-of-failure restore is performed if the tail of the transaction log is backed up before restoring the complete backup
backup strategy case study 2
Backup Strategy Case Study 2
  • Consider a data warehousing database (about 100 GB) created with three filegroups and five files per filegroup
  • Database is loaded with data every month on the first Sunday of the month (data is used primarily to create analysis services cubes)
  • Perform complete database backup after the data is loaded on the first Sunday of the month
  • Alternatively, differential backups could be performed every Sunday, with a complete backup being performed once every month
backup strategy case study 2 2
Backup Strategy Case Study 2 (2)
  • If this database was modified regularly, then filegroup backups (in conjunction with transaction log backups) could be used
  • Filegroup1 could be backed up on Monday and Thursday; Filegroup2 on Tuesday and Friday; and Filegroup3 on Wednesday and Saturday
  • Back up transaction logs as frequently as you want; however, these would be mandatory after each filegroup backup
troubleshooting backup restore
Troubleshooting Backup Restore
  • Check SQL Server errorlogs, Windows NT System and Application event logs, and scheduled job output to make sure that regular backups completed successfully
  • If backups are unsuccessful, refer to any I/O errors in the SQL errorlogs for more information
    • Also, look at the Windows NT System and Application logs to see if there are any errors
  • Test the integrity of the backups by performing a restore to a different server
troubleshooting backups
Troubleshooting Backups
  • Check the basics:
    • Drive space
    • Disk subsystem errors
    • Tape drive errors
  • Regularly restore the backups and notice any errors raised while restoring
  • If network backups are being performed, look for network errors in the Windows System and Application eventlogs
  • Regulary look at SQL Server errorlogs and Windows NT eventlogs for errors that may indicate backup failures
troubleshooting restore
Troubleshooting Restore
  • The most common problem not being able to restore the backup file
    • The only way to verify this is to regularly restore backups using the same medium that you would use in disaster recovery
  • If the message from SQL Enterprise Manager is not descriptive, perform the restore operation from the SQL Query Analyzer window to get more information
references
References
  • KB articles
    • Q307775, “INF: Disaster Recovery Articles for Microsoft SQL Server”
    • Q221465, “INF: Using the WITH MOVE Option with the RESTORE Statement”
    • Q231347, “INF: SQL Server Databases Not Supported on Compressed Volumes”
    • Q253817, “INF: How to Backup Last Transaction Log When Files are Damaged”
    • Q281122, “INF: Restore File and Filegroup Backups in SQL Server”
  • SQL Server Books Online
  • Inside SQL Server 2000