What are the challenges you face when working across database platforms? Take the survey
Options

Handling "deferred transactions"

bh123bh123 Posts: 2
edited April 14, 2010 11:10AM in SQL Backup Previous Versions
We're having an issue with a filegroup restore, which I think is just a matter of getting the syntax right for the RESTORE statement. But I haven't seen the information I need from Books Online or Red Gate Backup's help file.

Our production server has a database with three filegroups: “PRIMARY”, “Web”, and “Main”. On a nightly basis, we make a backup of two of those filegroups, “PRIMARY” and “Web”, and restore the backup on our web server. (The "Main" filegroup contains sensitive data and is never put out on the web server.) Occasionally, during the restore, we get this error message:
SQL Error 3192: Restore was successful but deferred transactions remain. These transactions cannot be resolved because there are data that is unavailable. Either use RESTORE to make the data available or drop the filegroups if you never need this data again. Dropping the filegroup results in a defunct filegroup.

The RESTORE command we're executing (the one that gets the above error) is similar to the following. (This is the @cmd passed to sqlbackup.):
RESTORE DATABASE ERIN_Web_New
    FILE = 'ERIN Primary',
    FILE = 'ERIN Web'
    FROM DISK = 'D:\Incoming Data\Web data from ERIN.sqb'
    WITH RECOVERY;

I read Books Online to find out how to make "Main" defunct. You execute "ALTER DATABASE ... REMOVE FILE" and then "ALTER DATABASE ... REMOVE FILEGROUP". I assume you also need to modify the RESTORE statement so it doesn't attempt to recover Main. Below is the code I tried. (Again, the RESTORE command shown below is actually a string passed to sqlbackup.):
RESTORE DATABASE ERIN_Web_New
    FILE = 'ERIN Primary',
    FILE = 'ERIN Web'
    FROM DISK = 'D:\Incoming Data\Web data from ERIN.sqb'
    WITH NORECOVERY, PARTIAL;
        
ALTER DATABASE ERIN_Web_New REMOVE FILE [ERIN Main];

ALTER DATABASE ERIN_Web_New REMOVE FILEGROUP Main;

RESTORE DATABASE ERIN_Web_New WITH RECOVERY;

The problem is that when I execute the first ALTER DATABASE, I get an error saying that the database is offline.

So it's essentially a catch-22. Either you do a "full restore", in which case you get an error about deferred transactions in Main, or you use PARTIAL and RECOVERY, but the database is not online. I'm obviously missing a step in my last block of code, or the RESTORE options are not right, or both. Any help would be appreciated.

Thanks.

Comments

Sign In or Register to comment.