Backup and Recovery in SQL Server - PowerPoint PPT Presentation

About This Presentation
Title:

Backup and Recovery in SQL Server

Description:

RESTORE LABELONLY: summarizes information about the backup set. ... RESTORE FILELISTONLY: returns the associated data files in relation to a specific backup device. ... – PowerPoint PPT presentation

Number of Views:2910
Avg rating:3.0/5.0
Slides: 33
Provided by: Robe477
Category:
Tags: sql | backup | recovery | server

less

Transcript and Presenter's Notes

Title: Backup and Recovery in SQL Server


1
Backup and Recovery in SQL Server
2
High Level View
3
Planning
  • Needs assessment
  • Management expectations
  • Network architecture
  • Personnel

4
Implementation
  • Training
  • Whos involved in the process?
  • Testing
  • Failsafe your process
  • Remember what the purpose of backups are!

5
The Role of Back-ups
  • Data Recovery
  • Data Migration

6
Backing up your data
7
Backup Basics
  • Data Files
  • .MDF
  • .LDF
  • Backup Devices
  • Create
  • Delete

8
Recovery Models
  • Simple
  • Most restrictive model.
  • Cant complete transaction log backups.
  • Good if you only want to recover to your last
    full backup.
  • Bulked Log
  • Ability to recover to the end of a full backup
    or the end of a transaction log.
  • Lose the ability to restore to a specific point
    in time.
  • Full
  • Allows full, differential, transaction log and
    filegroup recoveries.
  • Provides the highest level of data protection.
  • Point in time restores.

9
Types of Backups
  • Full Database
  • Differential Database
  • Transaction Log
  • Filegroup

10
Full
  • Completes a full backup up the database right up
    to the point where the database finishes.
  • Backs up all tables, indexes, system tables and
    the database object in those tables
  • Striping example

11
The Backup Database Statement
  • BACKUP DATABASE database_name
    _at_database_name_var TO lt backup_device gt
    ,...n WITH      BLOCKSIZE blocksize
    _at_blocksize_variable      , DESCRIPTION
    'text' _at_text_variable      ,
    DIFFERENTIAL      , EXPIREDATE date
    _at_date_var          RETAINDAYS days
    _at_days_var      , PASSWORD password
    _at_password_variable      , FORMAT
    NOFORMAT      , INIT NOINIT
         , MEDIADESCRIPTION 'text'
    _at_text_variable      , MEDIANAME
    media_name _at_media_name_variable      ,
    MEDIAPASSWORD mediapassword
    _at_mediapassword_variable      , NAME
    backup_set_name _at_backup_set_name_var     
    , NOSKIP SKIP      , NOREWIND
    REWIND      , NOUNLOAD UNLOAD
         , RESTART      , STATS
    percentage

12
Differential Backup
  • Creates a backup of the changes made to the
    database since the last full backup
  • Does not keep track of individual transactions so
    it does not provide the ability to restore to a
    point in time

13
The Transaction Log
  • The transaction log keeps a log of each
    transaction giving a DBA the ability roll forward
    or roll back to a specific point.
  • When the transaction log is backed up the log is
    truncated.
  • Transaction log backups are scheduled in
    intervals typically 5 to 30 minutes.
  • Backs up the transactions since the last Full
    Backup, Differential Backup or Transaction Log
    Backup.

14
Backup Log Statement
  • BACKUP LOG database_name _at_database_name_var
        TO lt backup_device gt ,...n     
    WITH          BLOCKSIZE blocksize
    _at_blocksize_variable          ,
    DESCRIPTION 'text' _at_text_variable
             , EXPIREDATE date _at_date_var
                 RETAINDAYS days _at_days_var
             , PASSWORD password
    _at_password_variable          , FORMAT
    NOFORMAT          , INIT NOINIT
             , MEDIADESCRIPTION 'text'
    _at_text_variable          , MEDIANAME
    media_name _at_media_name_variable         
    , MEDIAPASSWORD mediapassword
    _at_mediapassword_variable          , NAME
    backup_set_name _at_backup_set_name_var
             , NO_TRUNCATE          ,
    NORECOVERY STANDBY undo_file_name
             , NOREWIND REWIND
             , NOSKIP SKIP         
    , NOUNLOAD UNLOAD          ,
    RESTART          , STATS percentage
        

15
Filegroups
  • Allow a user to place tables, indexes and other
    database objects into specific database files.
  • Can be backed up and restored individually or in
    groups.
  • Useful when time restraints prevent full backups.

16
Verifying the Backup
  • Does not check the consistency of the database
    (i.e. DBCC CHECKDATABASE).
  • Verifies the integrity of the database backup and
    checks for corruption.
  • Makes sure all the data in the backup set is
    readable.

17
Restore Verify Only
  • RESTORE VERIFYONLY FROM lt backup_device gt
    ,...n WITH      FILE file_number
         , NOUNLOAD UNLOAD      ,
    LOADHISTORY      , PASSWORD password
    _at_password_variable      ,
    MEDIAPASSWORD mediapassword
    _at_mediapassword_variable      ,
    NOREWIND REWIND

18
Job Scheduling
  • The most widely used way complete day to day and
    week to week scheduled backup operations.
  • Creation of jobs though Enterprise Manager.
  • SQL Server Agent must be started.

19
Database Maintenance Plans
  • Expanded job scheduling abilities.
  • Creates jobs managed by the SQL Server Agent.
  • Can schedule integrity checks, reorganize indexes
    and delete unwanted database backups.

20
Storage
  • Using a consistent method of keeping track of
    your backups.
  • Using the system backup tables in the msdb
    database.
  • Archiving backups to tape and different
    considerations for different industries

21
Recovering your data
22
Restore scenarios
  • Data Migration
  • Development
  • Testing
  • Disaster Recovery
  • Recovering Backups
  • Standby Servers

23
Restore Basics
  • Make sure you are using the correct backup set.
  • RESTORE LABELONLY summarizes information about
    the backup set.
  • RESTORE HEADERONLY returns information about
    each item in the backup set including the order
    in which the backups were made.
  • RESTORE FILELISTONLY returns the associated data
    files in relation to a specific backup device.

24
Restoring a Full Backup and a Differential Backup
  • Restore a full backup.
  • Restoring a differential.
  • The WITH RECOVERY/NORECOVERY clause.
  • Files must be restored in order.

25
The Restore Statement
  • RESTORE DATABASE database_name
    _at_database_name_var FROM lt backup_device gt
    ,...n WITH      RESTRICTED_USER     
    , FILE file_number _at_file_number
         , PASSWORD password
    _at_password_variable      , MEDIANAME
    media_name _at_media_name_variable      ,
    MEDIAPASSWORD mediapassword
    _at_mediapassword_variable      , MOVE
    'logical_file_name' TO 'operating_system_file_name
    '              ,...n      ,
    KEEP_REPLICATION      , NORECOVERY
    RECOVERY STANDBY undo_file_name     
    , NOREWIND REWIND      ,
    NOUNLOAD UNLOAD      , REPLACE
         , RESTART      , STATS
    percentage

26
Restoring a Full Backup and Tlogs
  • Must restore files in order
  • Provides the ability to restore to a point in
    time
  • Provides the ability to restore to a marked
    transaction

27
Restore Log Statement
  • RESTORE LOG database_name _at_database_name_var
    FROM lt backup_device gt ,...n WITH
         RESTRICTED_USER      , FILE
    file_number _at_file_number      ,
    PASSWORD password _at_password_variable
         , MOVE 'logical_file_name' TO
    'operating_system_file_name'             
    ,...n      , MEDIANAME media_name
    _at_media_name_variable      ,
    MEDIAPASSWORD mediapassword
    _at_mediapassword_variable      ,
    KEEP_REPLICATION      , NORECOVERY
    RECOVERY STANDBY undo_file_name     
    , NOREWIND REWIND      ,
    NOUNLOAD UNLOAD      , RESTART
         , STATS percentage      ,
    STOPAT date_time _at_date_time_var
             , STOPATMARK 'mark_name' AFTER
    datetime          , STOPBEFOREMARK
    'mark_name' AFTER datetime

28
Point in Time Restore
  • Allows user to restore to a point before a
    failure or corrupt data
  • Allows user to restore without unwanted data

29
Restore to Mark
  • STOPATMARK Transactions can be marked so that
    a restore can take you up to that marked
    transaction.
  • STOPBEFOREMARK Restores can also restore up to
    but not including that mark.

30
Using a Backup to Create a New Database
  • A new database can be created by using the backup
    from an existing database.
  • This is made possible by using the with move
    statement which copies the data files and applies
    them to the new database.

31
Consistency in Backing Up the System Databases
  • It is important to consistently backup up the
    master database and the msdb databases.
  • In a restore scenario the master database should
    be restored first and the msdb database should be
    restore next followed by the model.
  • The user databases should be restored last.

32
Discussion
  • Disaster Recovery scenarios.
  • Added input from user experience.
  • Discussion/Comments
Write a Comment
User Comments (0)
About PowerShow.com