Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Work of a DBA is never to be underestimated

  • Work of a DBA is never to be underestimated. An effective DBA’s role maybe understated for the 364 days in a year but for that 1 day when the database gets into a corrupt state is when all the skills come to the fore. Recently while working on a 24x7 large database split into multiple filegroups got into a corrupt state. DBA brought the database ONLINE quickly and was doing a restore in the background. To understand what he would have done, what is Piecemeal Restore inside SQL Server? When was this introduced and what are the options of using this feature?

    Posted on 01-22-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

6  Answers  

Subscribe to Notifications
  • Score
    2

    Databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore.A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary and all read-write, secondary filegroups.

    I have blogged about filegroup level backup/restore here http://sqlsailor.com/2012/01/09/partial-database-availability-a-walk-through-2/

    Replied on Jan 22 2012 11:01PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    8

    What is Piecemeal restore

    In SQL Server 2005, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one of more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

    Piecemeal restore is more versatile than SQL Server 2000 partial restore, because unrestored filegroups can be restored at a later time.

    A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary and all read/write, secondary filegroups.

    Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups. During the piecemeal-restore sequence, the whole database must go offline. Thereafter, the database is online and restored filegroups are available. However, any filegroups that have not yet been restored, remain offline.

    Regardless of the recovery model that is used by the database, the partial-restore sequence starts with a RESTORE DATABASE statement that restores a full backup and specifies the PARTIAL option. The PARTIAL option always starts a new piecemeal restore; therefore, you must specify PARTIAL only one time in the initial statement of the partial-restore sequence. When the partial restore sequence finishes and the database is brought online, the state of the remaining files becomes "recovery pending" because their recovery has been postponed.

    Subsequently, a piecemeal restore typically includes one or more restore sequences, which are called filegroup-restore sequences. You can wait to perform a specific filegroup-restore sequence for as long as you want. Each filegroup-restore sequence restores and recovers one or more offline filegroups to a point consistent with the database. The timing and number of filegroup-restore sequences depends on your recovery goal, the number of offline filegroups you want to restore, and on how many of them you restore per filegroup-restore sequence.

    Piecemeal Restore Scenarios

    OFFLINE PIECEMEAL RESTORE SCENARIO

    In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.

    ONLINE PIECEMEAL RESTORE SCENARIO

    In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.

    Online piecemeal restores can involve deferred transactions. When only a subset of filegroups has been restored, transactions in the database that depend on online filegroups might become deferred. This is typical, because the whole database must be consistent.

    RECOVERY MODEL

    PIECEMEAL RESTORE UNDER THE SIMPLE RECOVERY MODEL

    Under the simple recovery model, the piecemeal restore sequence must start with a full database or partial backup. Then, if the restored backup is a differential base, restore the latest differential backup next.

    PIECEMEAL RESTORE UNDER THE FULL RECOVERY MODEL

    Under the full recovery model or bulk-logged recovery model, piecemeal restore is available for any database that contains multiple filegroups and you can restore a database to any point in time.

    For details Refrence Click:: http://msdn.microsoft.com/en-us/library/ms177425(v=sql.90).aspx

    above link provide detail explaination about pecimal restore with examples

    Thanks

    Yogesh

    Replied on Jan 22 2012 11:18PM  . 
    Yogesh Kamble
    141 · 1% · 349
  • Score
    10

    Piecemeal Restores

    • In SQL Server 2005 Enterprise Edition and later versions that contain multiple files or filegroups;
    • Under the simple model, only for read-only filegroups.

    In SQL Server 2005 and later versions, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore.

    Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

    • Works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

    • Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups.

    • During the piecemeal-restore sequence, the whole database must go offline. Thereafter, the database is online and restored filegroups are available. However, any filegroups that have not yet been restored, remain offline.

    • Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence.

    • The partial-restore sequence starts with a RESTORE DATABASE statement that restores a full backup and specifies the PARTIAL option. The PARTIAL option always starts a new piecemeal restore; therefore, you must specify PARTIAL only one time in the initial statement of the partial-restore sequence. When the partial restore sequence finishes and the database is brought online, the state of the remaining files becomes "recovery pending" because their recovery has been postponed.

    • A piecemeal restore typically includes one or more restore sequences, which are called filegroup-restore sequences. You can wait to perform a specific filegroup-restore sequence for as long as you want

    Piecemeal Restore Scenarios

    All editions of SQL Server support offline piecemeal restores. In SQL Server 2005 Enterprise Edition and later versions, a piecemeal restore can be either online or offline. The implications of offline and online piecemeal restores are as follows:

    Offline piecemeal restore scenario

    In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.

    Online piecemeal restore scenario

    In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.

    Online piecemeal restores can involve deferred transactions. When only a subset of filegroups has been restored, transactions in the database that depend on online filegroups might become deferred. This is typical, because the whole database must be consistent.

    Restriction If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported

    Piecemeal Restore Under the Simple Recovery Model

    Under the simple recovery model, the piecemeal restore sequence must start with a full database or partial backup. Then, if the restored backup is a differential base, restore the latest differential backup next.

    Requirements for Piecemeal Restore Under the Simple Recovery Model

    Under the simple recovery model, the initial stage restores and recovers the primary filegroup and all read/write secondary filegroups. After the initial stage is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

    Piecemeal Restore Stages (Simple Recovery Model)

    • Initial stage (restore and recover the primary filegroup and all read/write filegroups)

    The initial stage performs a partial restore. The partial restore sequence restores the primary filegroup, all read/write secondary filegroups, and (optionally) some of the read-only filegroups. During the initial stage, the whole database must go offline. After the initial stage, the database is online, and restored filegroups are available. However, any read-only filegroups that have not yet been restored, remain offline.

    The first RESTORE statement in the initial stage must do the following:

    • Use a partial or full database backup that contains the primary filegroup and all filegroups that were read/write at the time of the backup. It is common to start a partial restore sequence by restoring a partial backup.
    • Specify the PARTIAL option, which indicates the start of a piecemeal restore.
    • Specify the READWRITEFILEGROUPS option if the backup is a full database backup.

      • While the database is online, you can use one or more online file restores to restore and recover offline read-only files that were read-only at the time of backup. The timing of the online file restores depends on when you want to have the data online.

      • Whether you must restore data to a file depends on the following:

      • Valid read-only files that are consistent with the database can be brought online directly by recovering them without restoring any data.

      • Files that are damaged or inconsistent with the database must be restored before they are recovered.

      --1. Partial restore of the primary and filegroups A and C. RESTORE DATABASE adb FILEGROUP='A',FILEGROUP='C' FROM partial_backup WITH PARTIAL, RECOVERY;

    At this point, the primary and filegroups A and C are online. All files in filegroup B are recovery pending, and the filegroup is offline.

    Piecemeal Restore of Database (Simple Recovery Model)

    --2. Online restore of filegroup B.
    RESTORE DATABASE adb FILEGROUP='B' FROM backup 
    WITH RECOVERY;
    

    All filegroups are now online.

    Piecemeal Restore Under the Full Recovery Model

    Applying Log Backups Under the full recovery model or bulk-logged recovery model, piecemeal restore is available for any database that contains multiple filegroups and you can restore a database to any point in time.
    In SQL Server 2005 Enterprise Edition and later versions, any offline secondary filegroup can be restored and recovered while the database remains online.

    If a read-only filegroup has been read-only since before the file backup was created, applying log backups to the filegroup is unnecessary and is skipped by file restore. If the filegroup is read/write, an unbroken chain of log backups must be applied to the last full or differential restore to bring the filegroup forward to the current log file.

    Before restoring the database, the database administrator must back up the tail of the log. Because the database is damaged, creating the tail-log backup requires using the NO_TRUNCATE option:

    BACKUP LOG adb TO tailLogBackup WITH NORECOVERY, NO_TRUNCATE
    

    1 Partial restore of the primary and secondary filegroup A

    RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 
       WITH PARTIAL, NORECOVERY
    RESTORE DATABASE adb FILEGROUP='A' FROM backup2 
       WITH NORECOVERY
    RESTORE LOG adb FROM backup3 WITH NORECOVERY
    RESTORE LOG adb FROM backup4 WITH NORECOVERY
    RESTORE LOG adb FROM backup5 WITH NORECOVERY
    RESTORE LOG adb FROM tailLogBackup WITH RECOVERY
    

    At this point, the primary filegroup and secondary filegroup A are online. All the files in filegroups B and C are recovery pending, and the filegroups are offline.

    2 Online restore of filegroup C.

    RESTORE DATABASE adb FILEGROUP='C' FROM backup2a WITH NORECOVERY
    RESTORE LOG adb FROM backup3 WITH NORECOVERY
    RESTORE LOG adb FROM backup4 WITH NORECOVERY
    RESTORE LOG adb FROM backup5 WITH NORECOVERY
    RESTORE LOG adb FROM tailLogBackup WITH RECOVERY
    

    At this point the primary and filegroups A and C are online. Files in filegroup B remain recovery pending, with the filegroup offline. Deferred transactions have been resolved, and log truncation occurs.

    3 Online restore of filegroup B.

    In the third restore sequence, the database administrator restores filegroup B. The backup of filegroup B was taken after the filegroup became read-only; therefore, it does not have to be rolled forward during recovery.

    RESTORE DATABASE adb FILEGROUP='B' FROM backup2b WITH RECOVERY
    

    All filegroups are now online.

    http://msdn.microsoft.com/en-us/library/ms177425.aspx

    Replied on Jan 23 2012 5:55AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    9

    Piecemeal Restore

    In SQL Server 2005 and later versions, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

    Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

    Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups. During the piecemeal-restore sequence, the whole database must go offline. Thereafter, the database is online and restored filegroups are available. However, any filegroups that have not yet been restored, remain offline.

    Regardless of the recovery model that is used by the database, the partial-restore sequence starts with a RESTORE DATABASE statement that restores a full backup and specifies the PARTIAL option. The PARTIAL option always starts a new piecemeal restore; therefore, you must specify PARTIAL only one time in the initial statement of the partial-restore sequence. When the partial restore sequence finishes and the database is brought online, the state of the remaining files becomes "recovery pending" because their recovery has been postponed.

    Subsequently, a piecemeal restore typically includes one or more restore sequences, which are called filegroup-restore sequences. You can wait to perform a specific filegroup-restore sequence for as long as you want. Each filegroup-restore sequence restores and recovers one or more offline filegroups to a point consistent with the database. The timing and number of filegroup-restore sequences depends on your recovery goal, the number of offline filegroups you want to restore, and on how many of them you restore per filegroup-restore sequence.

    Restore Scenarios

    All editions of SQL Server support offline piecemeal restores. In SQL Server 2005 Enterprise Edition and later versions, a piecemeal restore can be either online or offline. The implications of offline and online piecemeal restores are as follows:

    • Offline piecemeal restore scenario

    In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.

    • Online piecemeal restore scenario

    In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.

    Online piecemeal restores can involve deferred transactions. When only a subset of filegroups has been restored, transactions in the database that depend on online filegroups might become deferred. This is typical, because the whole database must be consistent.

    Restrictions

    If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. You can force the restore sequence to continue. However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. To force a point-in-time restore, specify the CONTINUEAFTERERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. If you specify CONTINUEAFTERERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

    Piecemeal Restore Under the Simple Recovery Model

    Under the simple recovery model, the piecemeal restore sequence must start with a full database or partial backup. Then, if the restored backup is a differential base, restore the latest differential backup next.

    During the first partial restore sequence, if you restore only a subset of read/write filegroups, any unrestored filegroups become defunct when you recover the partially restored database. Omitting a read/write filegroup from the partial-restore sequence is appropriate only in the following cases:

    • You intend for the unrestored filegroups to become defunct.
    • The restore sequence will arrive at a recovery point at which each unrestored filegroup has become read-only, dropped, or defunct (during a previous restore in the partial-restore sequence).
    • The full backup was taken while the database was using the simple recovery model, but the recovery point is at a time when the database is using the full recovery model.

    For detail, Check here.

    Piecemeal Restore Under the Full Recovery Model

    Under the full recovery model or bulk-logged recovery model, piecemeal restore is available for any database that contains multiple filegroups and you can restore a database to any point in time. The restore sequences of a piecemeal restore behave as follows:

    • Partial-restore sequence

    The partial restore sequence restores the primary filegroup and, optionally, some of the secondary filegroups.

    The first RESTORE DATABASE statement must do the following:

    1. Specify the PARTIAL option. This indicates the start of a piecemeal restore.
    2. Use any full database backup that contains the primary filegroup. The common practice is to start a partial restore sequence by restoring a partial backup.
    3. To restore to a specific point in time, you must specify the time in the partial restore sequence. Every successive step of the restore sequence must specify the same point in time.

    Filegroup-restore sequences bring additional filegroups online to a point consistent with the database.

    In SQL Server 2005 Enterprise Edition and later versions, any offline secondary filegroup can be restored and recovered while the database remains online. If a specific read-only file is undamaged and consistent with the database, the file does not have to be restored.

    For detail, Check here.

    Performing a Piecemeal Restore of a Database Whose Recovery Model Has Been Switched from Simple to Full

    You can perform a piecemeal restore of a database that has been switched from the simple recovery model to the full recovery model since the full partial or database backup. For example, consider a database for which you take the following steps:

    1.Create a partial backup (backup_1) of a simple-model database.

    2.After some time, change the recovery model to full.

    3.Create a differential backup.

    4.Start taking log backups.

    Thereafter, the following sequence is valid:

    1.A partial restore that omits some secondary filegroups.

    2.A differential restore followed by any other needed restores.

    3.Later, a file restore of a read/write secondary filegroup WITH NORECOVERY from the backup_1 partial backup

    4.The differential backup followed by any other backups that were restored in the original piecemeal restore sequence to restore the data up to the original recovery point.

    Replied on Jan 27 2012 1:09AM  . 
    ATif-ullah Sheikh
    132 · 1% · 391
  • Score
    10

    To provide greater flexibility for large DB scenarios, a new feature of SQL Server 2005 called OnLine Piecemeal Restore allows us to use specific filegroup backups to create a smaller work database containing only the data we wish to restore. By placing tables (including individual partitions of partitioned tables) on specific filegroups, we can devise a granular backup and restore strategy where part of a database such as a table or even a single partition of a partitioned table, can be restored quickly. We can also roll transactions forward to a specific point-in-time with transaction logs using any of the following: a specific time, a transaction mark, or a log sequence number (LSN).

    Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

    The example database, ProdDB has the following secondary filegroups:

        Customers:                         FGCustomers
    
        Current year orders (2005):        FGOrders 
    
        Historical orders (2000-2004):     FGOrderHistory.
    

    ProdDB contains three large user tables: Customers, Orders and OrderDetails. The Customers table is placed on filegroup FGCustomers. The table Orders is partitioned by year. The current years’ orders (2005) are placed on FGOrders, while years 2000-2004 are placed on filegroup FGOrderHistory.
    The layout of ProdDB is as follows:

    alt text

    The combination of filegroup backups and rolling transaction logs forward provide recoverability to a point-in-time.
    Assume that a batch job has resulted in incorrect 2005 data and that you must perform a partial filegroup restore of FGOrders (shaded in gray) to recover the data.

    Steps for partial online filegroup restore to a point-in-time:

    Backup ProdDB’s filegroups primary, FGCustomers, FGOrders, FGOrderHistory Batch jobs run (assume batch job messes up 2005 data……) Backup tail of transaction log (assuming you need to roll forward to point-in-time in the log) Perform piecemeal restore. Use ProdDB primary & specific filegroup (FGOrders) backup to create a new work database called PartialDB. Roll forward transaction log to point in time (just before bad batch job). The 2005 partition of Orders was recovered from the FGOrders backup. Delete bad data in ProdDB.
    Copy restored partition or table from work database PartialDB to ProdDB

    Example of filegroup & transaction log backups, bad batch job, and online piecemeal restore

    1. Backup ProdDB filegroups & transaction log:
    
    BACKUP DATABASE ProdDB
    
    FILEGROUP = 'PRIMARY' 
    
    to disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK' with init
    
    -- SQL FGCustomers FILEGROUP backup
    
    BACKUP DATABASE ProdDB
    
    FILEGROUP = 'FGCustomers'
    
    to disk = 'C:\ProdDB_FG_FGCustomers_SQL.BAK' with init
    
    -- SQL FGOrders FILEGROUP backup
    
    BACKUP DATABASE ProdDB
    
    FILEGROUP = 'FGOrders'    
    
    to disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK' with init
    
    -- SQL FGOrderHistory FILEGROUP backup
    
    BACKUP DATABASE ProdDB
    
    FILEGROUP = 'FGOrderHistory'    
    
    to disk = 'C:\ProdDB_FG_FGOrderHistory_SQL.BAK' with init
    
    -- SQL log backup
    
    BACKUP log ProdDB
    
    to disk = 'C:\ProdDB_LOG_SQL1.BAK' with init
    
     
    2. Assume a batch job messes up some 2005 data
    -- assume a batch job wrongly updates or deletes some 2005 data 
    
    DELETE from [ProdDB]..Orders
    
    where OrderID in (select top 50 OrderID from [ProdDB]..Orders where OrderDate > '2005-01-30')
    
     
    3. Backup tail of log:
    -- Backup tail of transaction log (assuming you need to roll forward to point-in-time in the log)
    
    BACKUP log ProdDB
    
    to disk = 'C:\ProdDB_LOG_SQL2.BAK' with init
    
    1. OnLine Piecemeal Restore creates work database PartialDB:
    -- PartialDB will be created from ProdDB backups. 
    
    -- MOVE used to create files for PartialDB
    
    RESTORE DATABASE PartialDB
    
    from disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK'
    
    WITH NORECOVERY, REPLACE, PARTIAL – partial:not all FG will be restored
    
    ,MOVE 'ProdDB_data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBPri1dat.mdf'
    
    ,MOVE 'SProdDB_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBlog.ldf'
    
    -- restore only the latest 2005 data, oldest partitions on FGOrdersHistory not restored
    
    RESTORE DATABASE PartialDB
    
    from disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK'
    
    WITH NORECOVERY
    
          ,MOVE 'FGOrders_file1' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBOrdFi1dt.ndf'
    
    -- restore first log up to point-in-time. You can also restore to a transaction mark or LSN.
    
    RESTORE log PartialDB
    
    from disk = 'C:\ProdDB_LOG_SQL1.BAK'
    
    WITH NORECOVERY, STOPAT = 'Oct 26, 2005 11:00 PM' -- restore second log up to point-in-time. You can also restore to a transaction mark or LSN.
    
    RESTORE log PartialDB
    
    from disk = 'C:\ProdDB_LOG_SQL2.BAK'
    
    WITH RECOVERY, STOPAT = 'Oct 26, 2005 11:00 PM'  
    
    -- NOTE: Piecemeal restore. Part of PartialDB is recovered & online.
    
    select file_id, name, physical_name, state, state_desc 
    
    from sys.database_files
    
     

    Note if you choose to use a transaction mark to establish the point-in-time to recover to, remember that the transaction must be writable, e.g. it must contain an insert, update, or delete within the transaction for the transaction mark to be written to the log. A transaction mark with select statements only is not a writeable transaction.

    alt text

    Note that the newly created work database PartialDB contains only 3 files from 2 filegroups. A backup of the Primary filegroup restores the files ProdDBdata (contains system catalog tables) and SProdDBlog which is the transaction log. The secondary filegroup FGOrders is used to recreate FGOrdersfile1.
    FGOrders contains only the 2005 Orders data.The state
    desc is the one should be pointed out here as ONLINE. This is the best way for multiple filegroup backup to restore the database within the limited period of time.

    Replied on Jan 31 2012 7:05AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    9

    Piecemeal Restore

    In SQL Server 2005 and later versions, databases that contain multiple filegroups can be restored and recovered in stages through a process known as piecemeal restore.

    Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. After the restore sequence is completed, recovered files, if they are valid and consistent with the database, can be brought online directly.

    Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

    Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and under the simple recovery model, all read/write filegroups.

    A piecemeal restore can be either online or offline.

    Online piecemeal restores can involve deferred transactions. When only a subset of filegroups has been restored, transactions in the database that depend on online filegroups might become deferred.

    When was this introduced
    In SQL Server 2005 and later versions.

    what are the options of using this feature?

    • PARTIAL
    • NORECOVERY
    • RECOVERY

    PARTIAL
    You must specify PARTIAL OPTION only one time in the initial statement of the partial-restore sequence.The PARTIAL option always starts a new piecemeal restor.

    NORECOVERY
    NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

    RECOVERY
    RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup. Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

    Example

    Partial restore of the primary and secondary filegroup A.

    RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 
       WITH PARTIAL, NORECOVERY
    RESTORE DATABASE adb FILEGROUP='A' FROM backup2 
       WITH NORECOVERY
    RESTORE LOG adb FROM backup3 WITH NORECOVERY
    RESTORE LOG adb FROM backup4 WITH NORECOVERY
    RESTORE LOG adb FROM backup5 WITH NORECOVERY
    RESTORE LOG adb FROM tailLogBackup WITH RECOVERY
    

    Note : Before restoring the database, the database administrator must back up the tail of the log. Because the database is damaged, creating the tail-log backup requires using the NO_TRUNCATE option:

    Example: Piecemeal Restore of Database (Full Recovery Model)
    http://msdn.microsoft.com/en-us/library/ms175541(v=sql.110).aspx

    Example: Piecemeal Restore of Database (Simple Recovery Model)
    http://msdn.microsoft.com/en-us/library/ms189557(v=sql.110).aspx

    Replied on Feb 4 2012 6:26AM  . 
    Mitesh Modi
    18 · 10% · 3080

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.