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 12»»

Rolling Back a Restore Expand / Collapse
Author
Message
Posted Saturday, March 20, 2010 11:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:49 AM
Points: 33,169, Visits: 15,302
Comments posted to this topic are about the item Rolling Back a Restore






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #886934
Posted Saturday, March 20, 2010 12:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 23,229, Visits: 31,927
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #886947
Posted Sunday, March 21, 2010 7:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 21,626, Visits: 15,283
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #887133
Posted Monday, March 22, 2010 1:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:49 AM
Points: 33,169, Visits: 15,302
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #887190
Posted Monday, March 22, 2010 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:16 AM
Points: 3, Visits: 162
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.
Post #887199
Posted Monday, March 22, 2010 6:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #887309
Posted Monday, March 22, 2010 8:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 751, Visits: 1,915
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 --
Post #887397
Posted Monday, March 22, 2010 8:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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?


ATB

Charles Kincaid

Post #887428
Posted Monday, March 22, 2010 9:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 9:48 AM
Points: 2,183, Visits: 1,971
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
Post #887436
Posted Monday, March 22, 2010 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 3:41 AM
Points: 15, Visits: 355
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.
Post #887469
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse