Export-SqlDatabase | Import-SqlDatabase; Please Comment on Connect!
Microsoft is listening.
We have a chance to make awesome things.
Microsoft needs your feedback.
Earlier this week I submitted a Connect Item proposing that Microsoft basically replace /augment /succeed the SQLPackage.exe functionality with at least three PowerShell cmdlets but possibly more. It should be fairly easy since SQLPackage.exe is just a wrapper around DacFx. The problem is that only 3 people have commented so far. Trust me, you don’t want what I jotted down to become what the SQL Tools team produces; I skipped over a few areas because I have other things going on. Heck I even misspelled SSDT, now how hard is that for get right?!?
Please Click the Link and Comment!
SQLPackage.exe – Needs to be made into at least 3 cmdlets (and possibly more; we have added ideas for additional cmdlets below). The first 3 cmdlets that need to be made into are:
At a minimum Export-SqlDatabase should take parameters such as -ServerInstance -Database -TargetFile -IncludeData (ExtractAllTableData).
It would also be nice if these cmdlets would work ‘smartly’ from within the provider. For instance, if you were within the provider inside a database (PS SQLSERVER:\sql\localhost\default\databases\AdventureWorks> ) and wanted to run Export-SqlDatabase you should only have to supply a filename for your export; the cmdlet should infer that you wanted to export that db on that instance and supply those parameters for you, so that you don’t have to.
(Below Chrissy proposes another cmdlet called “Export-SqlTable” and not to steal from her thunder, but… alternatively there could be an optional parameter call -Table that accepted an array of tables to be exported. However, the problem with that is obvious, if you do that for tables you will need to add a parameter for stored procedures, views, etc…)
Import-SqlDatabase should obviously import a database from a .DACPAC file, but it should also be able to import the structure of a database from an existing SQL Server database, and maybe even a SSDS Project. If Importing directly from an existing SQL Server database you should probably have to pipe output of Export-SqlDatabase to Import-SqlDatabase. This cmdlet should include parameters such as -ServerInstance -Database -SourceFile -IncludeData.
Comparing databases is extremely important and should be supported from a cmdlet named something like Compare-SqlDatabase. When using this cmdlet there will need to be a ton of optional parameters. You need to be able to compare your database to another database, possibly on another SQL Server (obviously) but you will also need to be able to compare a database to a .DACPAC file and vice-versa. This cmdlet should be able to generate a drift report, which should output to the screen by default, but have an optional parameter -DriftFile that would allow you to save the results to disk. It should be possible to push the changes to the target database if a parameter is supplied (and the user doing so has the requisite permissions). If the user does deploy changes, there should be a report of what was changed.
Make it super similar to mysqldump. Especially now with the SQL Server on Linux thing, it’s gonna be a great idea to have a tool similar to mysqldump. As a MySQL user, I’ve wanted this functionality from SQL Server for years.
For cmdlet names, the SQLPS module uses Verb-SqlNoun, so the cmdlet names should be: Export-SqlDatabase, Export-SqlTable, Import-SqlDatabase, Import-SqlTable
1. Create an alias for Export-SqlDatabase and call it mssqldump
Set-Alias -Name mssqldump -Value Export-SqlDatabase
2. Get syntax close to mysqldump, so if no server specified, default to local. If no username specified, default to local (like sqlcmd)
mysqldump test > dump.sql
mysqldump –databases db1 db2 db3 > dump.sql
3. The data should be output by default, like mysqldump. If people only want the schema, they could do Export-SqlDatabaseSchema OR add a -SchemaOnly switch to Export-SqlDatabase / Table. This is the behavior of mysqldump. You’ll start to like it; I guarantee 😉
Again, this is just a starting point and I’m sure
the community YOU can contribute a lot more ideas than the few that I have laid out here.