SQL Server Performance

Backing Up and Restoring Filegroups

Discussion in 'SQL Server 2008 General DBA Questions' started by jamie.downs, Aug 11, 2010.

  1. jamie.downs Member

    Hi,
    Please can someone advise if the following is possible?
    I have a production and a dr site with the same database on.
    If I have a table which is partitioned by month and only the current month is updated daily.
    I want to backup only this Filegroup and restore it against DR daily. Is this possible?
    Thanks for taking the time to read my post.
    cheers.
    Jamie.
  2. FrankKalis Moderator

    Yes, have a look at file backup/restore in BOL.The explanations for BACKUP give you some example for that.
    I guess this is additional to restoring the database once in a month to production site to DR site?
  3. jamie.downs Member

    Hi Frank,
    Thanks for the reply.
    I have taken a look at BOL and am a little bit confused what exactly I need to backup to make this work.
    If I use Filegroup backups can I backup just the one I am interested in. Or do I need to backup and restore the Primary filegroup as too.
    I don't believe it is practical for us to do a full backup monthly is the db is 0.5TB and the kit it is on is under powered.
    Thanks
    Jamie.
  4. jamie.downs Member

    Arghhhhhhhhhhhhhhhh. I'm going to explode. Or am I just thick? Please don't answer that.
    I am able to restore an additional FILEGROUP but then the others are left in a state of RECOVERY_PENDING. The scenario is:
    1, create db with two additional filegroups and partitioned table.
    2. Insert some data
    3. Do a full backup
    4. add new filegroup and change partition stuff to create new partitiion on end
    5. backup primary and new filegroup
    6. DROP the database
    7. Restore the full backup. At this point all filgroups are online total of three including primary
    8. I now want to restore the additional 3rd Filegroup backup. If I use the followingRESTORE DATABASE Test
    FILEGROUP
    = 'PRIMARY',FILEGROUP
    = 'fg03'FROM DISK = N'D:FG03.bak' WITH RECOVERY, REPLACE, PARTIAL
    GO
    The 3rd Filegroup comes online but the other two are now RECOVERY_PENDING
    Please help.
    Thanks
    Jamie
  5. jamie.downs Member

    yeah. At last
    The following was what was missing.RESTORE
    DATABASE Test FILEGROUP='FG01' WITH RECOVERY, REPLACERESTORE
    DATABASE Test FILEGROUP='FG02' WITH RECOVERY, REPLACE
    The whole script is a s follows.use master
    go
    IF
    EXISTS(SELECT * FROM sys.databases where name = 'Test')BEGIN
    alter database [Test] set single_user with rollback immediate
    DROP database [Test] END
    GO-- Restore the initial db. This only has two filegroups.RESTORE DATABASE [Test]
    FROM DISK = N'D:FullBackup.bak' WITH FILE = 1,
    MOVE N'TEST' TO N'D:DatabaseData FilesTest.mdf', MOVE N'df01' TO N'D:DatabaseData FilesTest_1.ndf', MOVE N'df02' TO N'D:DatabaseData FilesTest_2.ndf',
    MOVE N'TEST_log' TO N'D:DatabaseLog FilesTest_3.LDF', NOUNLOAD, STATS = 10GO-- SELECT file_id, name, physical_name, state, state_desc, is_read_only from test.sys.database_files
    -- set the first two fg's which have been copied across to read onyalter database test set single_user with rollback immediate
    alter
    database test
    modify filegroup fg01 readonlyalter database test
    modify filegroup fg02 readonly alter database test set multi_user with rollback immediateselect file_id, name, physical_name, state, state_desc, is_read_only from test.sys.database_files
    -- now restore the addtional FG3 which has a new partition.
    --RESTORE DATABASE Test
    -- FILEGROUP = 'PRIMARY',
    -- FILEGROUP = 'fg03'--FROM DISK = N'D:FG03.bak'
    -- WITH RECOVERY, PARTIAL--, replace
    --GO
    --BACKUP LOG Test
    -- TO DISK = N'D:LogBackup.bak' WITH INIT
    --GORESTORE DATABASE Test
    FILEGROUP
    = 'PRIMARY',FILEGROUP
    = 'fg03'FROM DISK = N'D:FullBackup1.bak'
    WITH RECOVERY, PARTIAL, REPLACEGO--RESTORE DATABASE TEST READ_WRITE_FILEGROUPS FROM DISK = N'D:FullBackup1.bak'
    --WITH PARTIAL, RECOVERY--, REPLACEselect
    file_id, name, physical_name, state, state_desc, is_read_only from test.sys.database_filesRESTORE
    DATABASE Test FILEGROUP='FG01' WITH RECOVERY, REPLACERESTORE DATABASE Test FILEGROUP='FG02' WITH RECOVERY, REPLACE alter database test set single_user with rollback immediate
    alter
    database test
    modify filegroup fg01 readwritealter database test
    modify filegroup fg02 readwrite alter database test set multi_user with rollback immediate
    select
    file_id, name, physical_name, state, state_desc, is_read_only from test.sys.database_filesselect * from test..trade where id >=15

Share This Page