Filegroup restore problem. RRS feed

  • Question

  • Hi!

    Last time I applied new filegroup to our database. I call it HISTORY, bacause it stores all moditications of tables on PRIMARY filegroup. I.e. if on PRIMARY filegroup is table User, then on HISTORY filegroup is table UserHistory which, stores changes apllied to User table. It's simple database changes monitoring. We use SQL SERVER 2000 Standard Edition.

    One person of our team (Artur) needs copy tables from PRIMARY filegroup to his computer to perform some experiments. Previously there aren't problem. He make full backup of our database, copy it to DVD drive and restore it on his computer. Backup was size of 3GB.

    When we added monitoring full backup is size of 20GB which is too large to DVD disk. So I thoght that we can backup only PRIMARY filegroup, because history of changes is useless for Artur.

    Unfortunately it is not simple as we wish. I tried those steps:

    1. Perform PRIMARY filegroup backup to file.
    2. Copy this file to Artur's computer by DVD disk.
    3. Perform our database's Trnsaction Log on Artur's computer.
    4. Restore PRIMARY filegroup on Artur's computer (then database is "Loading" is Enterprise Manager)
    5. Restore backup done in step 3. --- Step 5. yelds error (File 'TeleDB' has been rolled forward to LSN 51...01. This log terminates at LSN 50...01, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.

    What is wrong?
    Is it possible to move data to Artur's computer this way, without full backup and serie of log backups?
    Can I use other alternative solution to move data?


    Best regards,

    Friday, December 8, 2006 7:41 PM


  • OK.

    There are several issues going on here, but the most fundamental one is that SQL 2000 does not support partial database availability.  The ability to restore only the primary filegroup and bring the database online is a new feature in SQL 2005.

    I believe that the reason for the LSN message you're getting is that you haven't overwritten the HISTORY filegroup in the database you're restoring to, and it has a higher LSN than the primary filegroup that you're restoring.  Even if that mismatch didn't exist (if you deleted the entire database before starting the restore), you wouldn't be able to bring the database online until you restored all of the filegroups.

    About the best you'll get in SQL 2000 is to back up each filegroup separately and transfer it on its own DVD, but you can't omit a filegroup and still bring the DB online.

    Monday, December 11, 2006 11:15 PM