November 13, 2009 at 1:31 pm
I have a rather simple problem. I have a database with multiple file groups:
PRIMARY
* DWPROD
fg1
* DWPROD_F1
fg2
* DWPROD_F2
fg3
* DWPROD_F3
fg4
* DWPROD_F4
They are (were) all set to read/write. I backed up PRIMARY & fg3 to backup device BACKUP1 and fg1 & fg2 to backup device BACKUP2. I also backed up the log.
I am trying to restore from the backup devices with the script:
RESTORE DATABASE DWPROD
FILEGROUP='PRIMARY'
FROM BACKUP1
WITH NORECOVERY
RESTORE DATABASE DWPROD
FILEGROUP='fg3'
FROM BACKUP1
WITH NORECOVERY
RESTORE DATABASE DWPROD
FILEGROUP='fg2'
FROM BACKUP2
WITH NORECOVERY
RESTORE DATABASE DWPROD
FILEGROUP='fg1'
FROM BACKUP2
WITH RECOVERY
The problem is I can not bring the database online because I'm getting the error message:
The roll forward start point is now at log sequence number (LSN) 1172000000494100001. Additional roll forward past LSN 1172000000497200001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
I assume it's because the backups taken on device BACKUP2 and the backups on device BACKUP1 occurred at different points, but I just want to recover the data I have available.
November 13, 2009 at 1:51 pm
You need to now go and restore all your log backups, starting with the one after the earliest of the filegroup backups and ending with the one you took right before restore (or at least one taken after the last of the filegroup backups)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2009 at 2:01 pm
I restored what I have, but when I query sys.database_files, I get...
DWPROD ONLINE
DWPROD_log ONLINE
DWPROD_F1 RESTORING
DWPROD_F2 RESTORING
DWPROD_F3 ONLINE
I'd just like to bring those two files online in their current state.
November 13, 2009 at 5:58 pm
Surely it must be possible. In a real-world scenario (which thankfully this is only some concept testing), backing up the tail of the t-log may not always be possible.
For example, I assume you manage a MS-SQL database. What would you do if at this very minute a meteorite crashes into it and destroys everything? Probably bring your database up on a remote site, but how do you restore the database without the last t-log & tail? Clearly I'm missing some very important concept...
November 14, 2009 at 2:15 am
ryan.mcatee (11/13/2009)
Surely it must be possible. In a real-world scenario (which thankfully this is only some concept testing), backing up the tail of the t-log may not always be possible.
You don't necessarily need the tail of the log, but you do need all the logs until after the last of those filegroup backups were taken. To restore file/filegroup, you need an unbroken chain of log backups that stretch from the first of the filegroup backups to the last of the filegroup backups. If you do not have all of those (and the error message you posted indicates that you're missing at least one at the end) you cannot bring all the filegroups online.
This is why filegroup backups are more complex that just fill/diff/log and why they're probably not as commonly used
For example, I assume you manage a MS-SQL database.
Not at the moment, I'm a consultant. But thinking back to when I did....
What would you do if at this very minute a meteorite crashes into it and destroys everything? Probably bring your database up on a remote site, but how do you restore the database without the last t-log & tail?
Probably nothing, seeing as my desk was 3 floors above the data center. Ignoring that small problem...
Drive to the secondary data center, reconfigure the SAN (with infrastructure help) to bring it active then reboot the server. We had SAN mirroring for the primary system. For the secondaries, I'd go the log-shipping standby servers and bring the DBs online. We wouldn't have the tail of the log, and maybe not even the log before that, but since we were using just full, diff and log backups, missing logs just meant missing data. We considered using filegroup on the biggest DB but didn't, mainly because of the more complex restore path that would have been required (also because the full/diff was working fine with the maint windows we had.
We
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2009 at 9:47 am
Ha... well let's hope the meteorite scenario doesn't come to fruition then.
I think I found the problem. After I did the original LOG backup, I did a log backup WITH INIT to the disk device I was using. So essentially I had no original LOG backup.
I guess the takeaway message from my little experiment is you can't restore a database under the full restore model with only data file backups, you must have at least the initial log backup.
Thanks for your help.
November 16, 2009 at 12:38 pm
ryan.mcatee (11/16/2009)
I guess the takeaway message from my little experiment is you can't restore a database under the full restore model with only data file backups, you must have at least the initial log backup.
Depends. If you're restoring from a full database backup, then you don't need any of the logs (the db will restore fine without), but to get to point of failure you'd want the logs.
If you're using file/filegroup backups, you need an unbroken chain of log backups that covers the entire interval between the earliest of the backups that you're restoring from and the latest of the backups to bring the entire DB online. Logs past that are just to get to point of failure
Other comment. Taking multiple backups to the same file is generally not recommended for exactly the reason you ran into. One accidental INIT can absolutely ruin your day.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply