Handling "deferred transactions"
bh123
Posts: 2
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:
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.):
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.):
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.
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
There are some potential gotchas with filegroup restore. Hopefully this article from the Red Gate knowledge base explains the situation and how best to work around it.
http://www.red-gate.com/support/Knowled ... How-to.pdf
You probably need to backup the tail of the log as in the document I'd sent previously and restore the log backups as well. That's my best guess as to what "deferred transactions" means.