July 9, 2008 at 5:00 am
I have to move my database to another machine for that i deattached the database and copy the mdf file to new machine and attached the file but at the time of attaching an error occur "the physical file .lbf is not found .how can i solve this?
July 9, 2008 at 5:16 am
Copy the .LDF file also.
.MDF file is data file. LDF file is Log file.
July 9, 2008 at 5:38 am
Be sure to point to the correct filelocations !
(and they must be reachable for the sqlserver service account !!)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 9, 2008 at 8:11 am
BEst Option will be BACKUP and Restore on the New Server.
After the Move, Change the compatibility level and RUN these commands.
DBCC UPDATEUSAGE and DBCC CHECKDB
Maninder
www.dbanation.com
July 9, 2008 at 10:22 am
Mani Singh (7/9/2008)
After the Move, Change the compatibility level
Why would you change the compatibility level after moving a database to another server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 10:37 am
Gila is right 🙂
I forgot to mention, that if you are moving form a lower version of SQL (2000) to 2005, then change the cmptlevel, but be sure to RUn the DBCC CHECKDB on the Database.
Maninder
www.dbanation.com
July 10, 2008 at 5:09 am
I second the backup and restore method. Create the databases on the new machine, then restore a backup from the old machine. Copying the mdf and ldf files is a recipe for bad results. We copy and restore backups all the time when moving data between production and test/acceptance environments. If this is something you do regularly, it's pretty easy to script the process.
July 10, 2008 at 5:32 am
Hi,
backup & restore is the best way to move databases from one box to another. Copying mdf & ldf is a bad practice.
Regards,
Rajesh
July 10, 2008 at 6:51 am
BAD-DBA-DAB (7/10/2008)
Hi,backup & restore is the best way to move databases from one box to another. Copying mdf & ldf is a bad practice.
I don't neccessarily agree with that statement. Both are acceptable where I work. As a matter of fact, we just did a migration where we simply copied our mdf and ldf files and did a sp_attachdb and it worked flawlessly. It was actually quicker than backing up/restoring 30+ databases as we scripted everything ahead of time and when the time came to move to production, simply ran the script. 😎
-- You can't be late until you show up.
July 10, 2008 at 7:07 am
When you attach the .MDF file back at the destination, you can manually remove the log file from the list and after attaching put the DB in FUll Mode and you can create a log file without any issues. However the bad side to this is that you dont benefit from the compression advantages of a backup.
July 10, 2008 at 8:55 am
JeeKay (7/10/2008)
When you attach the .MDF file back at the destination, you can manually remove the log file from the list and after attaching put the DB in FUll Mode and you can create a log file without any issues.
Except that you have a good chance of having a corrupt database if it wasn't shut down cleanly. Deleting a log file is not something you shoule ever consider doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2008 at 8:58 am
BAD-DBA-DAB (7/10/2008)
Hi,Copying mdf & ldf is a bad practice.
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2008 at 10:07 am
pound for pound, i have found it quicker to detach copy then re attach databases rather than backup and restore. Also dont forget the microsoft scripts to export your logins too 😉 .
BAD-DBA-DAB (7/10/2008)
Copying mdf & ldf is a bad practice.Regards,
Rajesh
where did you get that idea from?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2008 at 11:29 am
I did not say DELETE log file. What i said was to have that removed from the list of files when you attach it. You have to have a shutdown and any DBA should make a clean shutdown before detaching the DB. Try it on a test box and you will get it.
July 11, 2008 at 12:32 am
JeeKay (7/10/2008)
I did not say DELETE log file. What i said was to have that removed from the list of files when you attach it.
Apologies. I misread.
However, if the log isn't present, attaching the DB with only the MDF forces SQL to rebuild the log, and it's only possible if the database was shut down cleanly
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply