none
Filegroup Restore and error "The online restore to database 'DB' failed."

    Question

  • Hi all,

    SQL Server 2012 EE

    Win Server 2008 R2

    I am doing some tests regarding backup and recovery. Here is what i have done so far:

     - I have created a small database

     - populated it,

     - generated a full backup,

     - Truncate a table

     - Restore (here is where i am getting into issues)

    Here are the detailed steps

    -- backup full

    BACKUP DATABASE TESTE1 TO DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_full_manual_teste1_25082014.bak';

    -- backup transaction log

    BACKUP LOG TESTE1 TO DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_tlog_manual_teste1_25082014.bak';

    -- truncate

    truncate table TESTE.TAB_TESTES_03


    -- restore full

    USE MASTER;
    RESTORE DATABASE TESTE1 FILEGROUP='FG02'
    FROM DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_full_manual_teste1_25082014.bak'
    WITH STATS=20, REPLACE, NORECOVERY;

    -- restore transaction log

    RESTORE LOG TESTE1 FROM DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_tlog_manual_teste1_25082014.bak'
    WITH RECOVERY;

    ... in the "restore full" step i get the following error:

    Msg 3101, Level 16, State 1, Line 1
    Exclusive access could not be obtained because the database is in use.
    Msg 4348, Level 16, State 1, Line 1
    The online restore to database 'TESTE1' failed. It may be appropriate to perform an offline restore instead. An offline restore is initiated by using BACKUP LOG WITH NORECOVERY.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    What i've read so far is that in the EE edition, i can restore a filegroup online. as you can see, i am using the EE version:

    Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
        Feb 10 2012 19:39:15
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    What am i doing wrong?

    Thanks in advance.

    Monday, August 25, 2014 5:09 PM

Answers

  • What I understand is that you cannot execute a ONLINE filegroup restore, is that correct?

    Msg 667, Level 16, State 1, Line 1
    The index "PK_ID_REG_3" for table "TESTE.TAB_TESTES_03" (RowsetId 72057594039173120) resides on a filegroup ("FG02") that cannot be accessed because it is offline, is being restored, or is defunct.

    In fact, the filegroup FG02 is with the state RESTORING (view sys.database_files, column state_desc).

    The question is why this filegroup is with the state RESTORING, if i used the RECOVERY clause in the restore command?


    Hi FabrícioPJ,

    SQL Server Enterprise Edition supports "Online Restore", for more information, please review this article: Online Restore (SQL Server).

    According to this article: Description of restoring file and filegroup backups in SQL Server, you must use file and filegroup backup and restore operations in conjunction with transaction log backups. After you restore the files, you must restore the transaction log backups which are created after backing up the file, then you can bring the database to a consistent state. Also you can review the example in this similar blog to get more information about how to restore file or filegroup.

    Regards,
    Lydia Zhang



    Thursday, August 28, 2014 3:43 AM
    Moderator

All replies

  • Try 

    USE MASTER;
    RESTORE DATABASE TESTE1 FILEGROUP='FG02' 
    FROM DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_full_manual_teste1_25082014.bak'
    WITH STATS=20, REPLACE, NORECOVERY;

    RESTORE LOG TESTE1 FROM DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_tlog_manual_teste1_25082014.bak'
    WITH NORECOVERY;

    and then

    RESTORE DATABASE TESTE1 WITH RECOVERY;



    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, August 25, 2014 5:59 PM
  • Hi,

    Thanks for your help.

    The error mentioned previously happens when i execute the filegroup restore, with the command:

    USE MASTER;

    RESTORE DATABASE TESTE1 FILEGROUP='FG02'

    FROM DISK='K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_full_manual_teste1_25082014.bak'

    WITH STATS=20, REPLACE, NORECOVERY;


    ... when i execute the above command, i get the mentioned error:

    Msg 3101, Level 16, State 1, Line 2
    Exclusive access could not be obtained because the database is in use.
    Msg 4348, Level 16, State 1, Line 2
    The online restore to database 'TESTE1' failed. It may be appropriate to perform an offline restore instead. An offline restore is initiated by using BACKUP LOG WITH NORECOVERY.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.


    • Edited by FabrícioPJ Monday, August 25, 2014 6:21 PM
    Monday, August 25, 2014 6:21 PM
  • You need to obtain exclusive access on TESTE1 database.

    Bring the database TESTE1 to offline state and then restore the database.

    use master
    go
    alter database TESTE1 set offline with rollback immediate;
    and execute the restore command shared by Javier.

    --Prashanth




    Monday, August 25, 2014 6:34 PM
  • Hi

    First you have to kill all the running processes if that also not works then take db in single user mode 

    ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    after restoration make that multiuser..

    thanks

    Vivek

    -----Please mark as Answer if u find it helpfull.

    Tuesday, August 26, 2014 5:16 AM
  • hello,

    Seems your database is in use and please find the SPID details from sp_who2.

    First you kill those sessions and try to restore and it will go smooth. 


    Tuesday, August 26, 2014 5:18 AM
  • Hi, Thanks for all the replies. What I understand is that you cannot execute a ONLINE filegroup restore, is that correct?


    I did another test here... i have created a file backup, as you can see below:

    BACKUP DATABASE TESTE1       

    FILE = 'FG02_01'   

    TO DISK = 'K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_fg02_teste1_27082014_1000.bak';

    The datafile "FG02_01" is the only datafile in the FG02 Filegroup. After that, i tried to restore that same backup, with the following command

    USE MASTER;

    RESTORE DATABASE TESTE1      

    FILE = 'FG02_01'      

    FROM DISK = 'K:\Instances\MSSQL11.MSSQLSERVER\MSSQL\Backup\bkp_fg02_teste1_27082014_1000.bak'  

     WITH STATS=20, RECOVERY;


    The restore ended successfully... but now i cannot use the tables in this filegroup. I get the following error:

    Msg 667, Level 16, State 1, Line 1
    The index "PK_ID_REG_3" for table "TESTE.TAB_TESTES_03" (RowsetId 72057594039173120) resides on a filegroup ("FG02") that cannot be accessed because it is offline, is being restored, or is defunct.

    In fact, the filegroup FG02 is with the state RESTORING (view sys.database_files, column state_desc).

    The question is why this filegroup is with the state RESTORING, if i used the RECOVERY clause in the restore command?

    Thanks again for the attention and pacience.






    • Edited by FabrícioPJ Wednesday, August 27, 2014 1:45 PM
    Wednesday, August 27, 2014 1:44 PM
  • What I understand is that you cannot execute a ONLINE filegroup restore, is that correct?

    Msg 667, Level 16, State 1, Line 1
    The index "PK_ID_REG_3" for table "TESTE.TAB_TESTES_03" (RowsetId 72057594039173120) resides on a filegroup ("FG02") that cannot be accessed because it is offline, is being restored, or is defunct.

    In fact, the filegroup FG02 is with the state RESTORING (view sys.database_files, column state_desc).

    The question is why this filegroup is with the state RESTORING, if i used the RECOVERY clause in the restore command?


    Hi FabrícioPJ,

    SQL Server Enterprise Edition supports "Online Restore", for more information, please review this article: Online Restore (SQL Server).

    According to this article: Description of restoring file and filegroup backups in SQL Server, you must use file and filegroup backup and restore operations in conjunction with transaction log backups. After you restore the files, you must restore the transaction log backups which are created after backing up the file, then you can bring the database to a consistent state. Also you can review the example in this similar blog to get more information about how to restore file or filegroup.

    Regards,
    Lydia Zhang



    Thursday, August 28, 2014 3:43 AM
    Moderator
  • Hi FabrícioPJ,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    Thanks,
    Lydia Zhang

    Monday, September 08, 2014 9:21 AM
    Moderator