I have program that uses SQL. For some reason the DB was on a USB stick.
Now we want the DB to be on the hard drive. From the DB manager I detached the database, copied the .mdf file to the HD and tried to reattach the database. However I am getting this error:
Where G:\ is the path of the USB stick.
Where is it getting the old path from? Is it written somewhere in the .mdf file?
You copied the .LDF to the same location that you copied the .MDF, right? You don’t mention that - just the .MDF - so not sure if that’s an oversight or if you didn’t actually copy both files. You need both of them, not just the .MDF.
You might want to try a SQL Server discussion forum. Here’s one and I’m sure there are others:
http://www.utteraccess.com/forum/Microsoft-SQL-Server-f32.html
I’m a DBA on Oracle and Access database so I don’t have a real clue on your problem.
Yes, I got both files, although for some bizzare reason the names are xxxx1.mdf and xxxx2.ldf
Should I rename both so they have the same name?
Renaming the file didn’t work. For some reason it tries to access the G:\xxxx_log.LDF file. THis file now resides in C:, along with the .mdf file.
I should mention that this is a virtual machine that I created from an actual machine and G: was the USB stick that originally contained the database.
Is the path for the LDF file contained in the MDF file? Where is it getting the G: path from?
Is it possible that the LDF isn’t the correct LDF file? I’m curious because you mention the name difference.
As far as I know, SQL just looks for an LDF in the same location as the MDF, with the same name. Not sure if you can change that or not, but it is odd that it’s looking at G:. Hmmmm…
Another thought: Check in your Database properties for the default location for .LDF files. Maybe that’s set to G?
Right click on the DB server name, Choose Properties, “Database Settings” tab, at least in my version of SQL.
Assuming you’re using a 2005+ version of MS-SQL and attempting to re-attach via the Management Studio GUI…
After you Add the MDF file, look down in the lower box. It should list both your mdf and ldf files there. Is the LDF file listed correctly?
In the Current File Path section, you can click the little “…” box to change the file name and path, if needed.
Does that help?
This is what I came in to say… The DB is probably still looking for the files in the original location. You need to change the path for them.
Note sure what version of SQL Server Management Studio you are using, but in 2008 R2 it is as follows:
Right click DB name → Properties → Files → Examine the Path column for both the LDF and MDF.
It looks to me like someone gave the log file an odd name, so that’s probably what needs to be fixed. It could also be the default log path set on the server, if you didn’t put the LDF file where all the others are.
The db file does contain the LDF file information, but SQL can’t read that before the database is attached. It uses a default naming convention and path when it’s trying to attach files.
If your LDF filename or path is non-standard, you have to set it manually.
I can post TSQL to do the attach if the GUI fix listed above doesn’t work, just let me know.
If you are unable to get this to work by detach/attach, try backing up the database and then restoring to the new file location. You can put the files wherever you want during the restore.
I made a G: drive on the virtual machine and put the files there. Now I get this error message:
The LSN (various numbers here) passed to log scan in database XXXX is invalid
Yeah, that’s looking like possibly a corrupt database. Or at least a corrupt logfile.
Do you still have the original database up and running? Do you have any backup copies of the database?
If so, the easiest would be to do a backup/recovery rather than detach/attach.
You can attach just the mdf file without using the ldf at all. If the logfile is the only thing corrupted, that might get ya working. Let me go find some code…
Thanks, I will copy the files again from the physical machine to the virtual one.
After some research, that method (attaching without the logfile) is specifically not recommended when you’re having corruption issues.
Unless the file was corrupted while copying over from the USB stick (which should be solved by re-copying the files per your last post), at this point I’d say your options are down to (1) restore from a good backup, or (2) hire a pro.
If your original database is still up (or you can get it back up), run a ‘DBCC checkdb’ on it and clear up any issues. Then take a new backup and restore to your new location from the validated backup.
If you can’t use the original database but have a backup for it, restore that backup and then run the DBCC checkdb.
Otherwise, you’re going to need some expert assistance to get out of this one. Like these folks.
You could try asking in some of the SQL specific forums. Some of those guys might be able to walk you through it. Or many of them are consultants for hire just for these sorts of problems.
Why did you do detach instead of backup? Typically detach/reattach is only useful if the DB stays in the same path on the same server. (I know you can use it to move DBs, but I don’t think it’s very reliable for that.)
The normal way to do what you want is to backup the database from the USB key, then restore from the backup to the new location.
Seconded. Detach/attach can be quicker if you’re dealing with a very large database, but it doesn’t really have many advantages you’d typically encounter otherwise.
I know zip about SQL. That’s what the helpdesk that supports the specific software suite told me to do! And now they won’t pick up the phone
For some reason, detach/attach is the go-to method for a lot of people (including helpdesks) who don’t know much about SQL. And a lot of software vendors use MSSQL but still don’t know crap about it. That was the first way I learned to copy databases, because somebody told me to do it that way.
Aside from the hassle factor, it’s also a lot riskier. Nobody ever (*) screwed up a database by making a backup.
(*)OK, someone, somewhere has probably managed this. I don’t know how, but I’ll bet they have. People can do amazing things when they try hard enough.
This is a little bit of a tangent, but here’s how you mess up with backup: while running the backup, the database is still live and transactions can be executed against it. If you don’t run things right, you can lose some of those transactions when you restore the database. The backup process is usually smart enough to deal with this: it first backs up the database (which doesn’t include the new transactions), then backs up the log as the final step (which does include them). Then when it restores the database, it applies the new transactions to the recovered DB.
But you can still mess this up - maybe the database is in Simple recovery mode, or you don’t take the database offline before the backup is completed. Detach/attach avoids this by taking the database offline immediately.
This is by no means an argument for using detach/attach instead of backup/restore. Instead just run the backup/restore correctly, and be aware of the potential pitfalls.