SQL Server Performance Forum – Threads Archive

Suspect Mode

Hi,
I was given a SQL 2000 backup to restore on SQL 2005 server. I restored the database, but now it went in the suspect mode which i think is because of the missing log file. How am I supposed to fix this in SQL 2005? Cheers
Roopa

---

Did you copy the files in the server directory and attach? Madhivanan Failing to plan is Planning to fail

---

True,if the location of data& log are different to SQL 2000 server then you have to use WITH MOVE option. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

I did a restore from the backup i got. And my maintenance palns filed when i did a full backup showing the error about the missing log file. And today it went into the suspect mode. Regards
Roopa

---

So you are missing the log file completely…according to what you say Try using <alter database…add log file> to add a log file to the database. But check for the log file location for the d/b in SQL 2000 and compare it with SQL 2005 server as Satya suggested. I think it should solve your problem Satya

---

Actually that backup file is from legacy server, and I do not have access to those servers. The application team put the backup file in a share and gave me access to that share. So i copied that file over and restored it. Regards Roopa

---

So the error has occured for sure while you restored it. As Satya suggested the error is definately because the data and log files location on both the servers are different, and you might have done the restore d/b operation without using ‘with move’ option.

---

I restored through GUI.
Regards Roopa

---

Generally, restore should not complete if the log file location is not exists on the sql 2005 server. But you don’t need to have old database online to check the log file location…
It can be found in your sql error log or if you have your sql server 2000 database backup you can run the RESTORE FILELISTONLY command to get the logical and physical name and location… restore filelistonly from disk = ‘\BackupSErvere$ackup_DatabaseName.bak’
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Better you check for any hardware issues too, as you say it went suspect without any reason. As suggested take a trip to Books online for RESTORE statement and do not completely depend upon the GUI based methods.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>