SQL Connection String TC11 vs TC10

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