Restoring database from production to Dev

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Restoring database from production to Dev

24 posts, Page 1 of 3

1 2 3 »»»

Restoring database from production to Dev

Rate Topic

Display Mode

Topic Options

Author

Message

Tripz

Posted Thursday, March 14, 2013 8:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:20 AM
Points: 33, Visits: 124

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

tim.nyland-jones

Posted Thursday, March 14, 2013 8:50 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:40 AM
Points: 17, Visits: 237

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

Tripz

Posted Thursday, March 14, 2013 9:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:20 AM
Points: 33, Visits: 124

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

tim.nyland-jones

Posted Thursday, March 14, 2013 9:05 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:40 AM
Points: 17, Visits: 237

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

tim.nyland-jones

Posted Thursday, March 14, 2013 9:17 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:40 AM
Points: 17, Visits: 237

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

Tripz

Posted Thursday, March 14, 2013 9:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:20 AM
Points: 33, Visits: 124

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

tim.nyland-jones

Posted Thursday, March 14, 2013 9:53 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:40 AM
Points: 17, Visits: 237

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

george sibbald

Posted Thursday, March 14, 2013 10:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 5,265, Visits: 11,194

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

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

Post #1431118

Tripz

Posted Thursday, March 14, 2013 11:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:20 AM
Points: 33, Visits: 124

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

Tripz

Posted Thursday, March 14, 2013 11:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:20 AM
Points: 33, Visits: 124

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 »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions