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.  ...
  • 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.

     

Colin_McCrae's avatar
Colin_McCrae
Community Hero

I'm using VBScript to do my restores (see link above) so I'll leave you guys to it.

 

However, I will chip in with ....

 

Before you start doing restores, make sure you kill ALL application processes and services (they are not all obvious) which may have hooks into the DB you're trying to restore. If anything still has a live connection (even if it's not actually doing anything) it will fail.

maximojo's avatar
maximojo
Frequent Contributor
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.

 

  • daniel_dewinter's avatar
    daniel_dewinter
    Contributor
    9 years ago

    Hi maximojo,

     

    Thanks for the detailed description.

    I got it working!

     

    Somehow I had to split the 'Set to single user' query and the 'Restore' query into two KeywordTests scripts.

    If I put them into one Test, than it doesn't work. I get some exception. But I don't mind, it works fine now.

    Thanks for the effort,

     

    Daniel

    • maximojo's avatar
      maximojo
      Frequent Contributor
      9 years ago

      Apologies daniel_dewinter. I believe in SSMS there would be a "GO" between the two which batches them as SQL server likes. As long as you send them as two separate queries (which is what I do) you should be gold!

    • shirishnagarbp's avatar
      shirishnagarbp
      New Contributor
      7 years ago

      Hi daniel_dewinter, maximojo

       

      Can you please share the full code to restore DB and killing process?

       

      Regards,

      -Shirish