March 14, 2013 at 8:48 am
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??
March 14, 2013 at 8:50 am
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?
March 14, 2013 at 9:01 am
My production environment is using only ONE ldf
and Dev has TWO ldf's
and I need to restore from Production to Dev db
March 14, 2013 at 9:05 am
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?
March 14, 2013 at 9:17 am
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.
March 14, 2013 at 9:28 am
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
March 14, 2013 at 9:53 am
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.
March 14, 2013 at 10:43 am
have you tried to restore? please share the restore command you used and what was the error?
---------------------------------------------------------------------
March 14, 2013 at 11:13 am
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
March 14, 2013 at 11:19 am
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
March 14, 2013 at 11:20 am
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.
---------------------------------------------------------------------
March 14, 2013 at 11:25 am
Tripz (3/14/2013)
To avoid all the confusions and complicationsI 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.
---------------------------------------------------------------------
March 14, 2013 at 11:46 am
I restored the db using interface.
Not using the command
March 20, 2013 at 1:40 am
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
March 20, 2013 at 5:03 am
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