June 14, 2015 at 11:44 am
Hi,
I have a question regarding the error "log file does not match primary file". last month, one of our 2005 databases ( lets call DB1 (simple mode) ) was moved to a 2008 R2 RTM x64 server. One of two people moved it by either restored .bak or copied .mdf & .ldf & attached, but I'm thinking the second. This past Saturday the server rebooted at 6:18am, not a planned reboot, immediately after the database became "inaccessible" and the error "log file does not match primary file" was in the log.
We use a third party for backups, all we tried (which are in the form of .mdf & .ldf) would not attach and resulted in the same error.
The network guy tried a direct restore through the software and that created a database in suspect mode. When I tried to update it to emergency mode, I got the same error. I tried two ways of creating the database with only the .mdf, both failed with the error message that "the database was not shut down cleanly and there were open transactions or that this could happen because the transaction log was deleted", so database would not create.
My question is, can anyone shed any light on what may have caused this? Is there anything I could have done prior to trying the restore or to detect this issue earlier? Is it possible it was an issue from day one that did not pop up until the reboot?
I ended up taking a backup of the original database and restoring it on the new server and ran CHECKDB, no errors. So we have data from a month ago, not a big deal for this database but would be a disaster if this happened to something else.
Thank you for any guidance here,
-karen
June 14, 2015 at 3:11 pm
Can you posts errors from the SQL Server Error Log with more details? It might shed some more light on the situation.
Joie Andrew
"Since 1982"
June 14, 2015 at 4:35 pm
Here was the initial error in the log on 6/13 at 6:20am:
Starting up database 'XXXXX'.
Error: 5173, Severity: 16, State: 1.
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Error: 5173, Severity: 16, State: 1.
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'E:\xxxx\LOGS\XXX_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
When I tried to attach the .mdf and .ldf from a previous days backup, I got the above error also.
when I tried to create a database from just an .mdf and rebuild the log file:
CREATE DATABASE XXXRestore ON
(FILENAME = N'x:\RestoreDB\XXXX.mdf')
FOR ATTACH_REBUILD_LOG
or
EXEC sp_attach_single_file_db @dbname='xxxxxRESTORE',
@physname=N'x:\RestoreDB\xxxxx.mdf'
GO
I got this as a result in the message window:
File activation failure. The physical file name "x:\xxxx\LOGS\xxxxxx_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'xxxxxRestore'. CREATE DATABASE is aborted.
and this in the error log:
Starting up database 'xxxxxRestore'.
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'x:\xxxx\LOGS\xxxx_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
Thank you!
Karen
June 14, 2015 at 9:12 pm
The safest thing to do for the future would be nightly full backups and log file backups every 15 minutes. That way, you'd never lose more than 15 minutes of data in such a situation.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2015 at 5:13 am
Copying files is not a backup strategy because, unless the tool copying the files syncs with SQL Server, the 'backed up' files may very well be useless.
You need to put a proper backup strategy in place. Full database backups, transaction log backups, possibly differential backups and then let your 3rd party backup tool copy those files to another location.
And no, once things went south, without database backups there was pretty much nothing you could have done.
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
June 15, 2015 at 7:08 am
I'd agree with Gail and Jeff here.
It looks like somehow the server was shutdown improperly and then files were moved incorrectly. Do you not have any SQL Server backups?
June 15, 2015 at 7:44 am
The server rebooted itself because of windows updates. Networking guys said this was a clean shutdown + supposed to happen.
We do sql database snapshot backups through a third party software on hourly intervals. I have sql server maintenance plans setup to backup other databases, but not this one (changed that), the one in question was only through the third party software which our primary backup solution.
All our backups through the third party software seem to be bad for this database, I am wondering, if I had sql server backups, if they would have been bad as well?
How does one verify the backup is successful and free of corruption? Is the only way to restore the backup and run CheckDB?
In the sql server maintenance plans I have for backing up my other databases, I have checked "Verify backup Integrity"? Is this just a check that my backup is complete or is it similar to CHECKDB where it will detect corruption? Also, will running checkDB detect corruption in the log file or only the data file?
Advice is much appreciated, thank you!
karen
June 15, 2015 at 8:27 am
The only way to check a backup is to restore it.
I have never seen a SQL Server backup fail to restore, unless the file was corrupt. It's a solid process. On the other hand, I've rarely seen a third party snapshot backup restore. Across multiple vendors, and many clients or employers, I've seen this fail over and over.
Verify backup integrity verifies the backup is complete and can be read at that time. It does not verify the data - https://technet.microsoft.com/en-us/library/ms189587%28v=sql.105%29.aspx
June 15, 2015 at 8:30 am
karend211 34657 (6/15/2015)
We do sql database snapshot backups through a third party software on hourly intervals.
Check your SQL error log. Unless you see messages in the log corresponding to those hourly backups, chances are they're file backups, not SQL backups and hence not a useful backup strategy.
Too many tools claim to be able to backup through file locks without mentioning that they may result in inconsistent files when used on SQL databases.
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
June 15, 2015 at 9:38 am
I do see in the sql log that when the snapshot backups are taken that the database IO is frozen, backup is taken & IO is resumed. So I do believe the software is taking a snapshot backup. Sometimes I wonder if what I'm getting is a result of that backup or just a file copy but I am reassured by networking that what I am getting is a result of the snapshot backup.
I guess the bottom line here is I need to implement a way to verify backups and be more diligent in checking the results of checkDB that is run by the scheduled maintenance plans during off hours. To see the results, I have to go through the sql log, am I missing an easier solution here?
Ideally, I'd like to run checkDB and get notified if there are errors. This may be on the list of third party backup solutions that rarely work but I was briefly looking at sql backup by redgate I noticed they advertise automated restore and running of CheckDB to verify the backup, is this something to consider or I should come up with my own scripts?
Thank you!
June 15, 2015 at 9:57 am
Frozen IO doesn't mean consistent. The backup needs to respect transactions.
June 15, 2015 at 10:08 am
karend211 34657 (6/15/2015)
I do see in the sql log that when the snapshot backups are taken that the database IO is frozen, backup is taken & IO is resumed. So I do believe the software is taking a snapshot backup.
Every third party software that I have worked with have caused this to appear in the logs. Most of them are using Windows Volume Shadow Copy as the engine in some manner. It's not actually a snapshot, but the data and log files may very well be backed up at different times, which will cause this problem.
I'm in agreement with everyone else. I have never had a third party work in the event of a disaster.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 15, 2015 at 4:30 pm
This is going to sound terrible but I believe that you've just proven that the "3rd Party Backup Guys" aren't quite up to snuff on how to do Point-in-Time restores. I believe I'd have a talk with management and recommend getting your own backups up to snuff and then discontinuing the services of the 3rd party. Backups are a total waste of time and money if they can't be used to restore.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2015 at 12:51 pm
Thanks for all the input!
I have one other question... Is mirroring an effective solution to implement a standby database in case of corruption?
If corruption occurred in my primary, would I also have it in my secondary?
I did a bit of research, but didn't find anything specifically about mirroring and corruption with the exception of page repair in some cases.
June 16, 2015 at 12:59 pm
karend211 34657 (6/16/2015)
I have one other question... Is mirroring an effective solution to implement a standby database in case of corruption?
It's part of a solution.
No, if you get corruption in the primary it won't propagate to the secondary, but neither is it always automatically repairable with mirroring.
Backups are your final line of defence, always.
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply