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 11:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 5,878, Visits: 13,006
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.


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

Post #1431149
Posted Thursday, March 14, 2013 11:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 5,878, Visits: 13,006
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.


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

Post #1431155
Posted Thursday, March 14, 2013 11:46 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 restored the db using interface.
Not using the command
Post #1431163
Posted Wednesday, March 20, 2013 1:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:23 AM
Points: 585, Visits: 1,295
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
Post #1433029
Posted Wednesday, March 20, 2013 5:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 324, Visits: 533
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
Post #1433131
Posted Wednesday, March 20, 2013 6:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 5,878, Visits: 13,006
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.

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

Post #1433173
Posted Wednesday, March 20, 2013 6:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 324, Visits: 533
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.....
Post #1433184
Posted Wednesday, March 20, 2013 7:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 5,878, Visits: 13,006
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.



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

Post #1433220
Posted Wednesday, March 20, 2013 8:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 324, Visits: 533
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!
Post #1433228
Posted Wednesday, March 20, 2013 8:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 5,878, Visits: 13,006
to everyone out there, attach with rebuild log is a last resort, please don't replace restores with it.

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

Post #1433236
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse