Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Restoring database from production to Dev Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:39 PM
Points: 33, Visits: 126
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??


Post #1431025
Posted Thursday, March 14, 2013 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 3:14 AM
Points: 17, Visits: 268
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?
Post #1431030
Posted Thursday, March 14, 2013 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:39 PM
Points: 33, Visits: 126
My production environment is using only ONE ldf
and Dev has TWO ldf's
and I need to restore from Production to Dev db
Post #1431034
Posted Thursday, March 14, 2013 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 3:14 AM
Points: 17, Visits: 268
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?
Post #1431038
Posted Thursday, March 14, 2013 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 3:14 AM
Points: 17, Visits: 268
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.
Post #1431053
Posted Thursday, March 14, 2013 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:39 PM
Points: 33, Visits: 126
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
Post #1431059
Posted Thursday, March 14, 2013 9:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 3:14 AM
Points: 17, Visits: 268
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.

Post #1431081
Posted Thursday, March 14, 2013 10:43 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 5,989, Visits: 12,927
have you tried to restore? please share the restore command you used and what was the error?

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

Post #1431118
Posted Thursday, March 14, 2013 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:39 PM
Points: 33, Visits: 126
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
Post #1431145
Posted Thursday, March 14, 2013 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:39 PM
Points: 33, Visits: 126
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
Post #1431148
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse