private async Task VerifyMetadataList(string query, List <ObjectMetadata> expectedMetadataList)
{
var testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, query, "MetadataTests");
try
{
var requestContext = new Mock <RequestContext <MetadataQueryResult> >();
requestContext.Setup(x => x.SendResult(It.IsAny <MetadataQueryResult>())).Returns(Task.FromResult(new object()));
ConnectionService connectionService = LiveConnectionHelper.GetLiveTestConnectionService();
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
//Opening a connection to db to lock the db
TestConnectionResult connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync(testDb.DatabaseName, queryTempFile.FilePath, ConnectionType.Default);
MetadataService service = new MetadataService();
await service.HandleMetadataListRequest(new MetadataQueryParams
{
OwnerUri = queryTempFile.FilePath
}, requestContext.Object);
Thread.Sleep(2000);
await service.MetadataListTask;
requestContext.Verify(x => x.SendResult(It.Is <MetadataQueryResult>(r => VerifyResult(r, expectedMetadataList))));
connectionService.Disconnect(new ServiceLayer.Connection.Contracts.DisconnectParams
{
OwnerUri = queryTempFile.FilePath
});
}
}
catch
{
throw;
}
finally
{
await testDb.CleanupAsync();
}
}
public async void RestoreShouldCloseOtherConnectionsBeforeExecuting()
{
await GetBackupFilesToRecoverDatabaseCreated();
var testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, null, "RestoreTest");
ConnectionService connectionService = LiveConnectionHelper.GetLiveTestConnectionService();
TestConnectionResult connectionResult;
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
//OE connection will be closed after conneced
connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync(testDb.DatabaseName, queryTempFile.FilePath, ConnectionType.ObjectExplorer);
//Opening a connection to db to lock the db
connectionService.ConnectionQueue.AddConnectionContext(connectionResult.ConnectionInfo, "", true);
try
{
Dictionary <string, object> options = new Dictionary <string, object>();
options.Add(RestoreOptionsHelper.ReplaceDatabase, true);
await VerifyRestore(null, databaseNameToRestoreFrom, true, TaskExecutionModeFlag.Execute, testDb.DatabaseName, null, options
, (database) =>
{
return(database.Tables.Contains("tb1", "test"));
});
}
finally
{
connectionService.Disconnect(new ServiceLayer.Connection.Contracts.DisconnectParams
{
OwnerUri = queryTempFile.FilePath,
Type = ConnectionType.Default
});
testDb.Cleanup();
}
}
}
public void ScriptBackupTest()
{
DisasterRecoveryService service = new DisasterRecoveryService();
string databaseName = "SqlToolsService_TestBackup_" + new Random().Next(10000000, 99999999);
using (SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, databaseName))
{
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo(databaseName);
using (DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(liveConnection.ConnectionInfo, databaseExists: true))
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
{
string backupPath = GetDefaultBackupFullPath(service, databaseName, helper.DataContainer, sqlConn);
BackupInfo backupInfo = CreateDefaultBackupInfo(databaseName,
BackupType.Full,
new List <string>()
{
backupPath
},
new Dictionary <string, int>()
{
{ backupPath, (int)DeviceType.File }
});
BackupOperation backupOperation = CreateBackupOperation(service, liveConnection.ConnectionInfo.OwnerUri, backupInfo, helper.DataContainer, sqlConn);
// Generate script for backup
service.ScriptBackup(backupOperation);
string script = backupOperation.ScriptContent;
Assert.True(!string.IsNullOrEmpty(script));
// Execute the script
testDb.RunQuery(script);
VerifyAndCleanBackup(sqlConn, backupPath);
}
}
}
public void ScriptBackupWithDifferentActionTypesTest()
{
string databaseName = "SqlToolsService_TestBackup_" + new Random().Next(10000000, 99999999);
SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, databaseName);
try
{
// Create Full backup script
string script = GenerateScriptForBackupType(BackupType.Full, databaseName);
// Validate Full backup script
Assert.Contains("BACKUP DATABASE", script, StringComparison.OrdinalIgnoreCase);
Assert.DoesNotContain("BACKUP LOG", script, StringComparison.OrdinalIgnoreCase);
Assert.DoesNotContain("DIFFERENTIAL", script, StringComparison.OrdinalIgnoreCase);
// Create log backup script
script = GenerateScriptForBackupType(BackupType.TransactionLog, databaseName);
// Validate Log backup script
Assert.Contains("BACKUP LOG", script, StringComparison.OrdinalIgnoreCase);
Assert.DoesNotContain("BACKUP DATABASE", script, StringComparison.OrdinalIgnoreCase);
Assert.DoesNotContain("DIFFERENTIAL", script, StringComparison.OrdinalIgnoreCase);
// Create differential backup script
script = GenerateScriptForBackupType(BackupType.Differential, databaseName);
// Validate differential backup script
Assert.Contains("BACKUP DATABASE", script, StringComparison.OrdinalIgnoreCase);
Assert.DoesNotContain("BACKUP LOG", script, StringComparison.OrdinalIgnoreCase);
Assert.Contains("WITH DIFFERENTIAL", script, StringComparison.OrdinalIgnoreCase);
}
finally
{
// Clean up the database
Console.WriteLine("Clean up database..");
testDb.Cleanup();
}
}
public async void GenerateDeployScriptWithSqlCmdVariables()
{
var result = GetLiveAutoCompleteTestObjects();
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, query : storedProcScript, dbNamePrefix : "DacFxGenerateScriptSqlCmdVarsTest");
try
{
DacFxService service = new DacFxService();
// First extract a db to have a dacpac to generate the script for later
string dacpacPath = InitialExtract(service, sourceDb, result);
// Generate script for deploying source dacpac to target db with SqlCmdVars
var generateScriptParams = new GenerateDeployScriptParams
{
PackageFilePath = dacpacPath,
DatabaseName = string.Concat(sourceDb.DatabaseName, "-generated"),
SqlCommandVariableValues = new Dictionary <string, string>()
{
{ databaseRefVarName, "OtherDatabase" },
{ filterValueVarName, "Employee" }
}
};
GenerateDeployScriptOperation generateScriptOperation = new GenerateDeployScriptOperation(generateScriptParams, result.ConnectionInfo);
service.PerformOperation(generateScriptOperation, TaskExecutionMode.Script);
// Verify the SqlCmdVars were set correctly in the script
Assert.NotEmpty(generateScriptOperation.Result.DatabaseScript);
Assert.Contains($":setvar {databaseRefVarName} \"{generateScriptParams.SqlCommandVariableValues[databaseRefVarName]}\"", generateScriptOperation.Result.DatabaseScript);
Assert.Contains($":setvar {filterValueVarName} \"{generateScriptParams.SqlCommandVariableValues[filterValueVarName]}\"", generateScriptOperation.Result.DatabaseScript);
VerifyAndCleanup(dacpacPath);
}
finally
{
sourceDb.Cleanup();
}
}
public async void RestorePlanShouldFailOnExistingDatabaseNotGivenReplaceOption()
{
SqlTestDb testDb = null;
try
{
testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, null, "RestoreTest");
//Create a backup from a test db but don't delete the database
await VerifyBackupFileCreated();
bool canRestore = true;
await VerifyRestore(new string[] { fullBackupFilePath }, null, canRestore, TaskExecutionModeFlag.None, testDb.DatabaseName, null, null);
}
finally
{
if (testDb != null)
{
testDb.Cleanup();
}
}
}
private async Task RunTest(string databaseName, string query, string testDbPrefix, Func <string, ObjectExplorerSession, Task> test)
{
SqlTestDb testDb = null;
string uri = string.Empty;
try
{
testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, query, testDbPrefix);
if (databaseName == "#testDb#")
{
databaseName = testDb.DatabaseName;
}
var session = await CreateSession(databaseName);
uri = session.Uri;
await test(testDb.DatabaseName, session);
}
catch (Exception ex)
{
string msg = ex.BuildRecursiveErrorMessage();
Console.WriteLine($"Failed to run OE test. uri:{uri} error:{msg} {ex.StackTrace}");
Assert.False(true, msg);
}
finally
{
if (!string.IsNullOrEmpty(uri))
{
CloseSession(uri);
}
if (testDb != null)
{
await testDb.CleanupAsync();
}
}
}
public void CreateBackupTest()
{
DisasterRecoveryService service = new DisasterRecoveryService();
string databaseName = "SqlToolsService_TestBackup_" + new Random().Next(10000000, 99999999);
SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, databaseName);
try
{
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo(databaseName);
DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(liveConnection.ConnectionInfo, databaseExists: true);
SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo);
string backupPath = GetDefaultBackupFullPath(service, databaseName, helper.DataContainer, sqlConn);
BackupInfo backupInfo = CreateDefaultBackupInfo(databaseName,
BackupType.Full,
new List <string>()
{
backupPath
},
new Dictionary <string, int>()
{
{ backupPath, (int)DeviceType.File }
});
BackupOperation backupOperation = CreateBackupOperation(service, liveConnection.ConnectionInfo.OwnerUri, backupInfo, helper.DataContainer, sqlConn);
// Backup the database
service.PerformBackup(backupOperation);
VerifyAndCleanBackup(backupPath);
sqlConn.Close();
}
finally
{
testDb.Cleanup();
}
}
public async void GenerateDeployScript()
{
// first extract a dacpac
var result = GetLiveAutoCompleteTestObjects();
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, SourceScript, "DacFxGenerateScriptTest");
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, null, "DacFxGenerateScriptTest");
try
{
DacFxService service = new DacFxService();
string dacpacPath = InitialExtract(service, sourceDb, result);
// generate script
var generateScriptParams = new GenerateDeployScriptParams
{
PackageFilePath = dacpacPath,
DatabaseName = targetDb.DatabaseName
};
// Generate script for deploying source dacpac to target db
GenerateDeployScriptOperation generateScriptOperation = new GenerateDeployScriptOperation(generateScriptParams, result.ConnectionInfo);
service.PerformOperation(generateScriptOperation, TaskExecutionMode.Script);
// Verify script was generated
Assert.NotEmpty(generateScriptOperation.Result.DatabaseScript);
Assert.Contains("CREATE TABLE", generateScriptOperation.Result.DatabaseScript);
VerifyAndCleanup(dacpacPath);
}
finally
{
sourceDb.Cleanup();
targetDb.Cleanup();
}
}
//[Fact]
public async void BackupFileBrowserTest()
{
string databaseName = "testfilebrowser_" + new Random().Next(10000000, 99999999);
SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, databaseName);
// Initialize backup service
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo(databaseName);
DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(liveConnection.ConnectionInfo, databaseExists: true);
SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo);
DisasterRecoveryService disasterRecoveryService = new DisasterRecoveryService();
BackupConfigInfo backupConfigInfo = disasterRecoveryService.GetBackupConfigInfo(helper.DataContainer, sqlConn, sqlConn.Database);
// Create backup file
string backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + ".bak");
string query = $"BACKUP DATABASE [{databaseName}] TO DISK = N'{backupPath}' WITH NOFORMAT, NOINIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, "master", query);
FileBrowserService service = new FileBrowserService();
string[] backupFilters = new string[2] {
"*.bak", "*.trn"
};
var openParams = new FileBrowserOpenParams
{
OwnerUri = liveConnection.ConnectionInfo.OwnerUri,
ExpandPath = backupConfigInfo.DefaultBackupFolder,
FileFilters = backupFilters
};
var openBrowserEventFlowValidator = new EventFlowValidator <bool>()
.AddEventValidation(FileBrowserOpenedNotification.Type, eventParams =>
{
Assert.True(eventParams.Succeeded);
Assert.NotNull(eventParams.FileTree);
Assert.NotNull(eventParams.FileTree.RootNode);
Assert.NotNull(eventParams.FileTree.RootNode.Children);
Assert.True(eventParams.FileTree.RootNode.Children.Count > 0);
Assert.True(ContainsFileInTheFolder(eventParams.FileTree.SelectedNode, backupPath));
})
.Complete();
await service.RunFileBrowserOpenTask(openParams, openBrowserEventFlowValidator.Object);
// Verify complete notification event was fired and the result
openBrowserEventFlowValidator.Validate();
var expandParams = new FileBrowserExpandParams
{
OwnerUri = liveConnection.ConnectionInfo.OwnerUri,
ExpandPath = backupConfigInfo.DefaultBackupFolder
};
var expandEventFlowValidator = new EventFlowValidator <bool>()
.AddEventValidation(FileBrowserExpandedNotification.Type, eventParams =>
{
Assert.True(eventParams.Succeeded);
Assert.NotNull(eventParams.Children);
Assert.True(eventParams.Children.Length > 0);
})
.Complete();
// Expand the node in file browser
await service.RunFileBrowserExpandTask(expandParams, expandEventFlowValidator.Object);
// Verify result
expandEventFlowValidator.Validate();
var validateParams = new FileBrowserValidateParams
{
OwnerUri = liveConnection.ConnectionInfo.OwnerUri,
ServiceType = FileValidationServiceConstants.Backup,
SelectedFiles = new[] { backupPath }
};
var validateEventFlowValidator = new EventFlowValidator <bool>()
.AddEventValidation(FileBrowserValidatedNotification.Type, eventParams => Assert.True(eventParams.Succeeded))
.Complete();
// Validate selected files in the browser
await service.RunFileBrowserValidateTask(validateParams, validateEventFlowValidator.Object);
// Verify complete notification event was fired and the result
validateEventFlowValidator.Validate();
// Remove the backup file
if (File.Exists(backupPath))
{
File.Delete(backupPath);
}
}
private async Task <Mock <RequestContext <SchemaCompareResult> > > SendAndValidateSchemaCompareGenerateScriptRequestDacpacToDatabaseWithOptions(string sourceScript, string targetScript, DeploymentOptions nodiffOption, DeploymentOptions shouldDiffOption)
{
var result = SchemaCompareTestUtils.GetLiveAutoCompleteTestObjects();
var schemaCompareRequestContext = new Mock <RequestContext <SchemaCompareResult> >();
schemaCompareRequestContext.Setup(x => x.SendResult(It.IsAny <SchemaCompareResult>())).Returns(Task.FromResult(new object()));
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, sourceScript, "SchemaCompareSource");
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, targetScript, "SchemaCompareTarget");
string folderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "SchemaCompareTest");
Directory.CreateDirectory(folderPath);
try
{
string sourceDacpacFilePath = SchemaCompareTestUtils.CreateDacpac(sourceDb);
SchemaCompareEndpointInfo sourceInfo = new SchemaCompareEndpointInfo();
SchemaCompareEndpointInfo targetInfo = new SchemaCompareEndpointInfo();
sourceInfo.EndpointType = SchemaCompareEndpointType.Dacpac;
sourceInfo.PackageFilePath = sourceDacpacFilePath;
targetInfo.EndpointType = SchemaCompareEndpointType.Database;
targetInfo.DatabaseName = targetDb.DatabaseName;
var schemaCompareParams1 = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo,
DeploymentOptions = nodiffOption,
};
SchemaCompareOperation schemaCompareOperation1 = new SchemaCompareOperation(schemaCompareParams1, result.ConnectionInfo, result.ConnectionInfo);
schemaCompareOperation1.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation1.ComparisonResult.IsValid);
Assert.True(schemaCompareOperation1.ComparisonResult.IsEqual);
Assert.NotNull(schemaCompareOperation1.ComparisonResult.Differences);
// generate script
var generateScriptParams1 = new SchemaCompareGenerateScriptParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation1.OperationId,
};
SchemaCompareGenerateScriptOperation generateScriptOperation1 = new SchemaCompareGenerateScriptOperation(generateScriptParams1, schemaCompareOperation1.ComparisonResult);
generateScriptOperation1.Execute();
// validate script generation failed because there were no differences
Assert.False(generateScriptOperation1.ScriptGenerationResult.Success);
var schemaCompareParams2 = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo,
DeploymentOptions = shouldDiffOption,
};
SchemaCompareOperation schemaCompareOperation2 = new SchemaCompareOperation(schemaCompareParams2, result.ConnectionInfo, result.ConnectionInfo);
schemaCompareOperation2.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation2.ComparisonResult.IsValid);
Assert.False(schemaCompareOperation2.ComparisonResult.IsEqual);
Assert.NotNull(schemaCompareOperation2.ComparisonResult.Differences);
// generate script
var generateScriptParams2 = new SchemaCompareGenerateScriptParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation1.OperationId,
};
SchemaCompareGenerateScriptOperation generateScriptOperation2 = new SchemaCompareGenerateScriptOperation(generateScriptParams2, schemaCompareOperation2.ComparisonResult);
generateScriptOperation2.Execute();
// validate script generation succeeded
Assert.True(generateScriptOperation2.ScriptGenerationResult.Success);
Assert.True(!string.IsNullOrEmpty(generateScriptOperation2.ScriptGenerationResult.Script), "Should have differences");
// cleanup
SchemaCompareTestUtils.VerifyAndCleanup(sourceDacpacFilePath);
}
finally
{
sourceDb.Cleanup();
targetDb.Cleanup();
}
return(schemaCompareRequestContext);
}
public override void Before(MethodInfo methodUnderTest)
{
SqlTestDb.CreateNew(ServerType, doNotCleanupDb: true, databaseName: Common.PerfTestDatabaseName, query: Scripts.CreateDatabaseObjectsQuery);
}
private async Task <Mock <RequestContext <DacFxResult> > > SendAndValidateGenerateDeployPlanRequest()
{
var result = GetLiveAutoCompleteTestObjects();
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, null, "DacFxGenerateDeployPlanTest");
sourceDb.RunQuery(@"CREATE TABLE [dbo].[table1]
(
[ID] INT NOT NULL PRIMARY KEY,
[Date] DATE NOT NULL
)
CREATE TABLE [dbo].[table2]
(
[ID] INT NOT NULL PRIMARY KEY,
[col1] NCHAR(10) NULL
)");
DacFxService service = new DacFxService();
string folderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "DacFxTest");
Directory.CreateDirectory(folderPath);
var extractParams = new ExtractParams
{
DatabaseName = sourceDb.DatabaseName,
PackageFilePath = Path.Combine(folderPath, string.Format("{0}.dacpac", sourceDb.DatabaseName)),
ApplicationName = "test",
ApplicationVersion = new Version(1, 0)
};
ExtractOperation extractOperation = new ExtractOperation(extractParams, result.ConnectionInfo);
service.PerformOperation(extractOperation);
// generate deploy plan for deploying dacpac to targetDb
var generateDeployPlanRequestContext = new Mock <RequestContext <DacFxResult> >();
generateDeployPlanRequestContext.Setup(x => x.SendResult(It.IsAny <DacFxResult>())).Returns(Task.FromResult(new object()));
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, null, "DacFxGenerateDeployPlanTestTarget");
targetDb.RunQuery(@"CREATE TABLE [dbo].[table2]
(
[ID] INT NOT NULL PRIMARY KEY,
[col1] NCHAR(10) NULL,
[col2] NCHAR(10) NULL
)
CREATE TABLE [dbo].[table3]
(
[ID] INT NOT NULL PRIMARY KEY,
[col1] INT NULL,
)");
var generateDeployPlanParams = new GenerateDeployPlanParams
{
PackageFilePath = extractParams.PackageFilePath,
DatabaseName = targetDb.DatabaseName,
};
GenerateDeployPlanOperation generateDeployPlanOperation = new GenerateDeployPlanOperation(generateDeployPlanParams, result.ConnectionInfo);
service.PerformOperation(generateDeployPlanOperation);
string report = generateDeployPlanOperation.DeployReport;
Assert.NotNull(report);
Assert.Contains("Create", report);
Assert.Contains("Drop", report);
Assert.Contains("Alter", report);
// cleanup
VerifyAndCleanup(extractParams.PackageFilePath);
sourceDb.Cleanup();
targetDb.Cleanup();
return(generateDeployPlanRequestContext);
}
private async Task <Mock <RequestContext <SchemaCompareResult> > > SendAndValidateSchemaComparePublishChangesRequestDatabaseToDatabase()
{
var result = SchemaCompareTestUtils.GetLiveAutoCompleteTestObjects();
var schemaCompareRequestContext = new Mock <RequestContext <SchemaCompareResult> >();
schemaCompareRequestContext.Setup(x => x.SendResult(It.IsAny <SchemaCompareResult>())).Returns(Task.FromResult(new object()));
SqlTestDb sourceDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, SourceScript, "SchemaCompareSource");
SqlTestDb targetDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, null, "SchemaCompareTarget");
try
{
SchemaCompareEndpointInfo sourceInfo = new SchemaCompareEndpointInfo();
SchemaCompareEndpointInfo targetInfo = new SchemaCompareEndpointInfo();
sourceInfo.EndpointType = SchemaCompareEndpointType.Database;
sourceInfo.DatabaseName = sourceDb.DatabaseName;
targetInfo.EndpointType = SchemaCompareEndpointType.Database;
targetInfo.DatabaseName = targetDb.DatabaseName;
var schemaCompareParams = new SchemaCompareParams
{
SourceEndpointInfo = sourceInfo,
TargetEndpointInfo = targetInfo
};
SchemaCompareOperation schemaCompareOperation = new SchemaCompareOperation(schemaCompareParams, result.ConnectionInfo, result.ConnectionInfo);
schemaCompareOperation.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation.ComparisonResult.IsValid);
Assert.False(schemaCompareOperation.ComparisonResult.IsEqual);
Assert.NotNull(schemaCompareOperation.ComparisonResult.Differences);
var enumerator = schemaCompareOperation.ComparisonResult.Differences.GetEnumerator();
enumerator.MoveNext();
Assert.True(enumerator.Current.SourceObject.Name.ToString().Equals("[dbo].[table1]"));
enumerator.MoveNext();
Assert.True(enumerator.Current.SourceObject.Name.ToString().Equals("[dbo].[table2]"));
// update target
var publishChangesParams = new SchemaComparePublishChangesParams
{
TargetDatabaseName = targetDb.DatabaseName,
OperationId = schemaCompareOperation.OperationId,
};
SchemaComparePublishChangesOperation publishChangesOperation = new SchemaComparePublishChangesOperation(publishChangesParams, schemaCompareOperation.ComparisonResult);
publishChangesOperation.Execute(TaskExecutionMode.Execute);
Assert.True(publishChangesOperation.PublishResult.Success);
Assert.Empty(publishChangesOperation.PublishResult.Errors);
// Verify that there are no differences after the publish by running the comparison again
schemaCompareOperation.Execute(TaskExecutionMode.Execute);
Assert.True(schemaCompareOperation.ComparisonResult.IsValid);
Assert.True(schemaCompareOperation.ComparisonResult.IsEqual);
Assert.Empty(schemaCompareOperation.ComparisonResult.Differences);
}
finally
{
sourceDb.Cleanup();
targetDb.Cleanup();
}
return(schemaCompareRequestContext);
}
public void ScriptBackupWithAdvancedOptionsTest()
{
DisasterRecoveryService service = new DisasterRecoveryService();
string databaseName = "SqlToolsService_TestBackup_" + new Random().Next(10000000, 99999999);
SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, databaseName);
try
{
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo(databaseName);
DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(liveConnection.ConnectionInfo, databaseExists: true);
SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo);
string backupPath = GetDefaultBackupFullPath(service, databaseName, helper.DataContainer, sqlConn);
string certificateName = CreateCertificate(testDb);
string cleanupCertificateQuery = string.Format(CleanupCertificateQueryFormat, certificateName);
BackupInfo backupInfo = CreateDefaultBackupInfo(databaseName,
BackupType.Full,
new List <string>()
{
backupPath
},
new Dictionary <string, int>()
{
{ backupPath, (int)DeviceType.File }
});
backupInfo.FormatMedia = true;
backupInfo.SkipTapeHeader = true;
backupInfo.Initialize = true;
backupInfo.MediaName = "backup test media";
backupInfo.MediaDescription = "backup test";
backupInfo.EncryptionAlgorithm = (int)BackupEncryptionAlgorithm.Aes128;
backupInfo.EncryptorType = (int)BackupEncryptorType.ServerCertificate;
backupInfo.EncryptorName = certificateName;
BackupOperation backupOperation = CreateBackupOperation(service, liveConnection.ConnectionInfo.OwnerUri, backupInfo, helper.DataContainer, sqlConn);
// Backup the database
Console.WriteLine("Generate script for backup operation..");
service.ScriptBackup(backupOperation);
string script = backupOperation.ScriptContent;
// Run the script
Console.WriteLine("Execute the script..");
testDb.RunQuery(script);
// Remove the backup file
Console.WriteLine("Verify the backup file exists and remove..");
VerifyAndCleanBackup(backupPath);
// Delete certificate and master key
Console.WriteLine("Remove certificate and master key..");
testDb.RunQuery(cleanupCertificateQuery);
sqlConn.Close();
}
finally
{
// Clean up the database
Console.WriteLine("Clean up database..");
testDb.Cleanup();
}
}
public async Task <string[]> CreateBackupSetsToRecoverDatabase()
{
List <string> backupFiles = new List <string>();
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
string query = $"CREATE SCHEMA [test]";
SqlTestDb testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, query, "RestoreTest");
string databaseName = testDb.DatabaseName;
// Initialize backup service
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo(databaseName, queryTempFile.FilePath);
DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(liveConnection.ConnectionInfo, databaseExists: true);
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
{
BackupConfigInfo backupConfigInfo = DisasterRecoveryService.Instance.GetBackupConfigInfo(helper.DataContainer, sqlConn, sqlConn.Database);
query = $"create table [test].[{tableNames[0]}] (c1 int)";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, databaseName, query);
string backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + "_full.bak");
query = $"BACKUP DATABASE [{databaseName}] TO DISK = N'{backupPath}' WITH NOFORMAT, NOINIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, "master", query);
backupFiles.Add(backupPath);
query = $"create table [test].[{tableNames[1]}] (c1 int)";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, databaseName, query);
backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + "_diff.bak");
query = $"BACKUP DATABASE [{databaseName}] TO DISK = N'{backupPath}' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, "master", query);
backupFiles.Add(backupPath);
query = $"create table [test].[{tableNames[2]}] (c1 int)";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, databaseName, query);
backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + "_log1.bak");
query = $"BACKUP Log [{databaseName}] TO DISK = N'{backupPath}' WITH NOFORMAT, NOINIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, "master", query);
backupFiles.Add(backupPath);
query = $"create table [test].[{tableNames[3]}] (c1 int)";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, databaseName, query);
backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + "_log2.bak");
query = $"BACKUP Log [{databaseName}] TO DISK = N'{backupPath}' WITH NOFORMAT, NOINIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, "master", query);
backupFiles.Add(backupPath);
query = $"create table [test].[{tableNames[4]}] (c1 int)";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, databaseName, query);
backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + "_log3.bak");
query = $"BACKUP Log [{databaseName}] TO DISK = N'{backupPath}' WITH NOFORMAT, NOINIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
await TestServiceProvider.Instance.RunQueryAsync(TestServerType.OnPrem, "master", query);
backupFiles.Add(backupPath);
databaseNameToRestoreFrom = testDb.DatabaseName;
// Clean up the database
testDb.Cleanup();
}
}
return(backupFiles.ToArray());
}
private async Task VerifyScriptAsForMultipleObjects(string query, List <ScriptingObject> scriptingObjects, ScriptingOperationType operation, List <string> expectedScripts)
{
var testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, query, "ScriptingTests");
try
{
var requestContext = new Mock <RequestContext <ScriptingResult> >();
requestContext.Setup(x => x.SendResult(It.IsAny <ScriptingResult>())).Returns(Task.FromResult(new object()));
ConnectionService connectionService = LiveConnectionHelper.GetLiveTestConnectionService();
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
//Opening a connection to db to lock the db
TestConnectionResult connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync(testDb.DatabaseName, queryTempFile.FilePath, ConnectionType.Default);
var scriptingParams = new ScriptingParams
{
OwnerUri = queryTempFile.FilePath,
ScriptDestination = "ToEditor",
Operation = operation
};
string scriptCreateOperation = "ScriptCreate";
if (operation == ScriptingOperationType.Delete)
{
scriptCreateOperation = "ScriptDrop";
}
else
{
scriptCreateOperation = $"Script{operation}";
}
scriptingParams.ScriptOptions = new ScriptOptions
{
ScriptCreateDrop = scriptCreateOperation,
};
scriptingParams.ScriptingObjects = scriptingObjects;
ScriptingService service = new ScriptingService();
await service.HandleScriptExecuteRequest(scriptingParams, requestContext.Object);
Thread.Sleep(2000);
await service.ScriptingTask;
requestContext.Verify(x => x.SendResult(It.Is <ScriptingResult>(r => VerifyScriptingResult(r, expectedScripts))));
connectionService.Disconnect(new ServiceLayer.Connection.Contracts.DisconnectParams
{
OwnerUri = queryTempFile.FilePath
});
}
}
catch
{
throw;
}
finally
{
await testDb.CleanupAsync();
}
}
public ScriptingFixture()
{
this.Database = SqlTestDb.CreateNew(TestServerType.OnPrem);
this.Database.RunQuery(Scripts.CreateNorthwindSchema, throwOnError: true);
Console.WriteLine("Northwind setup complete, database name: {0}", this.Database.DatabaseName);
}
public void CreateBackupWithAdvancedOptionsTest()
{
string databaseName = "testbackup_" + new Random().Next(10000000, 99999999);
SqlTestDb testDb = SqlTestDb.CreateNew(TestServerType.OnPrem, false, databaseName);
string certificateName = "backupcertificate" + new Random().Next(10000000, 99999999);
string masterkeyPassword = Guid.NewGuid().ToString();
string createCertificateQuery = string.Format(CreateCertificateQueryFormat, masterkeyPassword, certificateName);
string cleanupCertificateQuery = string.Format(CleanupCertificateQueryFormat, certificateName);
// create master key and certificate
Console.WriteLine("Create master key and certificate..");
testDb.RunQuery(createCertificateQuery);
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo(databaseName);
// Initialize backup service
DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(liveConnection.ConnectionInfo, databaseExists: true);
SqlConnection sqlConn = DisasterRecoveryService.GetSqlConnection(liveConnection.ConnectionInfo);
// Get default backup path
Console.WriteLine("Get default backup path..");
BackupConfigInfo backupConfigInfo = DisasterRecoveryService.Instance.GetBackupConfigInfo(helper.DataContainer, sqlConn, sqlConn.Database);
string backupPath = Path.Combine(backupConfigInfo.DefaultBackupFolder, databaseName + ".bak");
BackupInfo backupInfo = CreateBackupInfo(databaseName,
BackupType.Full,
new List <string>()
{
backupPath
},
new Dictionary <string, int>()
{
{ backupPath, (int)DeviceType.File }
});
// Set advanced options
backupInfo.ContinueAfterError = true;
backupInfo.FormatMedia = true;
backupInfo.SkipTapeHeader = true;
backupInfo.Initialize = true;
backupInfo.MediaName = "backup test media";
backupInfo.MediaDescription = "backup test";
backupInfo.RetainDays = 90;
backupInfo.CompressionOption = (int)BackupCompressionOptions.On;
// Set encryption
backupInfo.EncryptionAlgorithm = (int)BackupEncryptionAlgorithm.Aes128;
backupInfo.EncryptorType = (int)BackupEncryptorType.ServerCertificate;
backupInfo.EncryptorName = certificateName;
var backupParams = new BackupParams
{
OwnerUri = liveConnection.ConnectionInfo.OwnerUri,
BackupInfo = backupInfo
};
// Backup the database
Console.WriteLine("Perform backup operation..");
BackupOperation backupOperation = DisasterRecoveryService.Instance.SetBackupInput(helper.DataContainer, sqlConn, backupParams.BackupInfo);
DisasterRecoveryService.Instance.PerformBackup(backupOperation);
// Verify backup file is created
Assert.True(File.Exists(backupPath));
// Remove the backup file
Console.WriteLine("Remove backup file..");
if (File.Exists(backupPath))
{
File.Delete(backupPath);
}
// Delete certificate and master key
Console.WriteLine("Remove certificate and master key..");
testDb.RunQuery(cleanupCertificateQuery);
// Clean up the database
Console.WriteLine("Clean up database..");
testDb.Cleanup();
}
