InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 20:05:38 GMT20http://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxPlease note: year old thread.Thu, 13 Sep 2012 02:28:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxHonestly 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!Thu, 13 Sep 2012 02:07:52 GMTwebtomtehttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxTake off line - Modify file and then take online worked for me. Thanks!Wed, 12 Sep 2012 18:52:24 GMTSQL_Surferhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxOh, 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.Fri, 21 Oct 2011 11:02:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxBelow is the error message I'm getting after running: [code="sql"]Alter database DevTestdb set online[/code][quote]Msg 5173, Level 16, State 1, Line 2One 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 2Database '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 2ALTER DATABASE statement failed.[/quote]Fri, 21 Oct 2011 10:51:20 GMTEjSQLmehttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxIf the drive is damaged you're probably restoring from backup.Fri, 21 Oct 2011 10:25:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspx[quote][b]GilaMonster (10/21/2011)[/b][hr][quote][b]Elliott Whitlow (10/21/2011)[/b][hr]If the files have moved I don't see much of a choice..[/quote]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.[/quote]Provided moving them back is possible I would agree. My thought is damaged drive. But I agree with your point.CEWIIFri, 21 Oct 2011 10:05:48 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspx[quote][b]Elliott Whitlow (10/21/2011)[/b][hr]If the files have moved I don't see much of a choice..[/quote]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.Fri, 21 Oct 2011 09:35:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspx[quote][b]GilaMonster (10/19/2011)[/b][hr]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.[/quote]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..CEWIIFri, 21 Oct 2011 09:29:38 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxTry taking the affected DB offline and then online (alter database ... set offline, alter database ... set online)If that doesn't work, post any errors.Fri, 21 Oct 2011 08:20:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxYes, I'm sure that the file is there b/c I checked it to make sure it is there when I saw the error message that I posted. I believe there was an issue with the server and did crash, but we have other DBs on the same SQL Server, and this is the only DB with this issue.Fri, 21 Oct 2011 08:16:27 GMTEjSQLmehttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspx[quote][b]R_S (10/19/2011)[/b][hr]Is the file there in the location showing in the error logs.Try to detach and reattach providing the new path.[/quote]Read Gail's post just above.Wed, 19 Oct 2011 15:09:09 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxIs the file there in the location showing in the error logs.Try to detach and reattach providing the new path.Wed, 19 Oct 2011 14:50:36 GMTR_Shttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxNo. 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.Wed, 19 Oct 2011 14:15:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxI'd probably go with a detach/re-attach, then you could specify all the file locations. Gail? Your thoughts?CEWIIWed, 19 Oct 2011 13:57:08 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxyes, the file is there. This is a Dev DB, we have been doing some work on it that would take us a while to recreate. So what is my next step?Wed, 19 Oct 2011 13:45:33 GMTEjSQLmehttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxBeyond the monitoring software I use, I have been configuring policies on all my SQL 2008 servers that check to be sure that all but tempdb have had a full backup within the last 25 hours. I haven't lost a database in a long time and even longer without a backup, I'd like to keep it that way. I've seen a DBA get fired over stuff like this, the last case the DBA did have a backup, from a month ago which in most cases is as bad as not having one at all.. He got lax and even though the monitoring software was sending him "love notes" he didn't resolve the issue. I will give him some cover, he was getting a fair number of alerts but after so long you'd think he would have seen a no backups message..Even with monitoring in place I will periodically go out to each server and run a script I have that tells me when every single database got its last full backup.As a DBA a little paranoia goes a long way and some of my "paranoid code" has saved my bacon (and others) more than twice..CEWIIWed, 19 Oct 2011 13:15:26 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspx[quote][b]Elliott Whitlow (10/19/2011)[/b][hr]If this is an important database I hope you can fix it or your resume is up to date. I've seen businesses go under because of things like this and even if they don't the DBA is usually toast.. [/quote]So far this month I'm 0-for-2 on corrupt databases without backups. Both the others were complete and total losses, no repair, no data could be extracted.Wed, 19 Oct 2011 13:01:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspx[quote][b]EjSQLme (10/19/2011)[/b][hr]No, unfortunately, I don't have a backup file.[/quote] (facepalm) If this is an important database I hope you can fix it or your resume is up to date. I've seen businesses go under because of things like this and even if they don't the DBA is usually toast.. Sorry man..And the ULTRA important question already asked, why no backup?CEWIIWed, 19 Oct 2011 12:43:51 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxAnother production database without a backup? Why?[quote]Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file [b]'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF'[/b][/quote]Is the file there?Wed, 19 Oct 2011 12:33:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxNo, unfortunately, I don't have a backup file. Below is an error message that I can see in the log. [quote]2011-10-10 15:00:50.03 spid27s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF'. Diagnose and correct the operating system error, and retry the operation.2011-10-10 15:00:50.03 spid27s File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF" may be incorrect.[/quote]Wed, 19 Oct 2011 12:27:16 GMTEjSQLmehttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxPlease open the SQL error log and find all errors relating to that database.Got a backup?Wed, 19 Oct 2011 11:18:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxHi,After running the query you provided, the output is: RECOVERY_PENDING.I'm seeing the Shutdown status at the General page of the DB Property section. In the Database subsection, the Status of the DB is Shutdown. In looking at other DBs, they have Normal Status.Wed, 19 Oct 2011 11:15:20 GMTEjSQLmehttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxWhere are you checking the status of the database. As per BOL, a database can have one of the following states.online, offline, restoring, recovering, recovery pending, suspect and emergency.what is the result of the below query.select state_desc from sys.databases where name='<your database name>'Wed, 19 Oct 2011 08:59:28 GMTcindyazhttp://www.sqlservercentral.com/Forums/Topic1192912-146-1.aspxHI,We have a database that the status is set to Shutdown. How can I change the DB Status to Normal.Thanks,Wed, 19 Oct 2011 08:52:45 GMTEjSQLme