<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
Restoring database from production to Dev
24 posts, Page 2 of 3
««
1
2
3
»»
Restoring database from production to Dev
Rate Topic
Display Mode
Topic Options
Author
Message
george sibbald
george sibbald
Posted Thursday, March 14, 2013 11:20 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 5,269,
Visits: 11,208
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
george sibbald
george sibbald
Posted Thursday, March 14, 2013 11:25 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 5,269,
Visits: 11,208
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
Tripz
Tripz
Posted Thursday, March 14, 2013 11:46 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:20 AM
Points: 33,
Visits: 124
I restored the db using interface.
Not using the command
Post #1431163
naga.rohitkumar
naga.rohitkumar
Posted Wednesday, March 20, 2013 1:40 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 525,
Visits: 1,004
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 & Regards
NAGA.ROHITKUMAR
Post #1433029
kevaburg
kevaburg
Posted Wednesday, March 20, 2013 5:03 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 159,
Visits: 191
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
george sibbald
george sibbald
Posted Wednesday, March 20, 2013 6:31 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 5,269,
Visits: 11,208
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
kevaburg
kevaburg
Posted Wednesday, March 20, 2013 6:59 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 159,
Visits: 191
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
george sibbald
george sibbald
Posted Wednesday, March 20, 2013 7:52 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 5,269,
Visits: 11,208
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
kevaburg
kevaburg
Posted Wednesday, March 20, 2013 8:00 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 159,
Visits: 191
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
george sibbald
george sibbald
Posted Wednesday, March 20, 2013 8:06 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 5,269,
Visits: 11,208
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 »
24 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
