SQLPackage.exe can be used for the following tasks:
- Extract: Creates a database snapshot (.dacpac) file from an on-premise (local) SQL Server or Windows Azure SQL Database.
- Export: Exports a on-premise (local) database – including database schema and user data – from SQL Server or Windows Azure SQL Database to a BACPAC package (.bacpac file).
- Import: Imports the schema and table data from a BACPAC package into a new user database in an instance of SQL Server or Windows Azure SQL Database.
- Publish: Incrementally updates a database schema to match the schema of a source .dacpac file. If the database does not exist on the server, the publish operation will create it. Otherwise, an existing database will be updated.
- DeployReport: Creates an XML report of the changes that would be made by a publish action.
- DriftReport: Creates an XML report of the changes that have been made to a registered database since it was last registered.
- Script: Creates a Transact-SQL incremental update script that updates the schema of a target to match the schema of a source.
Let’s see some examples
Export the local database to a bacpac file
“C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” /a:Export /ssn:mysqlserver /sdn:Tailspintoys /tf:C:\DataExtraction\Tailspintoys.bacpac
ssn – Specifies the name of the server that hosts the database.
sdn – Defines the name of the source database.
tf – Specifies a disk file path where the .dacpac or .bacpac file will be written.
If the logged in user is executing the script, Integrated Windows Authentication will be used. So no need to specify the username or password.
Import the bacpac file to SQLAzure
“C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe” /a:Import /sf:C:\DataExtraction\Tailspintoys.bacpac /tsn:cgrd7z8kac.database.windows.net /tdn:Tailspintoys /tu:mysysadmin@cgrd7z8kac /tp:Pa55w0rd
sf – Specifies a source file to be used as the source of action instead of database.
tsn – Specifies the name of the server that hosts the target database.
tdn – Specifies the name of the target database.
tu – SQL Server user that is used to get access to the target database.
tp – specifies password that is used to get access to the target database.
If you are planning to automate this using C# console application, there is a code sample available here