Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Suppo

1
Understanding Backup Restore in SQL Server
2000Pankaj AgarwalSQL Server Support
EngineerSQL Support PSS NCMicrosoft Corporation
2
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

3
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

4
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

5
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

6
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

7
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

8
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

9
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

10
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

11
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

12
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
13
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

14
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

15
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

16
Complete Database Backup (2)
17
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

18
Differential Backup (2)
backup database db1 to device1 with differential
Differential Backup
Bitmap of modified extents
19
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

20
Transaction Log Backup (2)
Transaction Log
db1
backup log db1 to device1
21
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

22
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

23
Filegroup Backup (3)
backup database db1 filegroup fg1 to device1
24
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

25
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

26
Media Set and Media Family
Media Set
back up database db1 to device1,
device2, device3, device4
27
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

28
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

29
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

30
Partial Database Restore (2)
fg1 Filegroup
fg2 Filegroup
fg2 marked offline
31
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?

32
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.

33
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.

34
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 100 A.M. and 400
  A.M.
• Perform a differential backup every night at 100
  A.M.
• Perform transaction log backups every 30 minutes
  around the clock

35
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

36
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

37
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

38
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

39
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

40
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

41
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

42
(No Transcript)