adminschool.net wiki

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
====== Lesson 1: Planning a Backup Strategy ======
===== SQL Server Backup Types =====
^  Backup Type    ^  Description                                                                              ^
^ Full            | 모든 데이터 파일과 트랜잭션 로그의 일부분을 백업한다.                                     |
^ Transaction log | 로그 파일에 기록된 데이터베이스 변화들을 백업한다.                                        |
^ Tail-log        | 로그의 활성부분(active portion)을 백업한다.                                               |
^ Differential    | The parts of the database that have changed since the last full database backup           |
^ File/Filegroup  | Specified files or filegroups                                                             |
^ Partial         | The primary filegroup, every read/write filegroup, and any specified read-only filegroups |
^ Copy-only       | The database or log (without affecting the backup sequence)                               |

===== What Are Recovery Models? =====
^  Recovery Model  ^  Description                                                                                ^
^ Simple      | Uses full or differential copies of the database. Truncates the transaction logs.                |
^ Full        | Includes both database backups and transaction log backups.                                      |
^ Bulk-logged | Includes both database and transaction log backups, but uses less log space for some operations. |

===== What Is a Full Database Backup Strategy? =====
  * 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

===== What Is a Database and Transaction Log Backup Strategy? =====
  * Combine a database and transaction log backup if:
    * Database is often modified
    * Full backup takes too long

===== What Is a Differential Bakcup Strategy? =====
  * Use differential backups if:
    * Database changes frequently
    * You want to minimize backup time
  * Backup up transaction logs separately

===== What Is a File of Filegroup Backup Strategy? =====
  * 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

===== Considerations for Backup Operators =====
  * Members of the following roles have permission to back up the database:
    * sysadmin
    * db_owner
    * db_backuppoperator

===== Considerations for Backup Media =====
  * 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


====== Lesson 2: Backing Up User Databases ======
===== How to Perform a Full Database Backup =====
  * Backs up the entire database
  * Includes part of the transaction log file <code>
BACKUP DATABASE {database_name | @dtabase_name_var}
TO <backup_device> [, ...n]
[WITH
  [FORMAT]
  [[,] {INIT | NOINIT}]
]
</code>

===== How to Perform a Transaction Log Backup =====
  * Recover database to point of failure
  * Back up transaction logs separately when using the full or bulk-logged recovery models
  * Create frequently <code>
BACKUP LOG {database_name | @database_name_var}
TO <backup_device> [, ...n]
[WITH
  [{INIT | NOINIT}]
]
</code>

===== How to Perform a Differential Backup =====
  * Backs up the changes since the last full backup
  * Smaller and faster than full backup <code>
BACKUP DATABASE {database_name | @database_name_var}
TO <backup_device> [, ...n]
[WITH
  [DIFFERENTIAL]
]
</code>

===== How to Perform File and Filegroup Backups =====
  * Use for very large databases
  * Complex to manage <code>
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}
}
</code>

===== Options for Ensuring Backup Integrity =====
  * 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

====== Lesson 3: Restoring User Databases ======
===== How the Restore Process Works =====
  * Phases of the restore process
    * Data copy
    * Redo
    * Undo
  * Using the NORECOVERY and RECOVERY options

===== How to Restore a Database =====
  * 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 <code>
USE master
RESTORE DATABASE AdventureWorks
FROM AWBack
WITH NORECOVERY

RESTORE DATABASE AdventureWorks
FROM AWBackDiff
WITH RECOVERY
</code>

===== How to Restore a Transaction Log =====
  * 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 <code>
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
</code>
  * Use the STOPAT option with RECOVERY to perform point-in-time recovery

====== Lesson 4: Performing Online Restore Operations ======
===== Overview of Online Restore Operations =====
<code>
                              Database
                       +--------------------+
Primary filegroup      |      Primary       |
online, database     -------> Filegrup      |
available              |                    |
                       |                    |
                       |                    |
File unavailable,    -------> Filegroup A   |<------- Restoring file [백업장치(테이프,디스크)]
filegroup offline      |                    |
                       |                    |
                       |                    |
All files available, -------> Filegroup B   |
filegroup online       |                    |
                       +--------------------+

</code>

===== How to Restore a Damaged Page =====
  - Identify ID of damaged page
  - Restore page from a full or differential backup \\ Use PAGE clause to specify pages to restore \\ Specify NORECOVERY <code>
RESTORE DATABASE AdventureWorks
PAGE = '1:610'
FROM AWBack
WITH NORECOVERY
</code>
  - Restore transaction logs
  - Backup transaction log tail
  - Restore transaction log tail

===== How to Restore a File =====
  - Restore damaged file from a full or differential backup \\ Use FILE clause to specify file to restore \\ Specify NORECOVERY <code>
RESTORE DATABASE AdventureWorks
FILE = AW2
FROM AWBack
WITH NORECOVERY
</code>
  - Backup transaction log tail \\ Specify COPY_ONLY
  - Restore transaction logs
  - Restore transaction log tail

===== How to Restore a Filegroup =====
  - Backup transaction log tail \\ Specify NORECOVERY
  - Restore filegroup from a full or differentail backup \\ Use FILEGROUP clause to specity filegroup to restore \\ Specify PARTIAL and NORECOVERY <code>
RESTORE DATABASE AdventureWorks
FILEGROUP = 'DYNAMIC_DATA'
FROM AWBackDynamic
WITH PARTIAL, NORECOVERY
</code>
  - Restore transaction logs
  - Restore transaction log tail

===== How to Perform a Piecemeal Restore =====
  * 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

====== Lesson 5: Recovering Data from Database Snapshots ======
===== What Is a Database Snapshot? =====
  * 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

===== How Database Snapshots Work =====
<code>
           Source DB                     Snapshot DB
           +--------+                    +--------+
           |  +--+  |                    |        |
           |  |  | <--------------------------------- SELECT...
           |  +--+  |                    |        |
           |        |   Copy-On-Write    |        |
           |  +--+  | (first time only)  |  +--+  |
UPDATE... --> |  | - - - - - - - - - - - -> |  | <--- SELECT...
           |  +--+  |                    |  +--+  |
           +--------+                    +--------+
</code>

===== How Data Can Be Recovered from a Database Snapshot =====
^  Undelete a row            | Insert original data from snapshot            |
^  Undo an update            | Get original values from snapshot             |
^  Recover a dropped object  | Generate script from snapshot, then copy data |
^  Revert to snapshot        | Restore database from snapshot                |


====== Lesson 6: System Databases and Disaster Recovery ======
===== Considerations for Backing Up System Databases =====
  * Back up the system Databases
    * After modifying the master database
    * After modifying the msdb database
    * After modifying the model database

===== Considerations for Restoring System Databases =====
  * 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

===== How to Restore the master Database =====
  * If SQL Server instance is accessible
    - Start SQL Server in single-user mode
    - Restore your lastest backup of the master database
    - Restart the server
  * If SQL Server instance is not accessible
    - Rebuild master database using Setup.exe
    - Restore master database
    - Restore msdb and model databases
adminschool.net wiki

사용자 도구

사이트 도구

문서 도구
