locked
Problem with restore read_only filegroup RRS feed

  • Question

  • Hello everybody. I have the next problem.

    1.I have database under simple recovery mode.

    2.I created two secondary filegroups A1, A2. Filled this by datas and set to read_only mode.

    3.I created backup of primary filegroup (Backup1_Prim) and backups of filegroups A1, A2 (Backup_A1, Backup_A2).

    4.Then i created another two secondary filgroups B1, B2, filled this by datas and set to read_only mode.

    5.I created backup of primary filegroup (Backup2_Prim) and backups of filegroups B1, B2 (Backup_B1, Backup_B2).

    6.I restored primary filegroup from Backup1_Prim, i restored filgroups A1 и A2 without options disk, because i don't change this filegroups. But files of filegroups B1, B2 was eliminated.

    7.I restored primary filegroup from Backup2_Prim, i restored filgroups A1 и A2 without options disk, because i don't change this filegroups. I try to restore B1 и B2 from Backup_B1 and Backup_B2

    ALTER DATABASE My_DB
    SET restricted_USER
    WITH ROLLBACK IMMEDIATE
    
    alter database My_DB
    set offline
    
    RESTORE DATABASE My_DB READ_WRITE_FILEGROUPS
      FROM DISK = 'path_to_Backup2_Prim'
      WITH PARTIAL, RECOVERY, Replace
    
    RESTORE DATABASE My_DB
      FILEGROUP = 'A1'
      WITH RECOVERY
    
    RESTORE DATABASE My_DB
      FILEGROUP = 'A2'
      WITH RECOVERY
    
    alter database My_DB
    set offline
    
    RESTORE DATABASE My_DB
      FILEGROUP = 'B1'
      FROM DISK = 'path_to_Backup_B1'
      WITH Replace, RECOVERY
    
    RESTORE DATABASE My_DB
      FILEGROUP = 'B2'
      FROM DISK = 'path_to_Backup_B2'
      WITH Replace, RECOVERY
    
    alter database My_DB
    set online
    
    ALTER DATABASE My_DB
    SET multi_user
    
    I have this error

    The database is using the simple recovery model. It is not possible to restore a subset of the read-write data. RESTORE DATABASE is terminating abnormally. The database is using the simple recovery model. It is not possible to restore a subset of the read-write data. RESTORE DATABASE is terminating abnormally. How can i restore filegroups B1 and B2?


    Tuesday, July 12, 2011 8:32 AM

Answers

  • As the error points out, when looking to perform a Partial Database Restore and you are using the SIMPLE recover model all read-write filegroups must be restored as part of the "partial-restore sequence". You cannot then subsequently restore additional read-write filegroups. To be able to do so your database must be using either the FULL or BULK LOGGED recovery models.

    Taken from paragraph 4 of Performing Piecemeal Restores.

    "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."

    See Performing Piecemeal Restores for more details.


    John Sansom | SQL Server DBA Blog | Twitter
    Tuesday, July 12, 2011 9:06 AM

All replies

  • See Tibor's example (I change it little bit to address you case), it worked just fine

    USE master 

     

    IF DB_ID('fgr') IS NOT NULL DROP DATABASE fgr 

     

    GO 

     

    --Three filegroups 

     

    CREATE DATABASE fgr ON  PRIMARY  

     

    ( NAME = N'fgr', FILENAME = 'C:\fgr.mdf'),  

     

     FILEGROUP fg1  

     

    ( NAME = N'fg1', FILENAME = 'C:\fg1.ndf'),  

     

     FILEGROUP fg2  

     

    ( NAME = N'fg2', FILENAME = 'C:\fg2.ndf') 

     

     LOG ON  

     

    ( NAME = N'fgr_log', FILENAME = 'C:\fgr_log.ldf') 

     

    GO 

     

    ALTER DATABASE fgr SET RECOVERY SIMPLE 

     

     

     

    --Base backup 

     

    BACKUP DATABASE fgr TO DISK = 'C:\fgr.bak' WITH INIT 

     

    GO 

     

     

     

    --One table on each filegroup 

     

    CREATE TABLE fgr..t_primary(c1 INT) ON "PRIMARY"

    CREATE TABLE fgr..t_fg1(c1 INT) ON fg1 

    CREATE TABLE fgr..t_fg2(c1 INT) ON fg2 

     

     

     

    --Insert data into each table 

     

    INSERT INTO fgr..t_primary(c1) VALUES(1) 

    INSERT INTO fgr..t_fg1(c1) VALUES(1) 

    INSERT INTO fgr..t_fg2(c1) VALUES(1) 

     

     

    ALTER DATABASE [fgr] MODIFY FILEGROUP [fg1] READONLY

     

     

    --Filegroup backup of fg2 

     

    BACKUP DATABASE fgr FILEGROUP = 'fg1' TO DISK = 'C:\fgr_fg1.bak' WITH INIT 

     

    --Now, try to restore that filegroup to previos point in time 

     

    RESTORE DATABASE fgr FILEGROUP = 'fg1' FROM DISK = 'C:\fgr_fg1.bak' 

    GO 

     

    SELECT * FROM fgr..t_fg1 --error 8653 

    GO 

     

    DROP DATABASE fgr


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 12, 2011 8:57 AM
    Answerer
  • As the error points out, when looking to perform a Partial Database Restore and you are using the SIMPLE recover model all read-write filegroups must be restored as part of the "partial-restore sequence". You cannot then subsequently restore additional read-write filegroups. To be able to do so your database must be using either the FULL or BULK LOGGED recovery models.

    Taken from paragraph 4 of Performing Piecemeal Restores.

    "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."

    See Performing Piecemeal Restores for more details.


    John Sansom | SQL Server DBA Blog | Twitter
    Tuesday, July 12, 2011 9:06 AM
  • Thanks, Uri Dimant, for your answer, but your answer is not for my question.

    I want to restore only read_write filegroups without any problems with read_only filegroups. But in my example i lost last two read_only filegroups. Now i want to restore this last.

    But you propose me to make buckup whole database.

    Tuesday, July 12, 2011 9:08 AM
  • John Sansom, you are right,

    but in my example i try to restore read_only filegroups (B1 and B2) and i don't changed this.

    Tuesday, July 12, 2011 9:11 AM
  • If so, you have to set your database to FULL recovery mode and perform BACKUP LOG file

    Tibor's example

    USE master 

    IF DB_ID('fgr') IS NOT NULL DROP DATABASE fgr 

    GO 

    --Three filegroups 

    CREATE DATABASE fgr ON  PRIMARY  

    ( NAME = N'fgr', FILENAME = 'C:\fgr.mdf'),  

     FILEGROUP fg1  

    ( NAME = N'fg1', FILENAME = 'C:\fg1.ndf'),  

     FILEGROUP fg2  

    ( NAME = N'fg2', FILENAME = 'C:\fg2.ndf') 

     LOG ON  

    ( NAME = N'fgr_log', FILENAME = 'C:\fgr_log.ldf') 

    GO 

    ALTER DATABASE fgr SET RECOVERY FULL 

     

    --Base backup 

    BACKUP DATABASE fgr TO DISK = 'C:\fgr.bak' WITH INIT 

    GO 

     

    --One table on each filegroup 

    CREATE TABLE fgr..t_primary(c1 INT) ON "PRIMARY"

    CREATE TABLE fgr..t_fg1(c1 INT) ON fg1 

    CREATE TABLE fgr..t_fg2(c1 INT) ON fg2 

     

    --Insert data into each table 

    INSERT INTO fgr..t_primary(c1) VALUES(1) 

    INSERT INTO fgr..t_fg1(c1) VALUES(1) 

    INSERT INTO fgr..t_fg2(c1) VALUES(1) 

     

    BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH INIT --1 

     

    --Filegroup backup of fg2 

    BACKUP DATABASE fgr FILEGROUP = 'fg2' TO DISK = 'C:\fgr_fg2.bak' WITH INIT 

     

    BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --2 

     

    --Delete from t_fg2 

    --Ths is our accident which we want to rollback!!! 

    DELETE FROM fgr..t_fg2 

     

    BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --3 

     

    --Now, try to restore that filegroup to previos point in time 

    RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'C:\fgr_fg2.bak' 

    GO 

     

    SELECT * FROM fgr..t_fg2 --error 8653 

    GO 

     

    --If we are on 2005+ and EE or Dev Ed, the restore can be online 

    --This means that rest of the database is accessible during the restore 

    INSERT INTO fgr..t_fg1(c1) VALUES(2) 

    SELECT * FROM fgr..t_fg1 

     

    --We must restore *all* log backups since that db backup 

    RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 2 --out of 3 

    RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 3 --out of 3 

    GO 

     

    SELECT * FROM fgr..t_fg2 --Success 

    --We didn't get to the data before the accidental DELETE! 

    GO 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 12, 2011 11:34 AM
    Answerer
  • Uri Dimant, thanks for your answer, but it is not, that i want.

    If at first i restore from Backup (made when i have only first secondary filegroups) i lost last secondary filegroups. But if i want to restore from Backup2 (when i have all filegroups) the restoring process will failed, and last secondary filgeroups will not restored.

    Wednesday, July 13, 2011 7:02 AM
  • I'm struggling to understand what you are trying to achieve.

    May I suggest that you provide a full T-SQL listing that demonstrates your current issue from start to finish, including backup steps.

    This will enable forum users to replicate the issue you describe and look toward a solution. 


    John Sansom | SQL Server DBA Blog | Twitter
    Wednesday, July 13, 2011 7:22 AM