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.
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?
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.
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
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'DatabaseData FilesTest.mdf', MOVE N'df01' TO N'DatabaseData FilesTest_1.ndf', MOVE N'df02' TO N'DatabaseData FilesTest_2.ndf', MOVE N'TEST_log' TO N'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