<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
»
SQLServerCentral.com
»
Editorials
»
Rolling Back a Restore
20 posts, Page 2 of 2
««
1
2
Rolling Back a Restore
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Cullen
Steve Cullen
Posted Monday, March 22, 2010 10:28 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595,
Visits: 1,226
As an option, I don't think this would be a bad thing, but for some, it could become a crutch.
Before I start a restore action, I always triple-check all my settings before I go. There are some things we do that have serious consequences for mistakes and this is one of them.
But anything that can make it safer would be a positive.
Converting oxygen into carbon dioxide, since 1955.
Post #887489
TheRealYeti
TheRealYeti
Posted Monday, March 22, 2010 11:22 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 31, 2010 10:40 AM
Points: 4,
Visits: 11
I think, it would be a great thing.
It does not happen very often that the real live database has to be overwritten, so if a proved Script / Command can help to make it less dangerous it is a great thing
Post #887525
L' Eomot Inversé
L' Eomot Inversé
Posted Monday, March 22, 2010 12:58 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 7,185,
Visits: 7,285
An option to rename the existing db instead of overwriting it might bevery nice, provided it would deal automatically with the required filename changes (can't have two DBs using the same filenames). (So of course would any other way of rolling back restore, prob=vided you could still do it a while after the restore was finished rather than not just until then).
We did something like that when rolling out upgrades (of databases that were read only on customer sites) using DTS (to copy whole databases), and it's clearly applicable in the restore case too. As DTS over a slow internet connection (which some of our customers had) was slow (and even over a fast connection was sufficiently slow to cause noticeable downtime) we always had DTS create a new DB; this also eliminated the possibility of DTS leaving a partly overwritten live DB as the result of a network or other failure. When DTS completed without errors, we would drop the current back copy, rename the current live DB to be the new back copy db, and rename the new db to be the current live db. If we later discovered that someone had dropped a cog and the new live database was not acceptable we could get thinks back up again in a very short time by renaming the live db back to the new db name and renaming the back copy to the live name.
Another point: having restore automatically destry the old db is the restore completed cleanly would be much less valuable than keeping the old DB (in renamed form) for a while, until someone decides it's no longer needed in case of something nasty coming to light.
Unfortunately I can't see any way of making this usable for really big databases.
Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
http://es.linkedin.com/in/tomthomsonsoftware
Post #887624
Ron Kunce
Ron Kunce
Posted Monday, March 22, 2010 7:55 PM
SSC-Addicted
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 11:56 AM
Points: 454,
Visits: 418
I kind of like the idea as an option only for relatively small databases. But I have to stop and think about it and why the option doesn't already exist. Most of the time whenever I needed to restore a database, it was because the that database had some how become corrupted with bad data, in which case, why would I want to have an option to keep it around in case the restore fails or is canceled? On the other hand why would I want to restore a perfectly good database.
An answer to the second question maybe, to eliminate a large accumulation of temporary or test data and reset it back to a baseline, then this option could be a good thing. Of course a transactional script could do the same thing unless the temp/test data had overwritten the data I want kept.
Possibly the reason that it is not already an option is because it would rarely be needed. Then again it does seem that it is those rare cases that always seem to rise up and bite us in the butt.
Ron K.
"
Any fool can write code that a computer can understand. Good programmers write code that humans can understand.
" -- Martin Fowler
Post #887815
L' Eomot Inversé
L' Eomot Inversé
Posted Monday, March 22, 2010 8:06 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 7,185,
Visits: 7,285
Ron Kunce (3/22/2010)
IPossibly the reason that it is not already an option is because it would rarely be needed. Then again it does seem that it is those rare cases that always seem to rise up and bite us in the butt.
I like those two sentences. The second explains why it would be an extremely good thing to have. And the first explains why MS almost certainly will never provide it.
Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
http://es.linkedin.com/in/tomthomsonsoftware
Post #887819
matt stockham
matt stockham
Posted Tuesday, March 23, 2010 12:13 PM
Right there with Babe
Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:36 AM
Points: 750,
Visits: 2,946
If anything, I would rather have the option to take a full, copy-only backup of the existing database as part of the restore (probably as part of the WITH clause).
Post #888417
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, March 24, 2010 4:24 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526,
Visits: 13,864
Matt,
Now that is a great idea as well.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #888798
info2rafiq
info2rafiq
Posted Monday, March 29, 2010 1:39 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, March 20, 2013 4:02 AM
Points: 2,
Visits: 250
Its Great Idea.
Nowadays space is not an issue. Production data more costlier than space.
Post #892156
Michael Valentine Jones
Michael Valentine Jones
Posted Monday, March 29, 2010 2:12 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 2,969,
Visits: 10,619
It wouldn’t do anything you can’t do already. You can restore a database with a new name, verify it is OK, drop the old database, and then rename the restored database.
There are so many possible scenarios for a restore that I don’t think it is really that useful. For example, a complete restore up to a point in time of a failure using the full backup and transaction log backups.
I rarely use the GUI for a restore; I find that it’s easier to use a script.
Post #892186
SQLBill
SQLBill
Posted Monday, March 29, 2010 4:05 PM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 10:47 AM
Points: 3,226,
Visits: 424
If I have a concern, I detach the existing database and change the extension to .old (ie: mydbname.mdf.old and mydbname.ldf.old). Then I restore from the backup. If anything fails, all I have to do is remove the .old part of the extension and attach the files. Once I know everything is good-to-go, I delete the old files and that frees up the space.
-SQLBill
Post #892280
« Prev Topic
|
Next Topic »
20 posts, Page 2 of 2
««
1
2
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.
