Forum Discussion

maximojo's avatar
maximojo
Frequent Contributor
10 years ago

Restoring SQL db quickly through TestComplete

Hi all,

 

I would like to perform multiple functional tests using our software which, in the back end, interacts with a MS SQL database in a data destructive manner i.e. changes table data, etc.

 

Then I would like to quickly restore the db to it's pre-functional test state and perform more tests (so each set of tests always start with the db in the same state).

 

Currently it seems I would have to manually restore the db between each set of tests.

Is there a way with TestComplete to restore a SQL db at runtime to a previous state without going through the SQL Management studio UI?

 

Just thought this might be a problem many people have had and is solved.

 

Thanks much

  • maximojo's avatar
    maximojo
    9 years ago

    Colin_McCrae yes this is a problem. Our devs (and I also found this googling) have this method of making sure no other processes are running:

     

     

    USE master;
ALTER DATABASE <DB_NAME_GOES_HERE>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <DB_NAME_GOES_HERE>
SET MULTI_USER;

     

    daniel_dewinter

     

    Sorry for the delay. Basically all you're doing is passing up some sql that is generated from SSMS. Then I have parameterized this so I can do this for any dbs I want to restore.

     

    So I'm sure you know that if you right click on a db in SSMS you can restore a database. After you fill in the db name you can go to "New Query Editor Window". This will generate the sql script that would restore the db if you did this from within SSMS. 

     

    USE [master]
    RESTORE DATABASE [My_TestDB] FROM DISK = N'C:\db_backups\My_TestDB.bak' WITH FILE = 1, MOVE N'My_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLDEV2014\MSSQL\DATA\My_TestDB.MDF', MOVE N'MyData_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLDEV2014\MSSQL\DATA\MyData_Log.LDF', NOUNLOAD, STATS = 5

     

     

    However that query can also be passed as a query to sql from TC/TE to restore dbs! 

     

    So parameterize that and you're gold! 

     

     

    var dbTargetName = "MyNewDB";
    var dbSourceFile = "C:\\db_backups\\automated_testing_source\\" + "MyDB.bak";
    var queryStringSource = "USE [master]RESTORE DATABASE [%s] FROM  DISK = N'%s' WITH  FILE = 1,  MOVE N'Data' TO N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.TESTSERVER\\MSSQL\\DATA\\%s.MDF',  MOVE N'Log' TO N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.TESTSERVER\\MSSQL\\DATA\\%s.LDF',  NOUNLOAD,  STATS = 5";
var queryStringFinal = aqString.Format(queryStringSource, dbTargetName, dbSourceFile, dbTargetName, dbTargetName);

    Your paths to where your SQL server is installed may vary so you will have to update those but can be parameterized as well.

     

    To see the bolded bits above come from the Files area of the Restore Database dialog. The values under the "Logical File Name". Mine, for the most part, have been exactly the same. There is a way to query those values which I do in a C# tool version of the restore functionality. I'm not sure how to do this in jscript but can research that if you like.

     

     

    but let me know of any questions. I'm sure there are areas that I could have presented more clearly.

     

  • maximojo's avatar
    maximojo
    Frequent Contributor
    9 years ago

    FYI I have found a solution to this using Jscript. It's really just sending SQL script up to the SQL server to restore a db using the script generated from SQL Server Management Studio itself. Not really that hard. 

     

    If anyone wants the nitty gritty please let me know and I'll whip up an example for SQL server.

    • daniel_dewinter's avatar
      daniel_dewinter
      Contributor
      9 years ago

      maximojo, I'm very interested in the script you're using. I'm restoring databases alot and it would be very nice to automate this in TestComplete. Could you please send me an example of the script you are using?

       

      Daniel

      • maximojo's avatar
        maximojo
        Frequent Contributor
        9 years ago

        daniel_dewinter apologies for the delay! Very busy with work atm! 

         

        Questions to start with:

         

        - Are you using SQL or MySQL? I'm using SQL but it shouldn't be too hard to modify for MySQL

        - Can you current run queries against your db from TC? If not you should set that up first. And that can be a bit of a bugbear.

        - My solution is for jscript in TC. Is that what you are using?

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    10 years ago

    You don't say what language you're scripting in?

     

    I use VBScript so I did a quick google for "slq restore vbscript". This was the first link:

     

    http://archanareddycm.blogspot.co.uk/

     

    If you're not using vbscript, just repeat the search with whatever you do use. I'm sure it will turn something up ....

    • maximojo's avatar
      maximojo
      Frequent Contributor
      10 years ago

      Thanks for that!

      I'm using jscript but perhaps I can modify that to suit.

       

      If anyone has a jscript solution please let me know and thanks in advance!