Not a valid SQL Server database file - attach errors

Question

Not a valid SQL Server database file - attach errors

S Hodkinson

SSCertifiable

Points: 7529
More actions
July 25, 2007 at 6:46 am

#69857

We recently detached a database but when we came to reattach it again, the following error was reported
'The file you specified is not a valid SQL Server database file'
It will not attach and is the Live system so we need help soonest!
Madame Artois

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

I would try and avoid using detach/attach if at all possible, and especially with a live database. This is because database corruption may prevent you from re-attaching the database, which I suspect could be your problem!

The safest way would be to backup the database and then to verify the backup files - assuming that you also perform regular DBCC CHECKDB(). You should also have earlier backup files that you can fall back on.

If database corruption is indeed your problem, then it is best to log a call with Microsoft.

Paul

jmcgarvey Default port Points: 1489 More actions · Answer 2

I have used detach/attach numerous times over the years with no problems. This is a fully acceptable approach in moving a database on a local machine. I will use a backup to move a database across platforms.

In answer to you question I have run across this error several times and every time it is been caused by my inability to type. Look very closely at you script and you will most likely find a typing error.

Finally, if the problem persists then you may indeed have a corrupt file. If the database was functional before the detach then that should not be the case. At this point you have a couple of options,

Attempt to use sp_attach_single_file_db. Look in BOL. This will eliminate the log file from the equation. Don't worry it will be rebuilt.
If that fails then your only recourse is to restore a backup. Sorry to be the bearer of bad news.

Either way Good Luck and remember that BOL is a DBAs best friend.

John

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

I should qualify my previous post by saying that if you have been performing detach/attach without performing regular database integrity checks and making regular backup of your database, then using detach/attach can leave you w/o anything to fall back to if you do encounter corruption in your database.

On the other hand, if you have been doing all of the above, then detach/attach would be safe to do.

Paul

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 4

This is just to lt you know that we found the problem. It appears that the third party supplier had created two mdf files and no ldf file i.e. <Name>.data.mdf and <Name>.log.mdf!

Plus the file named as the data file was actually the log file and vice versa. We have never seen anything like it before. Has anyone else?

Madame Artois

Paul Mu SSCertifiable Points: 5547 More actions · Answer 5

Interesting file names! Basically, the 3rd party supplier did not follow the naming convention for database files.

However, when I tried this on SQL Server 2005, it appears to work fine! Maybe it only affects versions of SQL Server 2000 and below? How did you manage to resolve the database attach in your case?

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 6

By renaming what we thought was the log file, re-attaching the data file (even though it was called a log file). This then created a new log file but with the right file extension.

So we know have to check the extensions as they are both called <name>_log files.

Madame Artois