October 21, 2011 at 8:20 am
Try taking the affected DB offline and then online (alter database ... set offline, alter database ... set online)
If that doesn't work, post any errors.
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
October 21, 2011 at 9:29 am
GilaMonster (10/19/2011)
No. Never ever detach a suspect or recovery_pending database as it will not always reattach (suspect will never reattach)Are you absolutely sure that file is there? Exact path, exact name.
Was there a drive problem that the drive came online later than SQL? If so, try restarting SQL Server.
I agree it won't always re-attach.. If all the files are there and in the exact same place then a restart probably won't hurt. If the files have moved I don't see much of a choice.. Luckily I don't have to deal with this kind of problem often..
CEWII
October 21, 2011 at 9:35 am
Elliott Whitlow (10/21/2011)
If the files have moved I don't see much of a choice..
Move them back where they should be and offline/online or ALTER DATABASE ... MOVE then offline/online. Or restart SQL instead of the offline/online combo if there's nothing else running on the server.
I would be very, very cautious about detaching a database in anything other than Online state as if it doesn't reattach the situation's just got worse.
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
October 21, 2011 at 10:05 am
GilaMonster (10/21/2011)
Elliott Whitlow (10/21/2011)
If the files have moved I don't see much of a choice..Move them back where they should be and offline/online or ALTER DATABASE ... MOVE then offline/online. Or restart SQL instead of the offline/online combo if there's nothing else running on the server.
I would be very, very cautious about detaching a database in anything other than Online state as if it doesn't reattach the situation's just got worse.
Provided moving them back is possible I would agree. My thought is damaged drive. But I agree with your point.
CEWII
October 21, 2011 at 10:25 am
If the drive is damaged you're probably restoring from backup.
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
October 21, 2011 at 10:51 am
Below is the error message I'm getting after running: Alter database DevTestdb set online
Msg 5173, Level 16, State 1, Line 2
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 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 945, Level 14, State 2, Line 2
Database 'DevTestdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
October 21, 2011 at 11:02 am
Oh, so the log file exists, but it's from another database (or something's been badly mangled in it.)
At this point it's pretty much restore from backup or hope a repair works and doesn't lose too much data. I recommend the backup.
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
September 12, 2012 at 6:52 pm
Take off line - Modify file and then take online worked for me. Thanks!
September 13, 2012 at 2:07 am
Honestly guys, no backup and they are to be DBA's?
First thing a DBA should do is to make sure he can restore every database to a point in time depending on needs, period.
To be able to do that he/she needs to have backups on every database (if in FULL RECOVERY MODE) aslo sufficient LOG file backups.
Shame to all of you who doesn't!
September 13, 2012 at 2:28 am
Please note: year old thread.
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
January 16, 2014 at 8:13 am
When this happened to me I was able to take the DB offline and then bring it back online.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply