Forum Discussion

kirk_bottomley's avatar
kirk_bottomley
Contributor
10 years ago

SQL Connection String TC11 vs TC10

The last bit of code I wrote with TestComplete 10 was a routine to restore a SQL database from backup. We are using SQL Server 2012, and the connection string with ADO.CreateADOConnection() was:

 

ConnectionString = "Provider=SQLNCLI11;Server=" + Server + ";Database=" + Database + ";Uid=" + UserName + ";Pwd=" + Password + ";"

It worked fine yesterday before the upgrade, then didn't today afterward. I modified the Provider to "SQLOLEDB", however, and it works just fine again:

 

ConnectionString = "Provider=SQLOLEDB;Server=" + Server + ";Database=" + Database + ";Uid=" + UserName + ";Pwd=" + Password + ";"

Just wondering if there's a change in the way TC11 does it, and wanted to post this solution if anyone else runs into the same issue.

  • "if you care to share the script to restore a sql db please do"

     

    Alright, but some caveats. Our approach is to run tests on a known dataset. That dataset is stored as a backup, then loaded when needed. What I did was two routines; one to backup the user's current database so they could go back to whatever they're doing when TestExecute finishes running the tests, and one to restore the dataset or their backup. 

    The dataset and their backup have to be moved to the local host of the SQL server. I haven't figured out how to get SQL to restore a backup across a network. So, I back up their data to a backup server, move the dataset to the SQL machine, and restore it. At the end, I move their backup to the SQL machine and restore it.

    We actually have two SQL databases, so the routines are abstracted to accept a database name and run with whatever is passed to it. The backups are a concatenation of the database name, their host machine name (without hyphens) and ".bak".

     

    Further, the dataset cannot be restored to a sql server of an earlier version than the one that created the dataset. Also, I've had to cheat a little by doing a manual backup and restore on each user's SQL server once before the code can run remotely without error. I haven't been able to figure that one out yet. Your mileage may vary...

     

    The password is plaintext, unfortunately. I tried storing it as a Project Variable using the password type, but the connection string will not work that way. 

     

    This one backs up their database and moves the testing dataset onto the SQL local host:

    function SQLDatabaseBackup(Database, Server)
    {
    var BackupDrive = "\\\\BackupServer\\BackupsGoHere\\";
    var Backup = BackupDrive + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak";
    var UserName = "DatabaseOwner";
    var Password = "DBOwnerPassword";
    
    // Path to test data location where the testing database is stored.
    var Dataset = Project.Variables.TestData + Database + ".bak";
    
    // Path to the system that is local to the SQL Server
    var SQLServerPath = Project.Variables.SQLServerPath
    
    aCmd = ADO.CreateADOCommand();
    aCmd.ConnectionString = "Provider=SQLOLEDB;Server=" + Server + ";Database=" + Database + ";Uid=" + UserName + ";Pwd=" + Password + ";";
    aCmd.CommandType = adCmdText;
    
      if (aqFile.Exists(Backup))
      {
        Log.Warning("Backup file already exists. Exiting.");
        return;
      }
      
      try
      {
        aCmd.CommandText = "BACKUP DATABASE " + Database + " TO DISK = '" + Backup + "'";
        aCmd.Execute();
        Log.Message("Backed up  " + Database + " to " + Backup + ".");
        
        if (!aqFile.Copy(Dataset, SQLServerPath, 1))
        {
          Log.Warning("Could not copy " + Dataset + " to " + SQLServerPath);
        }
        
        Log.Message("Copied " + Dataset + " to " + SQLServerPath);
      }
      
      catch(err)
      {
        Log.Message(err.message);
      }
    }

    This one restores their data or the testing set. The switch statement is a little kludgy, but it allows for future options.

    function SQLDatabaseRestore(Database, Server, BackupOrDataset)
    {
    var BackupDrive = "\\\\BackupServer\\BackupsGoHere\\";
    var Backup = BackupDrive + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak";
    var UserName = "DatabaseOwner";
    var Password = "DBOwnerPassword";
    
    // Path to test data location where the testing database is stored.
    var Dataset = Project.Variables.TestData + Database + ".bak";
    
    // Path to the system that is local to the SQL Server
    var SQLServerPath = Project.Variables.SQLServerPath
    
    aCmd = ADO.CreateADOCommand();
    aCmd.ConnectionString = "Provider=SQLOLEDB;Server=" + Server + ";Database=" + Database + ";Uid=" + UserName + ";Pwd=" + Password + ";";
    aCmd.CommandType = adCmdText;
    
      switch(BackupOrDataset)
      {
        case "Backup":
          try
          {
            if (!aqFile.Exists(Backup))
            {
              Log.Warning("Backup file does not exist. Exiting.");
              return;
            }
            
            aqFile.Copy(Backup, SQLServerPath, 1)
              
            aCmd.CommandText = "ALTER DATABASE " + Database + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
            aCmd.Execute();
            Log.Message("Set " + Database + " to Single User.");
            aCmd.CommandText = "USE master";
            aCmd.Execute();
            Log.Message("Using master db.");
            aCmd.CommandText = "RESTORE DATABASE " + Database + " FROM DISK = 'C:\\" + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak" + "' WITH REPLACE";
            aCmd.Execute();
            Log.Message("Restored " + Database + " from " + Backup + ".");
            aCmd.CommandText = "ALTER DATABASE " + Database + " SET MULTI_USER WITH ROLLBACK IMMEDIATE";
            aCmd.Execute();
            Log.Message("Set " + Database + " to Multi-User.");
            
            aqFile.Delete(Backup);
            Log.Message("Deleted " + Backup);
            
            aqFile.Delete(SQLServerPath + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak")
            Log.Message("Deleted " + Project.Variables.SQLServerPath + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak")
            
            aqFile.Delete(ServerDrive + Database + ".bak");
            Log.Message("Deleted " + ServerDrive + Database + ".bak");
          }
          catch(err)
          {
            Log.Message(err.message);
          }
          
          break;
        
        case "Dataset":
          try
          {
            if (!aqFile.Exists(Backup))
            {
              Log.Warning("Backup file does not exist. Exiting.");
              return;
            }
    
            aCmd.CommandText = "ALTER DATABASE " + Database + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
            aCmd.Execute();
            Log.Message("Set " + Database + " to Single User.");
            aCmd.CommandText = "USE master";
            aCmd.Execute();
            Log.Message("Using master db.");
            aCmd.CommandText = "RESTORE DATABASE " + Database + " FROM DISK = 'C:\\" + Database + ".bak' WITH REPLACE";
            aCmd.Execute();
            Log.Message("Restored " + Dataset + ".");
            aCmd.CommandText = "ALTER DATABASE " + Database + " SET MULTI_USER WITH ROLLBACK IMMEDIATE";
            aCmd.Execute();
            Log.Message("Set " + Database + " to Multi-User.");
          }
          catch(err)
          {
            Log.Message(err.message);
          }
          
          break; 
          
        default:
          Log.Warning("Neither Backup nor Dataset entered.");
      }
    }

    I've tried to include some safeties, like if the backups don't exist, exit the routine. 

     

    Note also that the user running the routine has to have permissions to write to the SQL Server's host machine. If they run on the same machine, obviously that isn't a problem.

  • maximojo's avatar
    maximojo
    Frequent Contributor

    Thank you! And if you care to share the script to restore a sql db please do :)

    • kirk_bottomley's avatar
      kirk_bottomley
      Contributor

      "if you care to share the script to restore a sql db please do"

       

      Alright, but some caveats. Our approach is to run tests on a known dataset. That dataset is stored as a backup, then loaded when needed. What I did was two routines; one to backup the user's current database so they could go back to whatever they're doing when TestExecute finishes running the tests, and one to restore the dataset or their backup. 

      The dataset and their backup have to be moved to the local host of the SQL server. I haven't figured out how to get SQL to restore a backup across a network. So, I back up their data to a backup server, move the dataset to the SQL machine, and restore it. At the end, I move their backup to the SQL machine and restore it.

      We actually have two SQL databases, so the routines are abstracted to accept a database name and run with whatever is passed to it. The backups are a concatenation of the database name, their host machine name (without hyphens) and ".bak".

       

      Further, the dataset cannot be restored to a sql server of an earlier version than the one that created the dataset. Also, I've had to cheat a little by doing a manual backup and restore on each user's SQL server once before the code can run remotely without error. I haven't been able to figure that one out yet. Your mileage may vary...

       

      The password is plaintext, unfortunately. I tried storing it as a Project Variable using the password type, but the connection string will not work that way. 

       

      This one backs up their database and moves the testing dataset onto the SQL local host:

      function SQLDatabaseBackup(Database, Server)
      {
      var BackupDrive = "\\\\BackupServer\\BackupsGoHere\\";
      var Backup = BackupDrive + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak";
      var UserName = "DatabaseOwner";
      var Password = "DBOwnerPassword";
      
      // Path to test data location where the testing database is stored.
      var Dataset = Project.Variables.TestData + Database + ".bak";
      
      // Path to the system that is local to the SQL Server
      var SQLServerPath = Project.Variables.SQLServerPath
      
      aCmd = ADO.CreateADOCommand();
      aCmd.ConnectionString = "Provider=SQLOLEDB;Server=" + Server + ";Database=" + Database + ";Uid=" + UserName + ";Pwd=" + Password + ";";
      aCmd.CommandType = adCmdText;
      
        if (aqFile.Exists(Backup))
        {
          Log.Warning("Backup file already exists. Exiting.");
          return;
        }
        
        try
        {
          aCmd.CommandText = "BACKUP DATABASE " + Database + " TO DISK = '" + Backup + "'";
          aCmd.Execute();
          Log.Message("Backed up  " + Database + " to " + Backup + ".");
          
          if (!aqFile.Copy(Dataset, SQLServerPath, 1))
          {
            Log.Warning("Could not copy " + Dataset + " to " + SQLServerPath);
          }
          
          Log.Message("Copied " + Dataset + " to " + SQLServerPath);
        }
        
        catch(err)
        {
          Log.Message(err.message);
        }
      }

      This one restores their data or the testing set. The switch statement is a little kludgy, but it allows for future options.

      function SQLDatabaseRestore(Database, Server, BackupOrDataset)
      {
      var BackupDrive = "\\\\BackupServer\\BackupsGoHere\\";
      var Backup = BackupDrive + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak";
      var UserName = "DatabaseOwner";
      var Password = "DBOwnerPassword";
      
      // Path to test data location where the testing database is stored.
      var Dataset = Project.Variables.TestData + Database + ".bak";
      
      // Path to the system that is local to the SQL Server
      var SQLServerPath = Project.Variables.SQLServerPath
      
      aCmd = ADO.CreateADOCommand();
      aCmd.ConnectionString = "Provider=SQLOLEDB;Server=" + Server + ";Database=" + Database + ";Uid=" + UserName + ";Pwd=" + Password + ";";
      aCmd.CommandType = adCmdText;
      
        switch(BackupOrDataset)
        {
          case "Backup":
            try
            {
              if (!aqFile.Exists(Backup))
              {
                Log.Warning("Backup file does not exist. Exiting.");
                return;
              }
              
              aqFile.Copy(Backup, SQLServerPath, 1)
                
              aCmd.CommandText = "ALTER DATABASE " + Database + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
              aCmd.Execute();
              Log.Message("Set " + Database + " to Single User.");
              aCmd.CommandText = "USE master";
              aCmd.Execute();
              Log.Message("Using master db.");
              aCmd.CommandText = "RESTORE DATABASE " + Database + " FROM DISK = 'C:\\" + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak" + "' WITH REPLACE";
              aCmd.Execute();
              Log.Message("Restored " + Database + " from " + Backup + ".");
              aCmd.CommandText = "ALTER DATABASE " + Database + " SET MULTI_USER WITH ROLLBACK IMMEDIATE";
              aCmd.Execute();
              Log.Message("Set " + Database + " to Multi-User.");
              
              aqFile.Delete(Backup);
              Log.Message("Deleted " + Backup);
              
              aqFile.Delete(SQLServerPath + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak")
              Log.Message("Deleted " + Project.Variables.SQLServerPath + aqString.Replace(Sys.HostName, "-", "")+ Database + ".bak")
              
              aqFile.Delete(ServerDrive + Database + ".bak");
              Log.Message("Deleted " + ServerDrive + Database + ".bak");
            }
            catch(err)
            {
              Log.Message(err.message);
            }
            
            break;
          
          case "Dataset":
            try
            {
              if (!aqFile.Exists(Backup))
              {
                Log.Warning("Backup file does not exist. Exiting.");
                return;
              }
      
              aCmd.CommandText = "ALTER DATABASE " + Database + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
              aCmd.Execute();
              Log.Message("Set " + Database + " to Single User.");
              aCmd.CommandText = "USE master";
              aCmd.Execute();
              Log.Message("Using master db.");
              aCmd.CommandText = "RESTORE DATABASE " + Database + " FROM DISK = 'C:\\" + Database + ".bak' WITH REPLACE";
              aCmd.Execute();
              Log.Message("Restored " + Dataset + ".");
              aCmd.CommandText = "ALTER DATABASE " + Database + " SET MULTI_USER WITH ROLLBACK IMMEDIATE";
              aCmd.Execute();
              Log.Message("Set " + Database + " to Multi-User.");
            }
            catch(err)
            {
              Log.Message(err.message);
            }
            
            break; 
            
          default:
            Log.Warning("Neither Backup nor Dataset entered.");
        }
      }

      I've tried to include some safeties, like if the backups don't exist, exit the routine. 

       

      Note also that the user running the routine has to have permissions to write to the SQL Server's host machine. If they run on the same machine, obviously that isn't a problem.

      • maximojo's avatar
        maximojo
        Frequent Contributor

        Awesome Kirk! Thanks for that. Just the code to use as a starting point is great!