Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: filegroups backup restore

    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





    create table sam (samid int) on 'fg2 '

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

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

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


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

    drop table sam
    go

    -- restore process

    use master
    go
    RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'C:\fgr_fg2.bak'
    GO
    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


    ------------------------------------------------------------------

    After executing these queries, I have 2 issues:

    1)Table sam is not restored.

    2)select * from sys.database_files - state_desc shows restoring. Not sure how to fix it?

    3)When to run the backup query again: BACKUP DATABASE fgr FILEGROUP = 'fg2' TO DISK = 'C:\fgr_fg2.bak' WITH INIT
    It shows the following error:
    Msg 3007, Level 16, State 2, Line 1
    The backup of the file or filegroup "fg2" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.



    Any idea what is going on?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think the sequence should be:

    RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'C:\fgr_fg2.bak' with norecovery
    RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 2 with norecovery
    RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 3 with norecovery
    RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = 1 with norecovery
    RESTORE DATABASE fgr with recovery
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •