copying .mdf and .ldf files
-
Hope there is a way to do this. Is there a tool or some way to access .mdf files when they have been copied to another machine, even though they were not detached on the original machine?
-
How did you get them copied in the first place?
If the SQL service was stopped you should be able to reattach them.If the files were open... how did you copy them... and I don't think it would be a quick fix to get the files reattaced.
Lukas Botha
Lukas Botha
-
The files were not opened.
I stopped the MSDE and also the SQL service, and copied the files to a mapped drive.
But I was told that they should have been detached first.
The hard drive was overwritten after copying the mdf and ldf files.
-
You should be able to reattach the files with no problem if you can run the sp_attachdb statement. Don't know how you are going to do it in MSDE ... it does'nt have command line.... maybe try isql
?If the Service was stopped the files are 'detached'.
Lukas Botha
Lukas Botha
-
The advice you received was right: you should never just copy the files after the service is stopped. You need to detach them first. This is a common error.
If you can, detach the original database and copy the files over again, reattach the original database and reattach the copied files to the second server. Alternatively, just perform a backup and restore.
If you still want to use the files you copied earlier, look here at the post by Jasper Smith but note that this is not a supported Microsoft option.
Lukas, the database is not 'detached' in SQL Server terms just because the service is stopped.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
That post by Jasper Smith says to make sure you have a copy of PowerDVD301_2_Data.MDF.
Can anyone tell me where I can get that?
Thanks,
wb
-
By the way....SQL Server does not like to use .mdf/.ldf files across a network. It likes them to be on the same system as the SQL Server software.
-SQLBill
-
PowerDVD301_2_Data.MDF is just the name of the data file that needs to be 'attached' in the example. Replace it with the name of your mdf file.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Hi Peter.
Do me a favour, do a quick test:
Lest use pubs, locate your database files eg.
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs.ldf
Now comes the difficult bit. Stop SQL Server, and rename the files lets say
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs1.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs1.ldf
Restart SQL Server, If you use Enterprize Manager you will notice that pubs is now suspect, go on right click and delete it...
No more pubs, this is where we continue with WB's problem.
Now in Enterprize Manager right click and click on Attach Database, locate your database file thru the GUI.
You will notice that after locating your mdf file it will locate your ldf file automatically, BUT
there will be 2 red crosses next to the file path.Next rename the file that is shown in the path box to point to the renamed file eg.
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs1.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs1.ldf
You should now have 2 green ticks next to the files, remember to change the database owner to the correct owner eg. sa. The system will pickup the database name from the mdf file.
Now who said that copying files when the service was stopped is a mistake, I would like it to be seen as a different way of solving a problem
Only one problem remains!!!! MSDE is not licensed for use af any management tools, eg not Enterprize manager of SQL Query Analyser.
Good luck WB, let us know if you managed to solve this one.
Lukas
Lukas Botha
-
Lukas, that was a very strange way to prove your point, as you could just as easily have ran sp_attach_db 'pubs', 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs1.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\pubs1_log.ldf'
Great for you if this practice has not caused any problems. Unfortunately for me, I have had 2 unpleasant experiences where simply copying the mdf and ldf files after the service was stopped was not sufficient to reattach the database later (120 Gig databases with 400+ concurrent users). Thus, for me, it was a mistake which I would rather not see anyone repeating.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
Thanks a million to you guys for all of your help.
I was able to get them to open, after many hours of trying different things.
I still don't know if the problem (or the solution) was more of a MSDE thing or if it was within the app I'm using, which uses MSDE - (Popkin System Architect). After I finally got the steps right, it let me attach the copied .mdf files to a workstation MSDE database (even though someone in SA support said it would not attach if it wasn't detached), and then open the file.
Also, the .ldf file was apparently not needed. The .mdf file would not open until I renamed the .ldf extension to something else (maybe just coincidence?), and then once the .mdf did open, a new very large .ldf was created (went from 6,720 KB to 15,040 KB).
In any case, now I'm really interested in learning more about SQL server, if I can just find the time.
Thanks again, I'm sure I'll be learning a lot from reading this forum from now on.
wb
-
I would have to agree with Lukas. Although we no longer do this, part of our backup strategy used to be to stop SQL to allow the system backup to pick up the mdf/ldf files.
I've used those files more than once to either restore a database or create a copy of it. Peter, I'm not sure why you had problems with this method, but I would be surprised if it was related to whether or not sp_detach_db was used.
Steve
-
If you take a look at the problem that Jasper Smith solved in the post I mentioned, you would see that the user failed to even attach using sp_attach_single_file_db. Usually, you can use sp_attach_single_file_db to attach only an mdf that was copied when the service was shut down (SQL Server will just recreate the ldf), but obviously, there are exceptions.
I happen to be among the privileged few, and thought I should share the experience. See here, here, here and here (among others), for other members of the SHDTFD (Should Have Detach The Freaking Database) club.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
That is what I call the BLACK ART of Information Technology, 110 ways to solve a problem, but only one seen as the 'correct' way.
I guess its all related to the angle or should I say BIT you look from.
Lukas
Lukas Botha
-
Peter,
I can't navigate to the forum links you posted as I'm getting blocked by our internal web policies. If you can elaborate on the issue some as to what specific problems you experienced, I'd appreciate it. We've successfully used this method to move databases between servers before (ie. shut down service, copy files, re-attach on new server) without issue and without detaching the source db and have even had this process recommended by a vendor a while back.
Shutting down SQL Server closes the files and allows you to copy or move them at will. I can understand that if you did this and then tried to re-attach on the same server how you'd have issues, but when moving the files to a new server or creating a development database I don't see where the problem is. Specifically I suppose my real question is, does the act of detaching the database do anything extra in terms of closing the files that simply stopping SQL Server doesn't do? Thanks.
My hovercraft is full of eels.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply