July 8, 2009 at 1:18 pm
Hey folks, I'm good with SQL (very good for a QA guy) but no where near a DBA. I tried several searches but was not able to find anything here that discussed this issue. The BOL reference stuff on 'restore' is very confusing to a non dba, and I'm totally lost as to what options we need to utilize to a create a SQL 'query' to perform a db restore that works for our needs.
The background is that we're creating suite of regression test scripts that run against a web UI, Because the scripts create and alter data they need to have the back-end DB (SQL Server 2005 currently) in a pre-determined state when we start so we don't run into conflicts such as trying to create a second copy of a unique name, or delete an items that's already deleted, etc.
The needs are as follows.
1) need to be able to easily create new 'reference' backups (snapshots?) for when we update the test data, overwriting the old reference backups. a simple SQL script we could execute manually for this part would be handy as it would eliminate human error doing it via the UI.
2) Need to be able to restore the reference backup of the database to return it to the known starting position. Unlike a production environment I care not about preserving ANY changes since the backup, in fact quite the opposite, I want the whole thing back the way it was with no trace of what the prior tests and perhaps adhoc testing have done to it.
3) Needs to be accomplished via a SQL query that I can execute from a remote machine, we want this to be fully automated.
4) a webserver likely has a connection to the db at the time we go to restore it, which has been causing us issues with errors about unable to get exclusive access to the database in order to do the restore, so we need some way to get around that also.
for those that are curious, The test scripts themselves are written in Ruby, using a browser driver gem called WATIR (Web Application Testing in Ruby). The Ruby language has libraries (gems) which provide methods to connect to SQL via ODBC and other means. That part is pretty straight forward from our perspective, it's just a matter of knowing what db we connect to (I'm suspecting it might need to the the 'master' system db), and what SQL commands to fire at it once we've connected. And of course what sort of return codes to look for that indicate success or failure.
I can if needed provide some examples of what we've tried, but since so far none of them have worked without issues (mostly the exclusive access thing, but also knowing the correct options to do a 'from scratch, not trying to preserve anything, restore') I'm not sure what value they would provide to the discussion.
I realise this is nearly a 'please do my job for me' type of request, and I normally HATE it when other folks do that, so trust me it totally galls me to be askign this way. Please trust me when I say we've thrashed it around for a day or so and are just plain stuck.. We can go over what we've tried if folks want, but I'm worried we might be so far off base that it might just confuse things more to do it that way. (on the upside, it we can work out some good simple scripts for this, it will probably be of use to a lot of other testers faced with the same situation)
--Chuck
July 8, 2009 at 1:28 pm
If you're using Enterprise or Developer edition, create a database snapshot before the test starts and restore the database from the snapshot afterwards.
If you're using standard edition, I would take a backup before and then, when the DB needs to go back to that point, take it offline (kicks everyone out) and restore from that backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2009 at 1:48 pm
If you want to run procedure from scripts, rather than the GUI, you can use the GUI to get everything set, then choose "Script to Query Window". That will build a script for you of what you were about to run through the GUI. You can then save, modify, build upon, schedule etc as needed.
July 8, 2009 at 2:12 pm
Was it really necessary to post this question 6 times?
July 8, 2009 at 5:12 pm
Michael Valentine Jones (7/8/2009)
Was it really necessary to post this question 6 times?
if I had KNOWN I was doing that, I wouldn't have.
it was giving me a big nasty server error when I posted. so I had no idea the post was going through, it looked to be nothing but fail..
I notified the webmaster of the error (since it had full error text, which rather suprised me, sites normally use custom errors, and don't expose stack dumps to outside users, it gives away too much info regarding the inner workings of the site)
What you saw was a result of me re-trying the post after the failure.
I'm in the process of cleaning up the other's and have told the system to delete them, but not seen any go away so far.
July 9, 2009 at 6:24 am
Michael Valentine Jones (7/8/2009)
Was it really necessary to post this question 6 times?
Seems like yesterday's issues with the site are continuing today. Look at all the other multiple posts:w00t:
Mods need to do a little cleanup.
July 9, 2009 at 1:24 pm
homebrew01 (7/9/2009)
Michael Valentine Jones (7/8/2009)
Was it really necessary to post this question 6 times?Seems like yesterday's issues with the site are continuing today. Look at all the other multiple posts:w00t:
Mods need to do a little cleanup.
I tried to delete the duplicates, the system lets me go through the motions of doing so, gives me a confirm dialog and everything.. but nothing deletes, not even the ones with no replies in the topic.
go figure.
What's REALLY ironic, is that you recall I said I see a rather detailed error message? Well, the core of the message has to do with an ODBC connection to a database, and what appears to be a permissions problem. it's shall we say 'not unfamiliar' to me, given I've had to setup testbeds with websites talking to databases etc.
So here we are, on forums of a site dedicated to database stuff, staffed by folks who are experienced experts in this field, and yet, seem to be unble to resolve an basic issue with a website correctly accessing a datase.. a simple permissions issue even (presuming I'm not missing my guess)... talk about egg on your face.. :hehe:
July 10, 2009 at 7:56 am
Are you asking for help with an attitude like that?
July 10, 2009 at 7:44 pm
Staffed?? There really isn't a whole lot of staff on SSC. Most of us give of our time voluntarily as many of us have our own full time jobs as well.
July 11, 2009 at 8:10 am
SQAPro (7/9/2009)
homebrew01 (7/9/2009)
Michael Valentine Jones (7/8/2009)
Was it really necessary to post this question 6 times?Seems like yesterday's issues with the site are continuing today. Look at all the other multiple posts:w00t:
Mods need to do a little cleanup.
I tried to delete the duplicates, the system lets me go through the motions of doing so, gives me a confirm dialog and everything.. but nothing deletes, not even the ones with no replies in the topic.
go figure.
True, we can't delete the original post, just replies can be deleted. Not sure the reason, but it's intentional.
July 11, 2009 at 9:16 am
SQAPro (7/9/2009)
... staffed by folks who are experienced experts in this field, and yet, seem to be unble to resolve an basic issue with a website correctly accessing a datase.. a simple permissions issue even (presuming I'm not missing my guess)...
The 'staff' of SSC is Steve and a small number of people from RedGate. That's it.
If you read over the thread that one of the Redgate project managers posted, you'll see that they were trying to quickly move the DBs onto separate servers to cope with a sudden spike of activity that threatened to bring the whole thing down. In doing so, some of the security and permissions on linked servers and DTC were missed, because they were not obvious settings. It happens. No big deal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2009 at 9:28 am
Back to the original topic, I second Gail's advice with one exception - if your testing includes performance then a plain backup/restore approach may be better as there is a small overhead in having a snapshot.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply