moving database to another machine

  • 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?

  • Copy the .LDF file also.

    .MDF file is data file. LDF file is Log file.

  • 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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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.

  • Hi,

    backup & restore is the best way to move databases from one box to another. Copying mdf & ldf is a bad practice.

    Regards,

    Rajesh

  • 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.

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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" 😉

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 15 total)

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