question

Hi All, Need a help with an issue regarding partial database backup/restore. I´ll try to detail as much as possible... I have database called "COLLECT" and a backup strategy that makes a full daily backup at 7AM. Then, I have an hourly log backup from 9AM until 11PM. That database has 2 filegroups, 1 file each. The PRIMARY filegroup holds all the transactional tables, actually the whole system itself. The SECONDARY filegroup holds historical data, and it is set to read-only. Since the SECONDARY filegroup is read-only, I just need to backup it up once. The full backup at 7AM consider only the PRIMARY filegroup. These backups set needs often to be restore in QA enviromment, so my users can have a database in QA very close to PRD. My question is that I cannot restore in QA my entire database. I´m able to succesfully restore only the PRIMARY filegroup, but never the SECONDARY. Following the backup and restore commands issued: --Full one-time backup for the SECONDARY filegroup. That one was taken august, 8th and the Filegroup was already set read-only. BACKUP DATABASE [COLLECT] FILEGROUP = N'SECONDARY' TO DISK = N'E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_SECONDARY.bak' WITH NOFORMAT, NOINIT, NAME = N'COLLECT_backup_201408060900_SECONDARY', SKIP, REWIND, NOUNLOAD --Full daily backup at 7AM for PRIMARY filegroup BACKUP DATABASE [COLLECT] FILEGROUP = N'PRIMARY' TO DISK = N'E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408280700_PRIMARY.bak' WITH NOFORMAT, NOINIT, NAME = N'COLLECT_backup_201408280700_PRIMARY', SKIP, REWIND, NOUNLOAD --backup logs hourly from 9AM to 11PM BACKUP LOG [COLLECT] TO DISK = N'E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408280900.trn' WITH NOFORMAT, NOINIT, NAME = N'COLLECT_backup_20140828090000', SKIP, REWIND, NOUNLOAD GO BACKUP LOG [COLLECT] TO DISK = N'E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408281000.trn' WITH NOFORMAT, NOINIT, NAME = N'COLLECT_backup_20140828100000', SKIP, REWIND, NOUNLOAD GO --and so on until the last one (11PM) BACKUP LOG [COLLECT] TO DISK = N'E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408282300.trn' WITH NOFORMAT, NOINIT, NAME = N'COLLECT_backup_20140828230000', SKIP, REWIND, NOUNLOAD Now, I need to restore then in QA enviromment. Following the commands issued: ALTER DATABASE COLLECT SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE COLLECT FILEGROUP = 'PRIMARY' FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408280700_PRIMARY.bak' WITH REPLACE, PARTIAL, NORECOVERY RESTORE DATABASE COLLECT FILEGROUP = 'SECONDARY' FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_SECONDARY.bak' WITH REPLACE, NORECOVERY RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408280900.trn' WITH REPLACE, NORECOVERY /* Here I get the firts error and cannot contineu with any other TRN restores until 11PM... Msg 4312, Level 16, State 1, Line 1 This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. */ RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261000.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261100.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261200.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261300.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261400.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261500.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261600.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261700.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261800.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408261900.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408262000.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408262100.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408262200.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT FROM DISK='E:\DATABASES\BACKUP\COLLECT\COLLECT_backup_201408262300.trn' WITH REPLACE, NORECOVERY --same error above RESTORE DATABASE COLLECT WITH RECOVERY /* Last error issued Msg 4303, Level 16, State 1, Line 1 The roll forward start point is now at log sequence number (LSN) 2562475000104117500001. Additional roll forward past LSN 2562937000274068900001 is required to complete the restore sequence. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. */ How can I be able to restore just the PRIMARY filegroup or BOTH filegroup in QA enviromment without getting an error? What I´m doing wrong??? Thanks for helping!!!!!