Category: SQL Server Restore

Use DacFx to deploy to a location outside of the normal SQL Server directory

Customer is using code like the following to transfer databases from Azure to On-Prem.

namespace ImportDatabase
{
class Program
{
static void Main(string[] args)
{
String targetConnectionString;
targetConnectionString = "Server=.\\SQL2014;Trusted_Connection=True;";
String targetDatabaseName;
targetDatabaseName = "FrontLine_$ID=123456_$Type=Primary";
String pathBacpac;
pathBacpac = "C:\\Users\\JohnDoe\\Documents\\SQL Server Management Studio\\DAC Packages\\FrontLine_$ID=123456_$Type=Primary.bacpac";
ImportBacPacToDB(targetConnectionString, targetDatabaseName, pathBacpac);
}
static void ImportBacPacToDB(String targetConnectionString, String targetDatabaseName, String pathBacpac)
{
Microsoft.SqlServer.Dac.DacServices svcDAC = new Microsoft.SqlServer.Dac.DacServices(targetConnectionString);
using (Microsoft.SqlServer.Dac.BacPackage oBacPackage = Microsoft.SqlServer.Dac.BacPackage.Load(pathBacpac))
{
svcDAC.ImportBacpac(oBacPackage, targetDatabaseName);
}
}
}
}

However, when the database name has a “$” sign it is replaced with a “_” in the $(DefaultFilePrefix of the CREATE DATABASE statement. This is expected behavior.

overwhelmed

Here is how this API launches the CREATE DATABASE statement:

CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS;

These $(DatabaseName), $(DefaultDataPath), $(DefaultFilePrefix) is how we pass variables in scripting mode in TSQL. See examples in https://msdn.microsoft.com/en-us/library/ms188714.aspx
So, if we have “$” symbol in the actual DefaultFilePrefix we transpose it to a “_” in order to prevent mixing up with “$” used for the variable name.
The TSQL this API finally submits has the “$” transposed to “_”.

CREATE DATABASE [FrontLine_$ID=123456_$Type=Primary]
ON
PRIMARY(NAME = [FrontLine_$ID=123456_$Type=Primary], FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\FrontLine__ID=123456__Type=Primary_Primary.mdf')
LOG ON (NAME = [FrontLine_$ID=123456_$Type=Primary_log], FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\FrontLine__ID=123456__Type=Primary_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS

If it is critical that the file name exactly match the database name, how does one work around this?

thinking

By using the following:
//------------------------------------------------------------------------------
//
//
// The MIT License (MIT)
//
// Copyright (c) 2015 Microsoft
//
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all
// copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
// SOFTWARE.
//
//------------------------------------------------------------------------------
using Microsoft.SqlServer.Dac;
using Microsoft.SqlServer.Dac.Extensions;
using Microsoft.SqlServer.Dac.Deployment;
using Microsoft.SqlServer.Dac.Data;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Public.Dac.Samples.Contributors
{
///
/// Supports overriding the location databases are created at. This contributor does
/// so by modifying the CreateDatabase step that actually creates the DB. This
/// provides more flexibility and power than alternative solutions, but it is more
/// brittle – if the internal implementation for creating a database changes this
/// contributor could potentially interfere with that or stop working.
///

///
/// This contributor solves an actual real-world problem that users have asked about.
/// Normally it’s quite hard for users who want to use DacFx to deploy to a location
/// outside of the normal SQL Server directory. This is sometimes the case when
/// defining local databases that should be deployed alongside an application.
///
/// This solution can be compared to the contributor, which changes the default
/// values used when creating a database. That is a simpler solution, possibly a
/// little slower in terms of performance and with less flexibility on naming.
/// However it’s simpler and better protected against implementation changes
///
[ExportDeploymentPlanModifier(ContributorId, “1.0.0.0”)]
public class DbCreateDatabaseModifier : DeploymentPlanModifier
{
public const string ContributorId = “Public.Dac.Samples.Contributors.DbCreateDatabaseModifier”;
///
/// Filepath to save the database file to
///

public const string MdfFilePathArg = “DbCreateDatabaseModifier.MdfFilePath”;

///
/// Filepath to save the log file to
///

public const string LdfFilePathArg = “DbCreateDatabaseModifier.LdfFilePath”;

///
/// Optional contributor argument defining a string the connection string must contain
/// in order to modify the DB location. This is useful if you wish to only change the
/// location for (localdb) deployments, for instance.
///

public const string TargetConnectionStringPatternArg = “DbCreateDatabaseModifier.TargetConnectionStringPattern”;

protected override void OnExecute(DeploymentPlanContributorContext context)
{
// Run only if a location is defined and we’re targeting a serverless (LocalDB)
// instance
string databasePathHex, logPathHex;
if (context.Arguments.TryGetValue(MdfFilePathArg, out databasePathHex)
&& context.Arguments.TryGetValue(LdfFilePathArg, out logPathHex)
//&& context.Options.TargetConnectionString.Contains(“(localdb)”)
)
{
string databasePath = ConvertFromHexString(databasePathHex);
string logPath = ConvertFromHexString(logPathHex);
if (TargetConnectionMatchesPattern(context))
{
ChangeNewDatabaseLocation(context, databasePath, logPath);
}
}
}

private static string ConvertFromHexString(string hexString)
{
List bytes = new List();
for (int i = 0; i < hexString.Length; i += 2)
{
int endIndex = Math.Min(hexString.Length, i + 2);
bytes.Add(Convert.ToByte(hexString.Substring(i, endIndex – i), 16));
}
return Encoding.Unicode.GetString(bytes.ToArray());
}

private bool TargetConnectionMatchesPattern(DeploymentPlanContributorContext context)
{
string targetConnectionStringPattern;
if (context.Arguments.TryGetValue(TargetConnectionStringPatternArg, out targetConnectionStringPattern))
{
string targetConnectionString = context.Options.TargetConnectionString;
return !string.IsNullOrEmpty(targetConnectionString)
&& targetConnectionString.Contains(targetConnectionStringPattern);
}
return true;
}

private void ChangeNewDatabaseLocation(DeploymentPlanContributorContext context, string databasePath,
string logPath)
{
DeploymentStep nextStep = context.PlanHandle.Head;

// Loop through all steps in the deployment plan
bool finished = false;
while (nextStep != null && !finished)
{
// Increment the step pointer, saving both the current and next steps
DeploymentStep currentStep = nextStep;

// Only interrogate up to BeginPreDeploymentScriptStep – setvars must be done
// before that
if (currentStep is BeginPreDeploymentScriptStep)
{
finished = true;
break;
}

SqlCreateDatabaseStep createDbStep = currentStep as SqlCreateDatabaseStep;
if (createDbStep != null)
{
TSqlFragment fragment = createDbStep.Script;

CreateDatabaseStatementVisitor visitor = new CreateDatabaseStatementVisitor(databasePath, logPath);
fragment.Accept(visitor);

finished = true;
}

nextStep = currentStep.Next;
}
}

///
/// A that updates the database and
/// log file names for a newly created database. The ScriptDom API uses a
/// visitor pattern to support interrogating the contents of a TSqlScript. In
/// this case we only care about the CreateDatabaseStatement and wish to
/// update its definition to no longer rely on the “$(DatabaseName)” defined
/// by DacFx. Instead we will override this with our own definition.
///
/// To figure out how this all worked, the basic approach is to debug into a
/// deployment and look for the step you want to change. In this case, you
/// would debug until you found the CreateDatabaseStep, and examine what the
/// basic ScriptDom looks like for that step. Once that’s done, the next step
/// is to hook a TSqlConcreteFragmentVisitor up and visit the type of
/// fragment you care about – look at its properties in the debugger, and
/// look at the API definition to understand what the properties do.Finally,
/// write some sample code that does what you want (changing the filename for
/// the primary filegroup)and debug in to verify it works, in addition to
/// writing a unit test that verifies it and catches regressions
///

private class CreateDatabaseStatementVisitor : TSqlConcreteFragmentVisitor
{
private const string DatabaseNameVariable = “$(DatabaseName)”;
private const string DatabaseLogNameVariable = “$(DatabaseName)_log”;
private readonly string _databaseFileName;
private readonly string _logFileName;

public CreateDatabaseStatementVisitor(string databaseFileName, string logFileName)
{
_databaseFileName = databaseFileName;
_logFileName = logFileName;
}

public override void ExplicitVisit(CreateDatabaseStatement node)
{
Visit(node);
}

public override void Visit(CreateDatabaseStatement node)
{
foreach (FileGroupDefinition fg in node.FileGroups)
{
foreach (FileDeclaration fileDecl in fg.FileDeclarations)
{
// The Primary filegroup is where the database’s MDF file will be
// present

if (fileDecl.IsPrimary)
{
NameFileDeclarationOption nameOption = fileDecl.Options.SingleOrDefault(
opt => opt.OptionKind == FileDeclarationOptionKind.Name) as NameFileDeclarationOption;
if (nameOption == null)
{
continue;
}

// Verify that the filename has the pattern “$(DatabaseName)”, and
// overwrite
if (string.Compare(nameOption.LogicalFileName.Identifier.Value,
DatabaseNameVariable,
StringComparison.OrdinalIgnoreCase) == 0)
{
FileNameFileDeclarationOption fileNameOption = fileDecl.Options.SingleOrDefault(
opt => opt.OptionKind == FileDeclarationOptionKind.FileName) as FileNameFileDeclarationOption;
if (fileNameOption != null)
{
fileNameOption.OSFileName = new StringLiteral { Value = _databaseFileName, IsNational = false };
}
}

}
}
}

foreach (FileDeclaration logFile in node.LogOn)
{
NameFileDeclarationOption nameOption = logFile.Options.SingleOrDefault(
opt => opt.OptionKind == FileDeclarationOptionKind.Name) as NameFileDeclarationOption;
if (nameOption == null)
{
continue;
}

if (string.Compare(nameOption.LogicalFileName.Identifier.Value,
DatabaseLogNameVariable,
StringComparison.OrdinalIgnoreCase) == 0)
{
FileNameFileDeclarationOption fileNameOption = logFile.Options.SingleOrDefault(
opt => opt.OptionKind == FileDeclarationOptionKind.FileName) as FileNameFileDeclarationOption;
if (fileNameOption != null)
{
fileNameOption.OSFileName = new StringLiteral { Value = _logFileName, IsNational = false };
}
}
}
}

}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using Microsoft.SqlServer.Dac;
using Public.Dac.Samples.Contributors;
using System.Data.SqlClient;

namespace ConsoleApplication5
{
class Program
{
static void Main(string[] args)
{
string targetConnectionString = “Server=.\\SQL2014;Trusted_Connection=True;”;
string targetDatabaseName = “FrontLine_$ID=123456_$Type=Primary”;
string pathBacpac = @”C:\Users\JohnDoe\Documents\SQL Server Management Studio\DAC Packages\FrontLine_$ID=123456_$Type=Primary.bacpac”;
string mdfFilePath = Path.Combine(@”C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\”, targetDatabaseName + “.mdf”);
string mdfFilePathHex = ConvertToHexString(mdfFilePath);
string ldfFilePath = Path.Combine(@”C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\”, targetDatabaseName + “.ldf”);
string ldfFilePathHex = ConvertToHexString(ldfFilePath);
ImportBacPacToDB(targetConnectionString, targetDatabaseName, pathBacpac, mdfFilePathHex, ldfFilePathHex);
}

private static string ConvertToHexString(string s)
{
byte[] ba = Encoding.Unicode.GetBytes(s);
string result = BitConverter.ToString(ba);
result = result.Replace(“-“, “”);
return result;
}

static void ImportBacPacToDB(string targetConnectionString, string targetDatabaseName, string pathBacpac, string mdfFilePath, string ldfFilePath)
{
DropExistingDatabase(targetConnectionString, targetDatabaseName);

DacServices svcDAC = new DacServices(targetConnectionString);
using (BacPackage oBacPackage = BacPackage.Load(pathBacpac))
{
DacImportOptions options = new DacImportOptions();
options.ImportContributors = DbCreateDatabaseModifier.ContributorId;
options.ImportContributorArguments = BuildContributorArguments(
new Dictionary<string, string>()
{
{DbCreateDatabaseModifier.MdfFilePathArg, mdfFilePath},
{DbCreateDatabaseModifier.LdfFilePathArg, ldfFilePath}
});
svcDAC.ImportBacpac(oBacPackage, targetDatabaseName, options);
}
}

private static void DropExistingDatabase(String targetConnectionString, String targetDatabaseName)
{
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(targetConnectionString);
scsb.InitialCatalog = “Master”;
using (SqlConnection connection = new SqlConnection(scsb.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = “select count(*) from sys.databases where [name] = ‘” + targetDatabaseName + “‘”;
int count = (int)command.ExecuteScalar();
if (count > 0)
{
command.CommandText = “drop database [” + targetDatabaseName + “]”;
command.ExecuteNonQuery();
}
}
}
}

private static DacDeployOptions SetCreateDatabaseContributorOptions(string mdfFilePath, string ldfFilePath)
{
DacDeployOptions options = new DacDeployOptions();
options.AdditionalDeploymentContributors = DbCreateDatabaseModifier.ContributorId;
options.AdditionalDeploymentContributorArguments =
BuildContributorArguments(new Dictionary<string, string>()
{
{DbCreateDatabaseModifier.MdfFilePathArg, mdfFilePath},
{DbCreateDatabaseModifier.LdfFilePathArg, ldfFilePath}
});
return options;
}

private static string BuildContributorArguments(Dictionary<string, string> arguments)
{
StringBuilder args = new StringBuilder();

foreach (var entry in arguments)
{
AddArgument(args, entry.Key, entry.Value);
}
return args.ToString();
}

private static void AddArgument(StringBuilder args, string key, string value)
{
if (args.Length > 0)
{
args.Append(“;”);
}
args.Append(key).Append(“=”).Append(value);
}
}
}

Note the following:

string targetConnectionString = "Server=.\\SQL2014;Trusted_Connection=True;";
<————connection details

string pathBacpac = @"C:\Users\XXXX\Documents\SQL Server Management Studio\DAC Packages\FrontLine_$ID=123456_$Type=Primary.bacpac";
<————–bacpac path

string mdfFilePath = Path.Combine(@"C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\", targetDatabaseName + ".mdf");
<————–path where you need to store the mdf file

string ldfFilePath = Path.Combine(@"C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\", targetDatabaseName + ".ldf");
<————– path where you need to store the ldf file

Advertisements

Restoring a Decrypted Database

Customer has TDE encrypted database that needs to be restored onto another SQL Server instance.  He turned off encryption, backed it up with a verify, and copied it to the other instance.

During the restore, it goes to 100% then gives the following error:

100 percent processed.
Processed 89426 pages for database 'MyDatabase', file 'mydatabase' on file 1.
Processed 4 pages for database 'MyDatabase', file 'mydatabase_log' on file 1.
Msg 3283, Level 16, State 1, Line 2
The file "mydatabase_log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Microsoft says this was fixed in Service Pack 2 and the customer is on SP2.

Frustrated

Try this:

DROP CERTIFICATE Certificate_Name
go
DROP DATABASE ENCRYPTION KEY
go

Back up the database again.

Restore it on the other instance

*Remember to always keep a backup of the certificate used for database encryption, even after you have decrypted it.  You never know when you’re going to need to restore an old backup.