Handling "deferred transactions"
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.