• Perform full backups if:
  • Database is small
  • Database has few changes or is read-only
• Clear transaction log periodically if using the full recovery model

• Combine a database and transaction log backup if:
  • Database is often modified
  • Full backup takes too long

• Use differential backups if:
  • Database changes frequently
  • You want to minimize backup time
• Backup up transaction logs separately

• Use file or filegroups if:
  • Database is very large
  • A full backup would take too long
• Back up transaction logs separately
• Can be complex to manage

• Members of the following roles have permission to back up the database:
  • sysadmin
  • db_owner
  • db_backuppoperator

• SQL Server supports backing up to:
  • Tape
  • Disk
• Backup device
  • A physical storage location for backup files
• Backup set
  • A backup on one or more backup devices

• Backs up the entire database
• Includes part of the transaction log file

  BACKUP DATABASE {database_name | @dtabase_name_var}
  TO <backup_device> [, ...n]
  [WITH
    [FORMAT]
    [[,] {INIT | NOINIT}]
  ]

• Recover database to point of failure
• Back up transaction logs separately when using the full or bulk-logged recovery models
• Create frequently

  BACKUP LOG {database_name | @database_name_var}
  TO <backup_device> [, ...n]
  [WITH
    [{INIT | NOINIT}]
  ]

• Backs up the changes since the last full backup
• Smaller and faster than full backup

  BACKUP DATABASE {database_name | @database_name_var}
  TO <backup_device> [, ...n]
  [WITH
    [DIFFERENTIAL]
  ]

• Use for very large databases
• Complex to manage

  BACKUP DATABASE {database_name | @database_name_var}
    [<file_or_filegroup> [, ...n]] TO <backup_device> [, ...n]
  
  Where <file_or_filegroup> is:
  {
    FILE = {logical_file_name | @logical_file_name_var}
    | FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
  }

• Use the MIRROR TO option of the BACKUP statement
• Use the CHECKSUM option of the BACKUP and RESTORE statements
• Use the RESTORE VERIFYONLY statement to validate the backup

• Phases of the restore process
  • Data copy
  • Redo
  • Undo
• Using the NORECOVERY and RECOVERY options

• Restore from a full or differential backup
  • Restores database files
  • Re-creates database objects
• Use RECOVERY or NORECOVERY to control recovery behavior
• Use MOVE…TO option to change file locations
• Use REPLACE option to replace an existing database

  USE master
  RESTORE DATABASE AdventureWorks
  FROM AWBack
  WITH NORECOVERY
  
  RESTORE DATABASE AdventureWorks
  FROM AWBackDiff
  WITH RECOVERY

• Restore from a transaction log backup
  • Restores changes to the database that are recorded in the transaction log
• Use the RECOVERY option on the final log to be restored

  RESTORE DATABASE AdventureWorks FROM AWBack
  WITH NORECOVERY
  
  RESTORE LOG AdventureWorks FROM AWBackLog
  WITH FILE = 1, NORECOVERY
  
  RESTORE LOG AdventureWorks FROM AWBackLog
  WITH FILE = 2, RECOVERY

• Use the STOPAT option with RECOVERY to perform point-in-time recovery

                              Database
                       +--------------------+
Primary filegroup      |      Primary       |
online, database     -------> Filegrup      |
available              |                    |
                       |                    |
                       |                    |
File unavailable,    -------> Filegroup A   |<------- Restoring file [백업장치(테이프,디스크)]
filegroup offline      |                    |
                       |                    |
                       |                    |
All files available, -------> Filegroup B   |
filegroup online       |                    |
                       +--------------------+

1. Identify ID of damaged page
2. Restore page from a full or differential backup
   Use PAGE clause to specify pages to restore
   Specify NORECOVERY

   RESTORE DATABASE AdventureWorks
   PAGE = '1:610'
   FROM AWBack
   WITH NORECOVERY

3. Restore transaction logs
4. Backup transaction log tail
5. Restore transaction log tail

1. Restore damaged file from a full or differential backup
   Use FILE clause to specify file to restore
   Specify NORECOVERY

   RESTORE DATABASE AdventureWorks
   FILE = AW2
   FROM AWBack
   WITH NORECOVERY

2. Backup transaction log tail
   Specify COPY_ONLY
3. Restore transaction logs
4. Restore transaction log tail

1. Backup transaction log tail
   Specify NORECOVERY
2. Restore filegroup from a full or differentail backup
   Use FILEGROUP clause to specity filegroup to restore
   Specify PARTIAL and NORECOVERY

   RESTORE DATABASE AdventureWorks
   FILEGROUP = 'DYNAMIC_DATA'
   FROM AWBackDynamic
   WITH PARTIAL, NORECOVERY

3. Restore transaction logs
4. Restore transaction log tail

• Restore a database in stages based on filegroups
  • Start with primary filegroup
  • Continue with secondary filegroups
• Each filegroup comes online as it is restored
• Transactions involving filegroups not yet restored will hold locks until rollback can complete

• Read-only, consistent view of a database at a specified point in time
• Useful as test or development database, or for reporting
• Must exist on same server as source database

           Source DB                     Snapshot DB
           +--------+                    +--------+
           |  +--+  |                    |        |
           |  |  | <--------------------------------- SELECT...
           |  +--+  |                    |        |
           |        |   Copy-On-Write    |        |
           |  +--+  | (first time only)  |  +--+  |
UPDATE... --> |  | - - - - - - - - - - - -> |  | <--- SELECT...
           |  +--+  |                    |  +--+  |
           +--------+                    +--------+

• Back up the system Databases
  • After modifying the master database
  • After modifying the msdb database
  • After modifying the model database

• Restore master, model, and msdb databases from backup if available
• If no valid backup of the master database exists, re-create using:
  • SQL Server Management Studio
  • Scripts used to create objects
• Reattach undamaged databases rather than restoring

• If SQL Server instance is accessible
  1. Start SQL Server in single-user mode
  2. Restore your lastest backup of the master database
  3. Restart the server
• If SQL Server instance is not accessible
  1. Rebuild master database using Setup.exe
  2. Restore master database
  3. Restore msdb and model databases