locked
Unable to restore a filegroup backup RRS feed

  • Question

  • I have created a database and added a table to that database in primary file group later,

    i created one more file group with name secondary and added another table to that

    file group, before adding secondary file group i have taken full backup of my database

    and in another server i have restored full backup first

    when i'm restoring the file group i'm getting an error what may be the problem.

    can any one help me.

    Monday, January 9, 2012 9:02 AM

Answers

  • See Tibor's example

    --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/
    • Marked as answer by Maggie Luo Monday, February 6, 2012 11:30 AM
    Monday, January 9, 2012 9:06 AM

All replies

  • See Tibor's example

    --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/
    • Marked as answer by Maggie Luo Monday, February 6, 2012 11:30 AM
    Monday, January 9, 2012 9:06 AM
  • As mentioned By Uri, you can try peicemeal restore: - http://msdn.microsoft.com/en-us/library/ms190394.aspx

     


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog
    Monday, January 9, 2012 11:49 AM