SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restoring database from production to Dev


Restoring database from production to Dev

Author
Message
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 13687
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.

---------------------------------------------------------------------
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 13687
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.

---------------------------------------------------------------------
Tripz
Tripz
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 126
I restored the db using interface.
Not using the command
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
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
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
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
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 13687
as far as i know this restore is done. ending up with the files you want is all doable via a restore of a bak file, starting to take risks and outages with detaching prod databases and then risking an inconsistent test database is not necessary.

---------------------------------------------------------------------
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
Actually, it can be faster and more reliable than a backup and whats more it will always be consistent. The reason for that is in what happens when the database starts up and performs a recovery. All uncommited transactions are rolled back and makes the file consistent together with all the settings and transaction logs of the original.

As you quite rightly mentioned though, if downtime (not outage) is not an option then it isn't a viable option anyway.....
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 13687
kevaburg (3/20/2013)
Actually, it can be faster and more reliable than a backup and whats more it will always be consistent. The reason for that is in what happens when the database starts up and performs a recovery. All uncommited transactions are rolled back and makes the file consistent together with all the settings and transaction logs of the original.


sorry, it won't be able to do that without the log file. you will only get away with it with a clean shutdown, so this is a risky way to do this and is unnecessary because a straight restore will take care of it.

---------------------------------------------------------------------
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
Of course it will! It doesn't even need a clean shutdown because every time a SQL Server database starts, it performs a recovery, whether we want it to or not!

With the parameter FOR ATTACH_REBUILD_LOG the logs will be rebuilt as the MDF expects them to be! It is clean, very safe, extremely consistent and a great deal faster because you aren't restoring the data, simply attaching the database to the host instance!

Honestly, on the databases I create for testing, it has proven to me that there are options far beyond simple restores that can be multiple times faster and this is one of them!
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 13687
to everyone out there, attach with rebuild log is a last resort, please don't replace restores with it.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search