July 8, 2009 at 1:53 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 9, 2009 at 2:14 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic749512-357-1.aspx
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 9, 2009 at 2:14 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic749512-357-1.aspx
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply