March 20, 2010 at 11:20 am
Comments posted to this topic are about the item Rolling Back a Restore
March 20, 2010 at 12:35 pm
Interesting idea. I think it would be good idea and perhaps implemented by extending the RESTORE statement, perhaps RETAIN_OLDDB with maybe a couple of options to it.
Curious what others may think.
March 21, 2010 at 7:00 pm
I like the idea. I see the only way of implementing it is as a new command or as an option on the restore command. Obvious complications would be disk space. You couldn't require that double your production database be stored side by side on the same storage, especially not if the space was limited already.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2010 at 1:25 am
It would definitely double disk space, though for many dbs, this isn't an issue. I'd think you ought to be able to configure the "new dbs" on alternate storage if available.
March 22, 2010 at 1:47 am
As someone who measures five times, cuts once, I can certainly see the usefulness in this idea. It would be nice if there was an option to manually make the "switch" after verifying that everything went OK, or just make it do it automagically when the restore is done, though I'm not entirely sure how this would be accomplished.
March 22, 2010 at 6:27 am
So long as it was an option, I think it would be a good idea.
Of course, as a workaround, you can restore as a different name, then issue a drop on the database you're overwriting and a rename on the restored database. Wouldn't be "instant", but since the restore itself wouldn't be instant, I think it's a valid way to handle it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2010 at 8:03 am
mpa (3/22/2010)
As someone who measures five times, cuts once, I can certainly see the usefulness in this idea. It would be nice if there was an option to manually make the "switch" after verifying that everything went OK, or just make it do it automagically when the restore is done, though I'm not entirely sure how this would be accomplished.
You can also re-name the existing db (users would need to be bounced off in any case before the restore), then restore the db. If anything goes wrong, you still have the original, or you can delete it when you are happy.
...
-- FORTRAN manual for Xerox Computers --
March 22, 2010 at 8:54 am
I agree with what has been said. Disk space is a big issue. Also if your restore fails part way through will SQL server have enough resources to "roll back"?
I dislike the way restore operates from the GUI anyway. If my restore is not an exact overwrite of the backup then things get tricky. Unlike many of the Microsoft dialogs it does not warn you that you are about to overwrite existing files. I think that is should. It also does not warn you that referenced directory paths don't exist. For example some of my customers have databases on D: and for me D: is the DVD drive. Even if I use the "force restore over existing database" it does not always put the files in the right place.
Would many of your concerns be addressed if the GUI restore tools were improved?
ATBCharles Kincaid
March 22, 2010 at 9:06 am
While its an interesting idea, I'm not sure I really see a benefit here. I can already restore to a different name if I want so if this fuction is useful to me I can esentially do this already. The RESTORE statement is pretty flexible already. I'm not sure I would want default behavior restoring to a test name.
Francis
March 22, 2010 at 9:52 am
This sounds like something we do .
We have a file that tells a browser-based application the name of the database it should use. New versions of the database (built behind our firewall from wider data) are restored using a different name. If Restore is successful, the name in the file ia changed.
March 22, 2010 at 10:28 am
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.
March 22, 2010 at 11:22 am
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 🙂
March 22, 2010 at 12:58 pm
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
March 22, 2010 at 7:55 pm
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.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
March 22, 2010 at 8:06 pm
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply