블로그 이미지
LifeisSimple

calendar

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

Notice

2010. 8. 19. 20:08 Brain Trainning/DataBase
출처는 MSDN :

Understanding How Restore and Recovery of Backups Work in SQL Server
SQL Server 2005

Updated: 17 July 2006

Icon showing a blue database disk This topic is relevant for all SQL Server databases.

Restoring is the process of copying data from a backup and applying logged transactions to the data to roll it forward to the target recovery point. A data or differential backup contains sufficient transaction log records to allow rolling forward the active transactions as part of restoring each backup. Each backup also contain sufficient log to roll back uncommitted transactions to bring the database into a state that is transactionally consistent and usable. The process of rolling forward uncommitted transactions, if any, and bringing the database online is known as recovery.

The Roll Forward Set

The process of applying logged changes to data in a database to bring the data forward in time is known as rolling forward. The set of all data restored is called the roll forward set. A roll forward set is defined by restoring one or more full backups, such as a database or partial backup or a set of file backups. If a RESTORE statement specifies filegroups, files, or pages, only these items are included in the roll forward set. Otherwise, all files in the backup being restored are included in the roll forward set. If the full backup contains log records, the restored data will be rolled forward using this log.

ms191455.note(en-US,SQL.90).gifNote:
If you specify a filegroup during restore, the restore encompasses the whole filegroup as it currently exists. This includes any files added to the filegroup since the backup was taken. For more information, see Controlling Creation of Added Files During Roll Forward.

For differential backups, if any files were added to the database since the differential base, restoring a differential backup might overwrite pages in the roll forward set with data from the differential backup. For more information, see Using Differential Backups.

Restoring a differential backup updates a page only if the page is in the roll forward set, the page is contained in the backup, and the RESTORE statement either lists the page or its file or does not list any files or pages.

Under the full and bulk-logged recovery models, the log must be backed up separately. After you restore data and (optionally) differential backups, you would typically restore the subsequent log backups to bring the database up to the point of failure. Restoring a log backup rolls forward all pages in the roll forward set. For more information about log backups, see Working with Transaction Log Backups.

Restore Sequences

Each restore scenario is implemented by using one or more restore steps (operations), called a restore sequence. Each operation corresponds to an individual Transact-SQL RESTORE statement. A restore sequence moves affected data through one or more of the phases of restore.

For more information about Transact-SQL restore sequences and how to construct them, see Working with Restore Sequences for SQL Server Databases.

The Phases of Restore

A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:

  • The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files. 
  • The redo phase applies the logged transactions to the data copied from the backup to roll forward that data to the recovery point. At this point, a database typically has uncommitted transactions and is in an unusable state. In that case, an undo phase is required as part of recovering the database.
  • The undo phase, which is the first part of recovery, rolls back any uncommitted transactions and makes the database available to users. After the roll back phase, subsequent backups cannot be restored. 

The rest of this section examines these phases in more detail.

Data Copy Phase

The first phase in any restore process is the data copy phase. The data copy phase initializes the contents of the database, files, or pages being restored. This phase is performed by restore database, restore file, and restore page operations using full or differential backups.

The data copy phase involves copying data from one or more full backups and, optionally, differential backups, and then resetting the contents of the affected database, files, or pages to the time that they were captured by those backups.

The oldest file or page in the roll forward set determines the starting point for the next phase: redo (roll forward).

Redo Phase (Roll Forward)

Redo (or roll forward) is the process of redoing logged changes to the data in the roll forward set to bring the data forward in time. To accomplish roll forward, the SQL Server Database Engine processes log backups as they are restored, starting with the log that is contained in full backups,

Restore avoids unnecessary roll forward. Generally, if data was read-only when it was backed up and has remained read-only, roll forward is unnecessary and is skipped.

The Recovery Point

The goal of roll forward is to return the data to its original state at the recovery point. The recovery point is the point to which the user specifies that the set of data be recovered. Under the full recovery model, you can specify the recovery point as a particular point in time, a marked transaction, or a log sequence number. Under the bulk-logged recovery model, you can restore to a point in time only if no bulk operations have been performed since the previous log backup.

Redo Consistency

In the redo phase, data is always rolled forward to a point that is redo consistent with the state of the database at the recovery point. All the data has been rolled forward to a point at which undo can occur.

The state of the database is defined by the primary file, as follows:

  • If the primary file is being restored, the recovery point determines the state of the whole database. For example, if a database is being recovered to a point in time just before a table was accidentally dropped, the whole database must be restored to the same point in time.
  • If the primary file is not being restored, the database state is known and restored data is rolled forward to a recovery point that is transactionally consistent with the database. SQL Server enforces this. 

However, the database might contain changes made by transactions that are uncommitted at the recovery point. For online restore, data is recovered to a point in time consistent with the current state of the online part of the database.

A differential backup skips forward to when the differential backup was taken. Pages in the roll forward set are overwritten with any more recent ones from the differential backup.

Undo (Roll Back) Phase and Recovery

After the redo phase has rolled forward all the log transactions, a database typically contains changes made by transactions that are uncommitted at the recovery point. This makes the rolled forward data transactionally inconsistent. The recovery process opens the transaction log to identify uncommitted transactions. Uncommitted transactions are undone by being rolled back, unless they hold locks that prevent other transactions from viewing transactionally inconsistent data. This step, is called the undo (or roll back) phase. If the data is already transactionally consistent at the start of the recovery process, the undo phase is skipped. After the database is transactionally consistent, recovery brings the database online.

ms191455.note(en-US,SQL.90).gifNote:
In general terms, recovery is the set of operations that makes a database consistent at database startup. If the database was shut down regularly, recovery skips the redo and undo phases. This is known as restart recovery.

After one or more backups have been restored, recovery typically includes both the redo and undo phases. Every full and differential backup contains enough transaction log records to allow for the data in that backup to be recovered to a self-consistent state.

ms191455.note(en-US,SQL.90).gifNote:
During a crash recovery or a database mirroring failover, SQL Server 2005 Enterprise Edition lets users access the database during the undo phase. This is known as fast recovery. Fast recovery is possible because transactions that were uncommitted when the crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users.

Relationship of RECOVERY and NORECOVERY Options to Restore Phases

A specific RESTORE statement either ends after the redo phase or continues through the undo phase, depending on whether the statement specified WITH NORECOVERY, as follows:

  • WITH RECOVERY includes both the redo and undo phases and recovers the database; additional backups cannot be restored. This is the default.
    If the roll forward set has not been rolled forward far enough to be consistent with the database, the undo phase cannot occur. The Database Engine issues an error and recovery stops. 
    If the whole roll forward set is consistent with the database, recovery is performed, and the database can be brought online. 
  • WITH NORECOVERY omits the undo phase to preserve uncommitted transactions. Omitting the undo phase allows for restoring other backups to roll the database further forward in time. Occasionally, RESTORE WITH NORECOVERY rolls forward data to where it is consistent with the database. In these cases, the Database Engine issues an informational message stating that the roll forward set can now be recovered by sing the RECOVERY option. 
    For information about roll forward sets, see Understanding How Restore and Recovery of Backups Work in SQL Server.
ms191455.note(en-US,SQL.90).gifNote:
A third alternative, WITH STANDBY, is outside the scope of this topic.

For an in-depth description of these RESTORE options, see RESTORE (Transact-SQL).

Recovery Paths

recovery path is a unique set of transformations that have evolved the database over time yet maintained its consistency. For information about recovery paths and the related concepts of recovery forks and recovery branches, see Recovery Paths.

Restoring a Database When SQL Server Is Offline

You can restore and recover a database by using SQL Writer while SQL Server is offline, if no full-text catalog is present. If a full-text catalog is associated with a database, you must first either start SQL Server or stop the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service.

When it is running, SQL Server forces full-text catalog files to close in preparation for the restore operation. However, if SQL Server is offline, MSFTESQL might keep certain full-text files open. This prevents the restore application from overwriting them. To force those full-text files to close, an application can shut down MSFTESQL.

To avoid having to do this, do either of the following:

Change History

ReleaseHistory

17 July 2006

Changed content:
  • Made minor updates in the following sections:
    • "Restore Sequences"
    • "Redo Consistency," formerly "Database Consistency"
    • "Undo (Roll Back) Phase and Recovery," formerly "Backup Recovery"
  • Incorporated and updated the former "Relationship of RESTORE Options to Restore Phases" topic and revised description of WITH RECOVERY.
posted by LifeisSimple

댓글을 달아 주세요

2010. 8. 13. 14:46 Brain Trainning/DataBase

/*

       Delete Del Table

*/

 

declare @tblName varchar(300)

declare curTblName cursor fast_forward for

       select TABLE_NAME

       from srvDB.INFORMATION_SCHEMA.tables where TABLE_NAME like '%삭제%'

open curTblName

fetch next from curTblName

       into @tblName

      

while @@FETCH_STATUS = 0

begin

       exec ('drop table ' + @tblName)

 

       fetch next from curTblName

             into @tblName

end

 

close curTblName

deallocate curTblName

 

 

/*

       Delete SP

*/

declare @prcName varchar(300)

 

declare curPrcName cursor fast_forward for

       select ROUTINE_NAME

             from srvDB.INFORMATION_SCHEMA.ROUTINES

       where ROUTINE_NAME like '%삭제%'

 

open curPrcName

 

fetch next from curPrcName

       into @prcName

      

while @@FETCH_STATUS = 0

begin

       exec ('drop proc ' + @prcName)

 

       fetch next from curPrcName

             into @prcName

end

 

close curPrcName

deallocate curPrcName

posted by LifeisSimple

댓글을 달아 주세요

2010. 7. 23. 15:22 Brain Trainning/DataBase
http://msdn.microsoft.com/ko-kr/library/dd299421(v=SQL.100).aspx

요기가 링크입니다. 

모든 권리는 마이크로소프트에 있다고 하네요.

Integration Services 비디오(SQL Server 2008)

'Brain Trainning > DataBase' 카테고리의 다른 글

[SSRS] Microsoft Video  (0) 2010.07.23
[SSAS] Microsoft Video  (0) 2010.07.23
[SSIS] Microsoft Video  (0) 2010.07.23
디스크공간 / 여유공간  (0) 2010.07.18
디스크 여유공간 체크 및 메일보내기~  (0) 2010.07.18
Categories of DMOs  (0) 2010.07.16
posted by LifeisSimple
TAG MSSQL, ssis

댓글을 달아 주세요

2010. 6. 18. 11:14 Brain Trainning/DataBase
요기를 참고해 주세요.

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx


된장 패치가 무슨 애들 장난이냐...
워드치는거냐

'Brain Trainning > DataBase' 카테고리의 다른 글

Top 10 SQL Server 2008 Features for ISV Applications  (1) 2010.07.14
SQL Server 2008 T-SQL Enhancements  (0) 2010.07.14
SQL Patch 정보  (0) 2010.06.18
MSSql 2005 SP 적용  (0) 2010.06.18
DBA 일간 체크리스트  (0) 2010.06.03
Optimising Server-Side Paging - Part II (펌)  (0) 2010.05.24
posted by LifeisSimple
TAG MSSQL, Patch

댓글을 달아 주세요

2010. 6. 18. 00:05 Brain Trainning/DataBase
SQL Server SP 적용할때 언제 서비스가 중단될까요? 
지금까지 크게 신경 안썻던 부분인데 음.. 요즘 이리 저리 많이 신경쓰게 되고 많은 관심을 가지게 되네요... 

대략 서비스 중단 시점은... 쭈욱~ 이 아니라 다음과 같습니다. 

1. Database Service 업데이트 하는 시점 : 대략 10분 정도
2. SQL Server Native Client 업데이트 시점 : 대략 5분 정도

이정도 서비스 중단이 되더군요... 

그리고, 마지막으로 Reboot 하는 시점입니다. ㅎ

그리 오래 되는건 아니더군요... 참고해야 할 것 같습니다. 



'Brain Trainning > DataBase' 카테고리의 다른 글

SQL Server 2008 T-SQL Enhancements  (0) 2010.07.14
SQL Patch 정보  (0) 2010.06.18
MSSql 2005 SP 적용  (0) 2010.06.18
DBA 일간 체크리스트  (0) 2010.06.03
Optimising Server-Side Paging - Part II (펌)  (0) 2010.05.24
Replication Across Non-Trusted Domains (펌)  (0) 2010.05.17
posted by LifeisSimple

댓글을 달아 주세요