Forum Discussion

smccafferty's avatar
smccafferty
Occasional Contributor
12 years ago

Firing VBscript to connect to database and restore from a snapshot HELP!

Hi guys.

Very new to all this but this is what I've got so far...



1. I can connect to a DB using a little VBScript. Cool.



I want to run the following SQL to revert the database to a snapshot. It's essential as I need stable data every time.

Here's the sql...



ALTER DATABASE [mydatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE master;

RESTORE DATABASE [mydatabase] from

DATABASE_SNAPSHOT = 'mydatabase_base';

GO

ALTER DATABASE [mydatabase]  SET MULTI_USER

GO



The project is using VbScript in testcomplete and this is the very last peice of the puzzle.

When I log into the database with SQL 2008 (r2) I can fire up a new query and it runs great. From testcomplete I keep getting errors.



Can anyone help?



Cheers!
  • vajindarladdad's avatar
    vajindarladdad
    12 years ago
    Hi Sean,

    One thing you can do.



    Create a .sql file for the query. (Test.sql)



    You can execute this query silently using sqlcmd utility. (Please refer help file to run the .sql file silently in SQL 2008 R2)



    Now , you can create a batch which will execute this query & you can invoke the .bat file using vbscript function using "wscript.shell" object.



    This could be the longer way of doing it , but you can skip the creation of batch file part and use vbscript funtion which will create the sqlcmd command and execute it.



    I hope this has been of some help.
  • vajindarladdad's avatar
    vajindarladdad
    Frequent Contributor
    12 years ago
    Hi Sean,

    One thing you can do.



    Create a .sql file for the query. (Test.sql)



    You can execute this query silently using sqlcmd utility. (Please refer help file to run the .sql file silently in SQL 2008 R2)



    Now , you can create a batch which will execute this query & you can invoke the .bat file using vbscript function using "wscript.shell" object.



    This could be the longer way of doing it , but you can skip the creation of batch file part and use vbscript funtion which will create the sqlcmd command and execute it.



    I hope this has been of some help.