Restore readonly filegroup sqlserver 2000

Hi,

I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:

Take a full backup of TestDatabase

Restore full backup as TestDatabaseDemo

Run long-running data-operations on TestDatabaseDemo

Take a backup of TestDatabaseDemo

Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.

Suppose we arrived at step 5, I am executing the following commands:

Code Snippet

--make full backup of TestDatabase
backup database TestDatabase to DISK='G:\temp\testdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G:\temp\testdatabasedemo.bak' with init

backup log TestDatabase to DISK='g:\temp\testdatabase.log'

--restore secondary filegroup from TestDatabasedemo-backup

restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G:\temp\testdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G:\temp\TestDatabase_Data_secondary.ndf', NORECOVERY

--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G:\temp\testdatabase.bak'
with move 'TestDatabase_Data' to 'G:\temp\TestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin

restore log TestDatabase FROM DISK='g:\temp\testdatabase.log' with recovery

I get the following error:

The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.

When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement

I get error:

The database cannot be recovered because the files have been restored to inconsistent points in time.

How i can restore the read-only filegroup correctly?

Hi Stijn,

Simple answer is that you can't do that. The SECONDARY filegroup is now at a different (more recent) point in time than the rest of the TestDatabase - exactly like the final error message says. What you're essentially trying to do is mix-n-match filegroups from different databases.

Thanks

|||

Hi Paul,

Thanks for the answer! I was thinking the mix-n-match would work, because that filegroup is readonly for the database, so applying the transaction log would be good enough for the other (read-write) filegroup, since there couldn't be any inserts/updates/deletes on the secondary filegroup. Which technique is used on very large databases to update a read-only filegroup? Using dts to copy objects from a test-database to the production database? Or is there a more efficient way?