Post reply

Filegroup backups

  • May 1, 2007 at 11:00 am

    #117171

    I'm looking at partial backups , using filegroups, where tables exist within a specfic filegroup and I want to just move say a couple of filegroups from one database to another within a filegroup.

    I'm getting some sucess but it doesn't quite work as I expect - or am I trying to do something that's not allowed? msdn indicates you can do this - anyone any experience?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • May 1, 2007 at 11:57 am

    #703634

    Hi Colin,

    What behaviour are you seeing and what are you expecting to happen - it sounds from your question as though you are having partial success in this?

    - James

    --
    James Moore
    Red Gate Software Ltd

  • May 2, 2007 at 3:53 am

    #703751

    You'll perhaps wish you didn't ask < grin > I've a partitioned table which exists in two dedicated filegroups , each partition of the table is in a dedicated filegroup, this table has been migrated from another partitioned table which exists in seperate ( different ) filegroups. I want to back up this table using a filegroup backup and restore it to another database.

    It partically works but I only seem to be able to move one filegroup, the other filegroup is reported as being offline. BOL is pants with msdn and technet not really having any good examples .. msdn says you can do this, but doesn't really show how.

    -- filegroup 1

    RESTORE DATABASE Testio2

       FILEGROUP = 'FG2'

       FROM DISK='F:\SQLData\MSSQL.1\MSSQL\Backup\testioFG.bak'

       WITH FILE=1,NORECOVERY,PARTIAL,REPLACE,

       MOVE 'fg2' TO 'F:\SQLData\Testio2\fg2.ndf',

       MOVE 'testio_log' TO 'F:\SQLData\Testio2.ldf',

       MOVE 'testio' TO 'F:\SQLData\Testio2.mdf';

    -- filegroup 2

    RESTORE DATABASE Testio2

       FILEGROUP = 'FG3'

       FROM DISK='F:\SQLData\MSSQL.1\MSSQL\Backup\testioFG.bak'

       WITH FILE=1,NORECOVERY,PARTIAL,REPLACE,

       MOVE 'fg3' TO 'F:\SQLData\Testio2\fg3.ndf',

       MOVE 'testio_log' TO 'F:\SQLData\Testio2.ldf',

       MOVE 'testio' TO 'F:\SQLData\Testio2.mdf';

    -- t log for completeness

    RESTORE LOG testio2

       FROM DISK='F:\SQLData\MSSQL.1\MSSQL\Backup\testioTlog.trn'

       WITH FILE = 1,

          NORECOVERY;

    -- database back to normal

    RESTORE DATABASE testio2 WITH RECOVERY;

    GO

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply