Scripts Folder

From RedGateWiki

This code demonstrates how to synchronize data from a SQL Server database into an empty folder containing SQL scripts. First, backup the example database "WidgetDev" because in order to synchronize the data, a schema has to be created in the scripts folder and this example takes that schema from the WidgetDev backup file.

C#

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using RedGate.Shared.SQL.ExecutionBlock;
using RedGate.Shared.Utils;
using RedGate.SQLCompare.Engine;
using RedGate.SQLCompare.Engine.ReadFromFolder;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLDataCompare.Engine.ResultsStore;
namespace SQLDataCompareCodeSnippets
{
/// <summary>
/// Create an empty (schema only) script folder, synchronize static data into it
/// </summary>
public class StaticDataWithScriptsExample
{
public void RunExample()
{
// Constant information for the test
// To run this test alter these settings to reflect local configuration
const string ServerNameToScript = @".";
const string DatabaseNameToScript = @"WidgetDevScripts";
string[] StaticDataTableNames = new string[]
{
"Widgets",
"WidgetDescriptions",
"WidgetPrices"
};
const string UserName = "";
const string Password = "";
bool UseIntegratedAuthentication = String.IsNullOrEmpty(UserName);
const string ScriptFolderPath = @"WidgetDev";
const string BackupPath = @"WidgetDev.bak";
const bool CleanUpAfterExecution = true;
// First restore the Backup of WidgetDev
Console.WriteLine(String.Format("RESTORE DATABASE - Creating new database {0}.{1} from {2}", ServerNameToScript, DatabaseNameToScript, BackupPath));
if (!RestoreDatabase(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, UserName, Password, BackupPath))
{
// Couldn't restore database
Console.WriteLine("Database couldn't be restored.");
return;
}
Console.WriteLine("END RESTORE DATABASE");
// Create the script folder containing the schema information
// Uses SQL Compare
Console.WriteLine("BEGIN - Create Schema Scripts");
Console.WriteLine(String.Format("From {0}.{1} -> {2}", ServerNameToScript, DatabaseNameToScript, ScriptFolderPath));
if (!CreateSchemaScripts(
ServerNameToScript,
DatabaseNameToScript,
UseIntegratedAuthentication,
UserName,
Password,
ScriptFolderPath
))
{
// Couldn't create schema scripts
Console.WriteLine("Schema scripts couldn't be created.");
return;
}
Console.WriteLine("END - Create Schema Scripts");
Console.WriteLine(String.Format("BEGIN - Synchronize Static Data into Scripts Folder {0} ", ScriptFolderPath));
if (!SynchronizeStaticDataIntoScriptFolder(
ServerNameToScript,
DatabaseNameToScript,
UseIntegratedAuthentication,
UserName,
Password,
ScriptFolderPath,
StaticDataTableNames))
{
// Couldn't synchronize static data into Script Folder
Console.WriteLine("Couldn't Synchronize Static Data into Scripts Folder.");
return;
}
Console.WriteLine("END - Synchronize Static Data into Scripts Folder");
//Perform execution cleanup
if (CleanUpAfterExecution)
{
Console.WriteLine("BEGIN CLEANUP");
Cleanup(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, UserName, Password,
ScriptFolderPath);
Console.WriteLine("CLEANUP COMPLETE");
}
}
/// <summary>
/// Cleans up after the running of this program
/// </summary>
/// <param name="sServerName">SQL Server name</param>
/// <param name="sDatabaseName">Database name</param>
/// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
/// <param name="sUserName">Username or null if using Integrated Authentication</param>
/// <param name="sPassword">Password or null if using Integrated Authentication</param>
/// <param name="sScriptFolderPath">Destination of the Script Folder</param>
/// <returns></returns>
private static void Cleanup(
string sServerName,
string sDatabaseName,
bool bUseIntegratedAuthentication,
string sUserName,
string sPassword,
string sScriptFolderPath)
{
try
{
// Check whether the destination directory exists
if (Directory.Exists(sScriptFolderPath))
{
Directory.Delete(sScriptFolderPath, true);
}
}
catch (Exception ex)
{
LogException(ex);
}
try
{
// Connect to SQL Server
ConnectionProperties connProp = CreateConnectionProperties(sServerName, "master",
bUseIntegratedAuthentication, sUserName,
sPassword);
using (SqlConnection sqlConn = new SqlConnection(connProp.ConnectionString))
{
sqlConn.Open();
// Drop the database we created
using (SqlCommand sqlCommand = sqlConn.CreateCommand())
{
sqlCommand.CommandTimeout = 5000;
sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName);
sqlCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
LogException(ex);
}
}
/// <summary>
/// Restore the database from the specified backup
/// </summary>
/// <param name="sServerName">SQL Server name</param>
/// <param name="sDatabaseName">Database name</param>
/// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
/// <param name="sUserName">Username or null if using Integrated Authentication</param>
/// <param name="sPassword">Password or null if using Integrated Authentication</param>
/// <param name="sBackupToRestore">Backup file to restore</param>
/// <returns></returns>
private static bool RestoreDatabase(
string sServerName,
string sDatabaseName,
bool bUseIntegratedAuthentication,
string sUserName,
string sPassword,
string sBackupToRestore)
{
try
{
if (!File.Exists(sBackupToRestore))
{
Console.WriteLine(String.Format("ERROR: Backup file {0} doesn't exist.", sBackupToRestore));
return false;
}
}
catch (Exception ex)
{
LogException(ex);
return false;
}
try
{
// Connect to SQL Server
ConnectionProperties connProp = CreateConnectionProperties(sServerName, "master",
bUseIntegratedAuthentication, sUserName,
sPassword);
using (SqlConnection sqlConn = new SqlConnection(connProp.ConnectionString))
{
sqlConn.Open();
// be optomistic about the drop command, database may not exist
try
{
using (SqlCommand sqlCommand = sqlConn.CreateCommand())
{
sqlCommand.CommandTimeout = 5000;
sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception)
{
}
// Create new database
using (SqlCommand sqlCommand = sqlConn.CreateCommand())
{
sqlCommand.CommandTimeout = 5000;
sqlCommand.CommandText = String.Format("CREATE DATABASE {0}", sDatabaseName);
sqlCommand.ExecuteNonQuery();
}
// Get the backup filelist
List<string> primaryDataFiles = new List<string>();
List<string> logFiles = new List<string>();
using (SqlCommand sqlCommand = sqlConn.CreateCommand())
{
sqlCommand.CommandTimeout = 5000;
sqlCommand.CommandText = String.Format("RESTORE FILELISTONLY FROM DISK = '{0}'", sBackupToRestore);
using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
{
while(dataReader.Read())
{
if (dataReader["type"].Equals("D"))
{
primaryDataFiles.Add(dataReader["LogicalName"] as string);
}
else if (dataReader["type"].Equals("L"))
{
logFiles.Add(dataReader["LogicalName"] as string);
}
}
}
}
//restore the backup file into the database
using (SqlCommand sqlCommand = sqlConn.CreateCommand())
{
sqlCommand.CommandTimeout = 5000;
StringBuilder restoreCommand = new StringBuilder();
restoreCommand.AppendFormat(
"RESTORE DATABASE {0} FROM DISK = '{1}' WITH FILE=1, REPLACE", sDatabaseName,
sBackupToRestore);
int count = 0;
foreach (string logicalFile in primaryDataFiles)
{
restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logicalFile,
String.Format("{0}{1}{2}{3}.mdf", new FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count));
count++;
}
count = 0;
foreach (string logFile in logFiles)
{
restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logFile,
String.Format("{0}{1}{2}{3}.ldf", new FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count));
count++;
}
sqlCommand.CommandText = restoreCommand.ToString();
sqlCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
LogException(ex);
return false;
}
return true;
}
/// <summary>
/// Create the Schema Scripts in the specified folder for the specified database
/// </summary>
/// <param name="sServerName">SQL Server name</param>
/// <param name="sDatabaseName">Database to script</param>
/// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
/// <param name="sUserName">Username or null if using Integrated Authentication</param>
/// <param name="sPassword">Password or null if using Integrated Authentication</param>
/// <param name="sScriptFolderPath">Destination of the Script Folder</param>
/// <returns></returns>
private static bool CreateSchemaScripts(
string sServerName,
string sDatabaseName,
bool bUseIntegratedAuthentication,
string sUserName,
string sPassword,
string sScriptFolderPath)
{
try
{
// Check whether the destination directory exists
if (Directory.Exists(sScriptFolderPath))
{
Directory.Delete(sScriptFolderPath,true);
}
// Create the script folder directory
Directory.CreateDirectory(sScriptFolderPath);
}
catch (Exception ex)
{
LogException(ex);
return false;
}
try
{
using (Database dLiveDatabase = new Database())
{
// Register the live database whose schema will be written to the script folder
Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName));
dLiveDatabase.Status += StatusCallBack;
try
{
dLiveDatabase.Register(CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication, sUserName, sPassword), Options.Default);
}
catch (SqlException sqlEx)
{
Console.WriteLine(sqlEx.Message);
Console.WriteLine(@"</li>
Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:</li>
o The sample databases are not installed</li>
o ServerName not set to the location of the target database</li>
o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor</li>
o Remote connections not enabled", sDatabaseName, sServerName);
return false;
}
// Save the registered database as a Schema Scripts Folder
Console.WriteLine(String.Format("Saving Database to {0}", sScriptFolderPath));
// Configure Script Folder settings
ScriptDatabaseInformation sdiDatabaseInformation = new ScriptDatabaseInformation();
// save the scripts as Unicode
sdiDatabaseInformation.ScriptEncodingType = ScriptEncodingType.UTF16;
dLiveDatabase.SaveToFolder(sScriptFolderPath, sdiDatabaseInformation);
}
}
catch (Exception ex)
{
LogException(ex);
return false;
}
return true;
}
/// <summary>
/// Using the existing Script Folder sync the static data from the Static Tables specified into the folder
/// </summary>
/// <param name="sServerName">SQL Server name</param>
/// <param name="sDatabaseName">Database to script</param>
/// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
/// <param name="sUserName">Username or null if using Integrated Authentication</param>
/// <param name="sPassword">Password or null if using Integrated Authentication</param>
/// <param name="sScriptFolderPath">The Script Folder to synchronize the static data into</param>
/// <param name="sStaticDataTables">The Tables within the database that contain the static data</param>
/// <returns></returns>
private static bool SynchronizeStaticDataIntoScriptFolder(
string sServerName,
string sDatabaseName,
bool bUseIntegratedAuthentication,
string sUserName,
string sPassword,
string sScriptFolderPath,
IEnumerable<string> sStaticDataTables)
{
try
{
// Check whether the destination directory exists
if (!Directory.Exists(sScriptFolderPath))
{
Console.WriteLine(String.Format("ERROR: Script folder {0} doesn't exist.", sScriptFolderPath));
return false;
}
// Check whether the destination folder contains a Script Folder
if (!FolderDataSource.DoesDatabaseInformationFileExist(sScriptFolderPath))
{
Console.WriteLine(String.Format("ERROR: Folder {0} doesn't contain a Script Folder.", sScriptFolderPath));
return false;
}
}
catch (Exception ex)
{
LogException(ex);
return false;
}
try
{
using (Database dScriptFolder = new Database())
using (Database dLiveDatabase = new Database())
using (ComparisonSession csComparison = new ComparisonSession())
{
// Register the live database whose schema will be written to the script folder
Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName));
// Now we have built the script folder we can register the database using RegisterForDataCompare
dLiveDatabase.Status += StatusCallBack;
try
{
dLiveDatabase.RegisterForDataCompare(
CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication,
sUserName, sPassword), Options.Default);
}
catch (SqlException sqlEx)
{
Console.WriteLine(sqlEx.Message);
Console.WriteLine(
@"</li>
Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:</li>
o The sample databases are not installed</li>
o ServerName not set to the location of the target database</li>
o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor</li>
o Remote connections not enabled",
sDatabaseName, sServerName);
return false;
}
// Register the script database
Console.WriteLine(String.Format("Registering Script Database from {0}", sScriptFolderPath));
dScriptFolder.Status += StatusCallBack;
// NOTE - always pass null for Script Database Information in Data Compare (will read script settings from the xml file in the folder)
dScriptFolder.RegisterForDataCompare(sScriptFolderPath, null, Options.Default);
Console.WriteLine("Creating Mappings...");
SchemaMappings smMappings = new SchemaMappings();
smMappings.CreateMappings(dLiveDatabase, dScriptFolder);
// Only compare tables that contain static data
// First exclude all tables
foreach (TableMapping tmMapping in smMappings.TableMappings)
{
tmMapping.Include = false;
}
// Now re-enable for the comparison those tables that contain static data
// N^2 algorithm only used for clarity
foreach (string staticDataTable in sStaticDataTables)
{
foreach (TableMapping tmMapping in smMappings.TableMappings)
{
IDatabaseObject scriptFolderTable = tmMapping.Obj2;
if (scriptFolderTable != null &&
scriptFolderTable.Name == staticDataTable)
{
if (tmMapping.Status != TableMappingStatus.UnableToCompare)
{
tmMapping.Include = true;
}
else
{
Console.WriteLine(
String.Format("Static Data Table {0} can't be included for comparison",
staticDataTable));
}
}
}
}
Console.WriteLine("Performing comparison (before sync)...");
// Perform the comparison
csComparison.Status += StatusCallBack;
csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings);
Console.WriteLine("Summary of Results of Comparison (before sync)...");
SummarizeDifferences(csComparison);
Console.WriteLine("Perform Synchronization...");
SqlProvider provider = new SqlProvider();
// Get the Migration SQL to synchronize the scripts folder
using (ExecutionBlock block = provider.GetMigrationSQL(csComparison,delegate { return true; },true))
{
// The execution block contains a script version of the changes that will be
// applied to the script folder for review purposes
block.Dispose();
}
// perform the synchronization
provider.Synchronize();
Console.WriteLine("Performing comparison (after sync)...");
// Perform the comparison
csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings);
Console.WriteLine("Summary of Results of Comparison (after sync)...");
SummarizeDifferences(csComparison);
}
}
catch (Exception ex)
{
LogException(ex);
return false;
}
return true;
}
/// <summary>
/// Provide a summary of the differences
/// </summary>
/// <param name="csComparison">Comparison Session to summarize</param>
private static void SummarizeDifferences(ComparisonSession csComparison)
{
foreach (TableDifference difference in csComparison.TableDifferences)
{
if (difference.TableMapping.Include) // included difference
{
DifferencesSummary summary = difference.DifferencesSummary;
Console.WriteLine(String.Format("Table {0} ", difference.TargetTable(true).Name));
Console.WriteLine(String.Format("Live Only {0}", summary.DifferenceCount(Row.RowType.In1)));
Console.WriteLine(String.Format("Script Folder Only {0}", summary.DifferenceCount(Row.RowType.In2)));
Console.WriteLine(String.Format("In both but Different {0}", summary.DifferenceCount(Row.RowType.Different)));
Console.WriteLine(String.Format("In both and the Same {0}", summary.DifferenceCount(Row.RowType.Same)));
}
}
}
/// <summary>
/// Create a connection properties object to connect to the current Server/Database specified
/// </summary>
/// <param name="sServerName">SQL Server name</param>
/// <param name="sDatabaseName">Database to script</param>
/// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
/// <param name="sUserName">Username or null if using Integrated Authentication</param>
/// <param name="sPassword">Password or null if using Integrated Authentication</param>
/// <returns>A connection properties</returns>
private static ConnectionProperties CreateConnectionProperties(
string sServerName,
string sDatabaseName,
bool bUseIntegratedAuthentication,
string sUserName,
string sPassword)
{
ConnectionProperties connProp = new ConnectionProperties();
connProp.ServerName = sServerName;
connProp.DatabaseName = sDatabaseName;
connProp.IntegratedSecurity = bUseIntegratedAuthentication;
if (!bUseIntegratedAuthentication)
{
connProp.UserName = sUserName;
connProp.Password = sPassword;
}
return connProp;
}
/// <summary>
/// Log an exception to the command line
/// </summary>
/// <param name="ex">The exception to log</param>
private static void LogException(Exception ex)
{
if (ex == null)
{
return;
}
Console.WriteLine("\n-------------------");
Console.WriteLine(String.Format("Caught {0}",ex.GetType().Name));
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
Console.WriteLine("-------------------");
}
private static void StatusCallBack(object sender, StatusEventArgs e)
{
if (e.Message != null)
{
Console.WriteLine(e.Message);
}
if (e.Percentage != -1)
{
Console.WriteLine("{0}%", e.Percentage);
}
}
}
}

VB

Option Explicit On
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.IO
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLCompare.Engine.ReadFromFolder
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore
Imports RedGate.Shared.Utils
Imports System.Text
''' <summary>
''' Create an empty (schema only) script folder, synchronize static data into it
''' </summary>
Public Class StaticDataWithScriptsExample
Public Sub RunExample()
' Constant information for the test
' To run this test alter these settings to reflect local configuration
Dim ServerNameToScript As String = "."
Dim DatabaseNameToScript As String = "WidgetDevScripts"
Dim StaticDataTableNames() As String = {"Widgets", "WidgetDescriptions", "WidgetPrices"}
Dim Username As String = ""
Dim Password As String = ""
Dim UseIntegratedAuthentication As Boolean = String.IsNullOrEmpty(Username)
Dim ScriptFolderPath As String = "WidgetDev"
Dim BackupPath As String = "WidgetDev.bak"
Dim CleanUpAfterExecution As Boolean = True
' First restore the Backup of WidgetDev
Console.WriteLine(String.Format("RESTORE DATABASE - Creating new database {0}.{1} from {2}", ServerNameToScript, DatabaseNameToScript, BackupPath))
If (Not RestoreDatabase(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, BackupPath)) Then
' Couldn't restore database
Console.WriteLine("Database couldn't be restored.")
Return
End If
Console.WriteLine("END RESTORE DATABASE")
' Create the script folder containing the schema information
' Uses SQL Compare
Console.WriteLine("BEGIN - Create Schema Scripts")
Console.WriteLine(String.Format("From {0}.{1} -> {2}", ServerNameToScript, DatabaseNameToScript, ScriptFolderPath))
If (Not CreateSchemaScripts(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, ScriptFolderPath)) Then
' Couldn't create schema scripts
Console.WriteLine("Schema scripts couldn't be created.")
Return
End If
Console.WriteLine("END - Create Schema Scripts")
Console.WriteLine(String.Format("BEGIN - Synchronize Static Data into Scripts Folder {0} ", ScriptFolderPath))
If (Not SynchronizeStaticDataIntoScriptFolder(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, ScriptFolderPath, StaticDataTableNames)) Then
' Couldn't synchronize static data into Script Folder
Console.WriteLine("Couldn't Synchronize Static Data into Scripts Folder.")
Return
End If
Console.WriteLine("END - Synchronize Static Data into Scripts Folder")
'Perform execution cleanup
If (CleanUpAfterExecution) Then
Console.WriteLine("BEGIN CLEANUP")
Cleanup(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, ScriptFolderPath)
Console.WriteLine("CLEANUP COMPLETE")
End If
End Sub
''' <summary>
''' Cleans up after the running of this program
''' </summary>
''' <param name="sServerName">SQL Server name</param>
''' <param name="sDatabaseName">Database name</param>
''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
''' <param name="sUserName">Username or null if using Integrated Authentication</param>
''' <param name="sPassword">Password or null if using Integrated Authentication</param>
''' <param name="sScriptFolderPath">Destination of the Script Folder</param>
Private Sub Cleanup(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sScriptFolderPath As String)
Try
' Check whether the destination directory exists
If (Directory.Exists(sScriptFolderPath)) Then
Directory.Delete(sScriptFolderPath, True)
End If
Catch ex As Exception
LogException(ex)
End Try
Try
' Connect to SQL Server
Dim connProp As ConnectionProperties = CreateConnectionProperties(sServerName, "master", bUseIntegratedAuthentication, sUserName, sPassword)
Using sqlConn As New SqlConnection(connProp.ConnectionString)
sqlConn.Open()
' Drop the database we created
Dim sqlCommand As SqlCommand = sqlConn.CreateCommand
Using sqlCommand
sqlCommand.CommandTimeout = 5000
sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName)
sqlCommand.ExecuteNonQuery()
End Using
End Using
Catch ex As Exception
LogException(ex)
End Try
End Sub
''' <summary>
''' Restore the database from the specified backup
''' </summary>
''' <param name="sServerName">SQL Server name</param>
''' <param name="sDatabaseName">Database name</param>
''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
''' <param name="sUserName">Username or null if using Integrated Authentication</param>
''' <param name="sPassword">Password or null if using Integrated Authentication</param>
''' <param name="sBackupToRestore">Backup file to restore</param>
''' <returns></returns>
Private Function RestoreDatabase(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sBackupToRestore As String) As Boolean
Try
If (Not File.Exists(sBackupToRestore)) Then
Console.WriteLine(String.Format("ERROR: Backup file {0} doesn't exist.", sBackupToRestore))
Return False
End If
Catch ex As Exception
LogException(ex)
Return False
End Try
Try
' Connect to SQL Server
Dim connProp As ConnectionProperties = CreateConnectionProperties(sServerName, "master", bUseIntegratedAuthentication, sUserName, sPassword)
Using sqlConn As New SqlConnection(connProp.ConnectionString)
sqlConn.Open()
Dim sqlCommand As SqlCommand
' be optomistic about the drop command, database may not exist
Try
sqlCommand = sqlConn.CreateCommand()
Using sqlCommand
sqlCommand.CommandTimeout = 5000
sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName)
sqlCommand.ExecuteNonQuery()
End Using
Catch e As Exception
End Try
' Create new database
sqlCommand = sqlConn.CreateCommand()
Using sqlCommand
sqlCommand.CommandTimeout = 5000
sqlCommand.CommandText = String.Format("CREATE DATABASE {0}", sDatabaseName)
sqlCommand.ExecuteNonQuery()
End Using
' Get the backup filelist
Dim primaryDataFiles As New List(Of String)
Dim logFiles As New List(Of String)
sqlCommand = sqlConn.CreateCommand()
Using sqlCommand
sqlCommand.CommandTimeout = 5000
sqlCommand.CommandText = String.Format("RESTORE FILELISTONLY FROM DISK = '{0}'", sBackupToRestore)
Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader()
Using dataReader
While (dataReader.Read())
If dataReader("type").Equals("D") Then
primaryDataFiles.Add(dataReader("LogicalName"))
ElseIf dataReader("type").Equals("L") Then
logFiles.Add(dataReader("LogicalName"))
End If
End While
End Using
End Using
'restore the backup file into the database
sqlCommand = sqlConn.CreateCommand()
Using sqlCommand
sqlCommand.CommandTimeout = 5000
Dim restoreCommand As StringBuilder = New StringBuilder()
restoreCommand.AppendFormat("RESTORE DATABASE {0} FROM DISK = '{1}' WITH FILE=1, REPLACE", sDatabaseName, sBackupToRestore)
Dim count As Int32 = 0
For Each logicalFile As String In primaryDataFiles
restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logicalFile, String.Format("{0}{1}{2}{3}.mdf", New FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count))
count += 1
Next logicalFile
count = 0
For Each logFile As String In logFiles
restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logFile, String.Format("{0}{1}{2}{3}.ldf", New FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count))
count += 1
Next logFile
sqlCommand.CommandText = restoreCommand.ToString()
sqlCommand.ExecuteNonQuery()
End Using
End Using
Catch ex As Exception
LogException(ex)
Return False
End Try
Return True
End Function
''' <summary>
''' Create the Schema Scripts in the specified folder for the specified database
''' </summary>
''' <param name="sServerName">SQL Server name</param>
''' <param name="sDatabaseName">Database to script</param>
''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
''' <param name="sUserName">Username or null if using Integrated Authentication</param>
''' <param name="sPassword">Password or null if using Integrated Authentication</param>
''' <param name="sScriptFolderPath">Destination of the Script Folder</param>
''' <returns></returns>
Private Function CreateSchemaScripts(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sScriptFolderPath As String) As Boolean
Try
' Check whether the destination directory exists
If (Directory.Exists(sScriptFolderPath)) Then
Directory.Delete(sScriptFolderPath, True)
End If
' Create the script folder directory
Directory.CreateDirectory(sScriptFolderPath)
Catch ex As Exception
LogException(ex)
Return False
End Try
Try
Using dLiveDatabase As New Database()
' Register the live database whose schema will be written to the script folder
Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName))
dLiveDatabase.Status = New StatusEventHandler(AddressOf StatusCallback)
Try
dLiveDatabase.Register(CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication, sUserName, sPassword), Options.Default)
Catch sqlEx As SqlException
Console.WriteLine(sqlEx.Message)
Console.WriteLine(vbCrLf & _
"Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:" & vbCrLf & _
" o The sample databases are not installed" & vbCrLf & _
" o ServerName not set to the location of the target database" & vbCrLf & _
" o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCrLf & _
" o Remote connections not enabled", sDatabaseName, sServerName)
Return False
End Try
' Save the registered database as a Schema Scripts Folder
Console.WriteLine(String.Format("Saving Database to {0}", sScriptFolderPath))
' Configure Script Folder settings
Dim sdiDatabaseInformation As New ScriptDatabaseInformation()
' save the scripts as Unicode
sdiDatabaseInformation.ScriptEncodingType = ScriptEncodingType.UTF16
dLiveDatabase.SaveToFolder(sScriptFolderPath, sdiDatabaseInformation)
End Using
Catch ex As Exception
LogException(ex)
Return False
End Try
Return True
End Function
''' <summary>
''' A function that always returns true
''' </summary>
''' <remarks></remarks>
Private Function TrueDelegate(ByVal syncRecord As SynchronizationRecord) As Boolean
Return True
End Function
''' <summary>
''' Using the existing Script Folder sync the static data from the Static Tables specified into the folder
''' </summary>
''' <param name="sServerName">SQL Server name</param>
''' <param name="sDatabaseName">Database to script</param>
''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
''' <param name="sUserName">Username or null if using Integrated Authentication</param>
''' <param name="sPassword">Password or null if using Integrated Authentication</param>
''' <param name="sScriptFolderPath">The Script Folder to synchronize the static data into</param>
''' <param name="sStaticDataTables">The Tables within the database that contain the static data</param>
''' <returns></returns>
Private Function SynchronizeStaticDataIntoScriptFolder(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sScriptFolderPath As String, ByVal sStaticDataTables As IEnumerable(Of String)) As Boolean
Try
' Check whether the destination directory exists
If Not Directory.Exists(sScriptFolderPath) Then
Console.WriteLine(String.Format("ERROR: Script folder {0} doesn't exist.", sScriptFolderPath))
Return False
End If
' Check whether the destination folder contains a Script Folder
If Not FolderDataSource.DoesDatabaseInformationFileExist(sScriptFolderPath) Then
Console.WriteLine(String.Format("ERROR: Folder {0} doesn't contain a Script Folder.", sScriptFolderPath))
Return False
End If
Catch ex As Exception
LogException(ex)
Return False
End Try
Try
Using dScriptFolder As New Database()
Using dLiveDatabase As New Database()
Using csComparison As New ComparisonSession()
' Register the live database whose schema will be written to the script folder
Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName))
' Now we have built the script folder we can register the database using RegisterForDataCompare
dLiveDatabase.Status = New StatusEventHandler(AddressOf StatusCallback)
Try
dLiveDatabase.RegisterForDataCompare(CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication, sUserName, sPassword), Options.Default)
Catch sqlEx As SqlException
Console.WriteLine(sqlEx.Message)
Console.WriteLine(vbCrLf & _
"Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:" & vbCrLf & _
" o The sample databases are not installed" & vbCrLf & _
" o ServerName not set to the location of the target database" & vbCrLf & _
" o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCrLf & _
" o Remote connections not enabled", sDatabaseName, sServerName)
Return False
End Try
' Register the script database
Console.WriteLine(String.Format("Registering Script Database from {0}", sScriptFolderPath))
dScriptFolder.Status = New StatusEventHandler(AddressOf StatusCallback)
' NOTE - always pass Nothing for Script Database Information in Data Compare (will read script settings from the xml file in the folder)
dScriptFolder.RegisterForDataCompare(sScriptFolderPath, Nothing, Options.Default)
Console.WriteLine("Creating Mappings...")
Dim smMappings As SchemaMappings = New SchemaMappings()
smMappings.CreateMappings(dLiveDatabase, dScriptFolder)
' Only compare tables that contain static data
' First exclude all tables
For Each tmMapping As TableMapping In smMappings.TableMappings
tmMapping.Include = False
Next tmMapping
' Now re-enable for the comparison those tables that contain static data
' N^2 algorithm only used for clarity
For Each staticDataTable As String In sStaticDataTables
For Each tmMapping As TableMapping In smMappings.TableMappings
Dim scriptFolderTable As IDatabaseObject = tmMapping.Obj2
If ((Not scriptFolderTable Is Nothing) And scriptFolderTable.Name.Equals(staticDataTable)) Then
If tmMapping.Status <> TableMappingStatus.UnableToCompare Then
tmMapping.Include = True
Else
Console.WriteLine(String.Format("Static Data Table {0} can't be included for comparison", staticDataTable))
End If
End If
Next tmMapping
Next staticDataTable
Console.WriteLine("Performing comparison (before sync)...")
' Perform the comparison
csComparison.Status = New StatusEventHandler(AddressOf StatusCallback)
csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings)
Console.WriteLine("Summary of Results of Comparison (before sync)...")
SummarizeDifferences(csComparison)
Console.WriteLine("Perform Synchronization...")
Dim provider As New SqlProvider()
' Get the Migration SQL to synchronize the scripts folder
Using block = provider.GetMigrationSQL(csComparison, AddressOf TrueDelegate, True)
' The execution block contains a script version of the changes that will be
' applied to the script folder for review purposes
block.Dispose()
End Using
' perform the synchronization
provider.Synchronize()
Console.WriteLine("Performing comparison (after sync)...")
' Perform the comparison
csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings)
Console.WriteLine("Summary of Results of Comparison (after sync)...")
SummarizeDifferences(csComparison)
End Using
End Using
End Using
catch ex As Exception
LogException(ex)
Return False
End Try
Return True
End Function
''' <summary>
''' Provide a summary of the differences
''' </summary>
''' <param name="csComparison">Comparison Session to summarize</param>
Private Sub SummarizeDifferences(ByVal csComparison As ComparisonSession)
For Each diff As TableDifference In csComparison.TableDifferences
If diff.TableMapping.Include Then ' included difference
Dim summary As DifferencesSummary = diff.DifferencesSummary
Console.WriteLine(String.Format("Table {0} ", diff.TargetTable(True).Name))
Console.WriteLine(String.Format("Live Only {0}", summary.DifferenceCount(Row.RowType.In1)))
Console.WriteLine(String.Format("Script Folder Only {0}", summary.DifferenceCount(Row.RowType.In2)))
Console.WriteLine(String.Format("In both but Different {0}", summary.DifferenceCount(Row.RowType.Different)))
Console.WriteLine(String.Format("In both and the Same {0}", summary.DifferenceCount(Row.RowType.Same)))
End If
Next diff
End Sub
''' <summary>
''' Create a connection properties object to connect to the current Server/Database specified
''' </summary>
''' <param name="sServerName">SQL Server name</param>
''' <param name="sDatabaseName">Database to script</param>
''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
''' <param name="sUserName">Username or null if using Integrated Authentication</param>
''' <param name="sPassword">Password or null if using Integrated Authentication</param>
''' <returns>A connection properties</returns>
Private Function CreateConnectionProperties(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String) As ConnectionProperties
Dim connProp As New ConnectionProperties()
connProp.ServerName = sServerName
connProp.DatabaseName = sDatabaseName
connProp.IntegratedSecurity = bUseIntegratedAuthentication
If Not bUseIntegratedAuthentication Then
connProp.UserName = sUserName
connProp.Password = sPassword
End If
Return connProp
End Function
''' <summary>
''' Log an exception to the command line
''' </summary>
''' <param name="ex">The exception to log</param>
Private Sub LogException(ByVal ex As Exception)
If ex Is Nothing Then
Return
End If
Console.WriteLine("\n-------------------")
Console.WriteLine(String.Format("Caught {0}", ex.GetType().Name))
Console.WriteLine(ex.Message)
Console.WriteLine(ex.StackTrace)
Console.WriteLine("-------------------")
End Sub
Private Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
If Not (e.Message Is Nothing) Then
Console.WriteLine(e.Message)
End If
If e.Percentage <> -1 Then
Console.WriteLine("{0}%", e.Percentage)
End If
End Sub
End Class

Scripts Folder

C#

VB

Views

Personal tools

Navigation

Search

Toolbox