Powershell scripts for extracting data and publishing databases using sqlpackage.exe

extract.ps1

#this file extracts database to  a dacpac

#path to store the dacpac 
$localDbConnStrTemplate  ="Data Source=tcp:localhost; Initial Catalog=<db;Integrated Security=True;"

#location to store dacpac
$dacpacTarget="c\testdac.dacpac"

#objects to exclude
$excludeObjectTypes="Aggregates;ApplicationRoles;Assemblies;AsymmetricKeys;BrokerPriorities;Certificates;ColumnEncryptionKeys;ColumnMasterKeys;Contracts;DatabaseRoles;Defaults;ExtendedProperties;ExternalDataSources;ExternalFileFormats;ExternalTables;Filegroups;FileTables;FullTextCatalogs;FullTextStoplists;MessageTypes;PartitionFunctions;PartitionSchemes;Permissions;Queues;RemoteServiceBindings;RoleMembership;Rules;SearchPropertyLists;SecurityPolicies;Sequences;Services;Signatures;SymmetricKeys;UserDefinedTableTypes;ClrUserDefinedTypes;Users;XmlSchemaCollections;Audits;Credentials;CryptographicProviders;DatabaseAuditSpecifications;DatabaseScopedCredentials;Endpoints;ErrorMessages;EventNotifications;EventSessions;LinkedServerLogins;LinkedServers;Logins;Routes;ServerAuditSpecifications;ServerRoleMembership;ServerRoles;ServerTriggers"

#extract the database
$sqlExtractCmd = "./sqlpackage.exe /action:extract /scs:`" $localDbConnStrTemplate`" /targetfile:`"$dacpacTarget`""

#note that this is the location of the sqlpackage utility -- it might be different on your computer depending on the version
cd "C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin"

Invoke-Expression  $sqlExtractCmd 

publish from files_files.ps1

#import the module that contains methods for building and extracting dac
#you will need to build the following cmdlet from here https://github.com/DarylSmith/DacpacToSqlCmdlet
Import-Module "<location>\DacpacSqlConverter.dll"

#location to store dacpac
$dacpacSource="C:\Users\smith\OneDrive\Documents\SQLPresentation\files\testdac.dacpac"

#location of sql files
$dbPath ="C:\Users\smith\OneDarive\Documents\SQLPresentation\MeetupDatabaseSql";


#convert the sql files into a dacpac
 Format-SqlAsDacpac -FileDirectory  $dbPath -OutputPath $dacpacSource


#path to sqlpackage.exe -- note that this is the location of your dac utility
$sqlPackageDir ="C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin"


#path to store the dacpac 
$localDbConnStr ="Data Source=tcp:localhost; Initial Catalog=MeetupEventsDb;Integrated Security=True;"


#objects to exclude
$excludeObjectTypes="Aggregates;ApplicationRoles;Assemblies;AsymmetricKeys;BrokerPriorities;Certificates;ColumnEncryptionKeys;ColumnMasterKeys;Contracts;DatabaseRoles;Defaults;ExtendedProperties;ExternalDataSources;ExternalFileFormats;ExternalTables;Filegroups;FileTables;FullTextCatalogs;FullTextStoplists;MessageTypes;PartitionFunctions;PartitionSchemes;Permissions;Queues;RemoteServiceBindings;RoleMembership;Rules;SearchPropertyLists;SecurityPolicies;Sequences;Services;Signatures;SymmetricKeys;UserDefinedTableTypes;ClrUserDefinedTypes;Users;XmlSchemaCollections;Audits;Credentials;CryptographicProviders;DatabaseAuditSpecifications;DatabaseScopedCredentials;Endpoints;ErrorMessages;EventNotifications;EventSessions;LinkedServerLogins;LinkedServers;Logins;Routes;ServerAuditSpecifications;ServerRoleMembership;ServerRoles;ServerTriggers"

#publish the database
#$sqlPublishCmd = "./sqlpackage.exe /action:publish /tcs:`"$localDbConnStr;`"  /sourcefile:`"$dacpacSource`"   /p:ExcludeObjectTypes=`"$excludeObjectTypes`""
$sqlPublishCmd = "./sqlpackage.exe /action:publish /tcs:`"$localDbConnStr;`"  /sourcefile:`"$dacpacSource`"  /p:BlockOnPossibleDataLoss=false  /p:ExcludeObjectTypes=`"$excludeObjectTypes`""

#note that this is the location of the sqlpackage utility -- it might be different on your computer depending on the version
cd "C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin"

Invoke-Expression  $sqlPublishCmd 

publish.ps1

#this file publishes a database witb a dacpac

#path to store the dacpac 
$localDbConnStrTemplate  ="Data Source=tcp:localhost; Initial Catalog=<db>;Integrated Security=True;"

#location to store dacpac
$dacpacTarget="<location>\testdac.dacpac"

#objects to exclude
$excludeObjectTypes="Aggregates;ApplicationRoles;Assemblies;AsymmetricKeys;BrokerPriorities;Certificates;ColumnEncryptionKeys;ColumnMasterKeys;Contracts;DatabaseRoles;Defaults;ExtendedProperties;ExternalDataSources;ExternalFileFormats;ExternalTables;Filegroups;FileTables;FullTextCatalogs;FullTextStoplists;MessageTypes;PartitionFunctions;PartitionSchemes;Permissions;Queues;RemoteServiceBindings;RoleMembership;Rules;SearchPropertyLists;SecurityPolicies;Sequences;Services;Signatures;SymmetricKeys;UserDefinedTableTypes;ClrUserDefinedTypes;Users;XmlSchemaCollections;Audits;Credentials;CryptographicProviders;DatabaseAuditSpecifications;DatabaseScopedCredentials;Endpoints;ErrorMessages;EventNotifications;EventSessions;LinkedServerLogins;LinkedServers;Logins;Routes;ServerAuditSpecifications;ServerRoleMembership;ServerRoles;ServerTriggers"

#extract the database
$sqlExtractCmd = "./sqlpackage.exe /action:extract /scs:`" $localDbConnStrTemplate`" /targetfile:`"$dacpacTarget`""


#note that this is the location of the sqlpackage utility -- it might be different on your computer depending on the version
cd "C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin"

Invoke-Expression  $sqlExtractCmd