Restoring database from production to Dev

  • We have Sql Server 2005 database server in both production and development environment.

    I have the requirement to restore a database from production to Dev.

    I my production database has one mdf and one ldf files

    But my dev environment has 1mdf and two ldf files.

    I am not sure how to restore in this one ldf backup to two ldf instance.

    How can achieve this ?? Any suggestions/ideas/approach??

  • Are you sure the production environment is using two ldf files? If so, are they both definitely log files, or is one another data file?

  • My production environment is using only ONE ldf

    and Dev has TWO ldf's

    and I need to restore from Production to Dev db

  • Ah, sorry. Still, same question - are they both definitely log files or is one an additional data file that just happens to have the extension .ldf? And are they both definitely attached to the database?

  • If your dev environment genuinely does have multiple log files, this is nothing to worry about from a backup point of view. The restore will only let you create a single log file, but you can create the other again afterwards if you need it.

  • Hi,

    Both seems to be logs files

    but one of them on dev is named as tlog_archive,

    I am not sure what it is doing.

    I tried restoring the db from production to dev and attached to the log file named as log.ldf

    but the dev website did not load

    what should be my next steps in the scenario

  • It's possible a second log file was added because the drive that the original was on ran out of space. My suggestion to minimise your heartache over this would be to detach the old dev database, delete the relevant .mdf and .ldf files, and restore the backup, putting the .mdf and .ldf files where you want to. Now you have a clean restore of the production backup.

  • have you tried to restore? please share the restore command you used and what was the error?

    ---------------------------------------------------------------------

  • I tried restoring directly by right clicking on the database and selecting the .bak file and choosing the mdf and ldf path's

    I was able to choose the mdf and only one ldf to restore

    It restored but the site did not comeup and failing to load

  • To avoid all the confusions and complications

    I created a fresh db on dev and restored the backup to this freshly created db.

    and now things seems to be clean and now the dev website is pointing to new dev db and seems to be fine now.

    But - I am still not sure how to delete the extra ldf files in the backup file restore to db which has only one mdf and one ldf

  • so if the restore worked thats not the problem. What status does the database show as in SSMS?

    What does 'select state_desc from master.sys.databases' return for your database

    If the name of the database is the same, and the logical file names are the same for the two files prod has, a straight restore database with no extra clauses should work.

    ---------------------------------------------------------------------

  • Tripz (3/14/2013)


    To avoid all the confusions and complications

    I created a fresh db on dev and restored the backup to this freshly created db.

    and now things seems to be clean and now the dev website is pointing to new dev db and seems to be fine now.

    But - I am still not sure how to delete the extra ldf files in the backup file restore to db which has only one mdf and one ldf

    what did you do with the original dev db?

    The restore would remove the extra .ldf file for you.

    ---------------------------------------------------------------------

  • I restored the db using interface.

    Not using the command

  • Instead of creating fresh DB if we restore directly it is better and after restoring all files suppose it it not in appropriate drives modify it

    or attach the database the database files in the dev server.

    Thanks
    Naga.Rohitkumar

  • How big is the mdf file? Can you tolerate a little downtime?

    I ask because I find the simplest way is to copy the mdf to the DEV environment and run the following:

    create database <db_name>

    on (filename='<path to mdb>')

    on attach_rebuild_log;

    That will create the database as per the orginal

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

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