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?
