dbatools.io = command-line SQL Server Management Studio - Export-Import
This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.
Script it out
The SSMS offers to script out lots of the SQL Server objects, however it can be limited in some areas. Using Get-Dba* commands and piping them into Export-DbaScript may add few more options. For example SQL Agent jobs.
Export-DbaScript
# create a job
New-DbaAgentJob -SqlInstance $server -Job ScriptMeOutBeforeYouGoGo -Description StuckInMyHead
New-DbaAgentJobStep -SqlInstance $server -Job ScriptMeOutBeforeYouGoGo -StepName OneStepCloser -Subsystem TransactSql -Command "SELECT 1"
# script out the job and save to file
Get-DbaAgentJob -SqlInstance $server -Job ScriptMeOutBeforeYouGoGo | Export-DbaScript -FilePath MikeyScriptsThings.sql
# want to script out a single step? can you do it in SSMS?
Get-DbaAgentJobStep -SqlInstance $server -Job ScriptMeOutBeforeYouGoGo | Export-DbaScript -FilePath MikeyScriptsThingsStepByStep.sql
Exported script will contain this piece, so it is clear where it is coming from:
/*
Created by COMPUTER\Mikey using dbatools Export-DbaScript for objects on localhost,1433 at 09/01/2020 02:45:34
See https://dbatools.io/Export-DbaScript for more information
*/
BEGIN TRANSACTION
...
..
.
In a similar way you can export multiple objects from the server to the file – an alternative to Object Explorer Details in SSMS.
Get-DbaDbStoredProcedure
# export all user sprocs
Get-DbaDbStoredProcedure -SqlInstance $server -Database master -ExcludeSystemSp| Export-DbaScript -FilePath MikeyScriptsLittleSprocsToo.sql
Export logins/users
These functions are powerful. Exporting users with all the role membership or logins with users in the databases is easy. I do not think it is that simple in SSMS.
Export-DbaUser
# export user with role memberships
Export-DbaUser -SqlInstance $server -Database master -User MikeyHasBeenHere -FilePath MikeyHasBeenHere.sql
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MikeyHasBeenHere')
CREATE USER [MikeyHasBeenHere] FOR LOGIN [MikeyHasBeenHere] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [MikeyHasBeenHere]
GO
GRANT CONNECT TO [MikeyHasBeenHere] AS [dbo];
GO
Exporting logins is even more impressive.
Export-DbaLogin
# even better, export login with all the users
Export-DbaLogin -SqlInstance $server -Login MikeyHasBeenHere -FilePath MikeyHasBeenThere.sql
/*
Created by DESKTOP-VDRVEN3\Mikey using dbatools Export-DbaLogin for objects on localhost,1433 at 2020-09-01 23:45:07.664
See https://dbatools.io/Export-DbaLogin for more information
*/
USE master
GO
IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'MikeyHasBeenHere') CREATE LOGIN [MikeyHasBeenHere] WITH PASSWORD = 0x02001FCDA0D223FE7279CF5D41A842DB240DA4D036A1F5908E0C4E73A447EAB4DC8C4C147A63CB4DA5F789D762DA6D3428DAABCA1E4224DDC7BFD38272A82342E6BAF0009E8E HASHED, SID = 0x24594378502A424D9732B93BD92729C5, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF, DEFAULT_LANGUAGE = [us_english]
GO
USE master
GO
Grant CONNECT SQL TO [MikeyHasBeenHere] AS [sa]
GO
USE [master]
GO
CREATE USER [MikeyHasBeenHere] FOR LOGIN [MikeyHasBeenHere] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [MikeyHasBeenHere]
GO
Grant CONNECT TO [MikeyHasBeenHere] AS [dbo]
GO
Manage the data-tier application/DACPAC
There are scenarios where one would like to use DACPACs, so either use SSMS wizards or handy dbatools commands.
Export-DbaDacPackage
# export DacPac
Export-DbaDacPackage -SqlInstance $server2 -Database Imported -AllUserDatabases -Path MyDacPac
<#
Database : Imported
Elapsed : 14.54 s
Path : C:\Users\micha\MyDacPac\localhost,14333-20200902112209-dacpackage.dacpac
Result : Extracting schema (Start)
Gathering database credentials
Gathering database options
Gathering generic database scoped configuration option
Gathering users
Gathering roles
Gathering application roles
Gathering role memberships
Gathering filegroups
Gathering full-text catalogs
Gathering assemblies
Gathering certificates
Gathering asymmetric keys
Gathering symmetric keys
Gathering encrypted symmetric keys
Gathering schemas
Gathering XML schema collections
Gathering user-defined data types
Gathering user-defined types
Gathering table types
Gathering unique keys for table types
Gathering primary keys for table types
Gathering indexes for table types
Gathering check constraints for table types
Gathering default constraints for table types
Gathering partition functions
Gathering partition schemes
Gathering functions
Gathering encrypted functions
Gathering aggregates
Gathering procedures
Gathering encrypted procedures
Gathering tables
Gathering primary keys
Gathering unique constraints
Gathering foreign keys
Gathering default constraints
Gathering check constraints
Gathering views
Gathering encrypted views
Gathering indexes
Gathering statistics
Gathering full-text index stoplists
Gathering search property lists
Gathering search properties
Gathering full-text indexes
Gathering column store indexes
Gathering spatial indexes
Gathering XML indexes
Gathering Selective XML indexes
Gathering encrypted triggers
Gathering triggers
Gathering encrypted and clr ddl triggers
Gathering ddl triggers
Gathering synonyms
Gathering defaults
Gathering data constraint uddt bindings
Gathering rules
Gathering message types
Gathering queues
Gathering contracts
Gathering services
Gathering event notifications
Gathering remote service bindings
Gathering broker priorities
Gathering signatures
Gathering data compression options
Gathering sequence types
Gathering security policies
Gather column encryption keys
Gathering column master keys
Gathering external data sources
Gathering external file formats
Gathering external tables
Gathering extended properties
Gathering credentials
Gathering logins
Gathering server audits
Extracting schema (Complete)
SqlInstance : 37f6c33a9599
#>
Publish-DbaDacPackage
# publish DacPac to the database
Publish-DbaDacPackage -SqlInstance $server2 -Path 'C:\Temp\MyDacPac\localhost,14333-20200902112209-dacpackage.dacpac' -Database Imported
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : localhost:14333
Database : Imported
Dacpac : C:\Temp\MyDacPac\localhost,14333-20200902112209-dacpackage.dacpac
PublishXml :
Result : Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Reporting and scripting deployment plan (Complete)
Reporting and scripting deployment plan (Start)
Updating database (Start)
Creating [dbo].[syscategories]...
Update complete.
Updating database (Complete)
DeployOptions : @{AdditionalDeploymentContributorPaths=; AdditionalDeploymentContributors=; AdditionalDeploymentContributorArguments=;
AllowDropBlockingAssemblies=False; AllowIncompatiblePlatform=False; BackupDatabaseBeforeChanges=False; BlockOnPossibleDataLoss=True;
BlockWhenDriftDetected=True; CommandTimeout=60; LongRunningCommandTimeout=0; DatabaseLockTimeout=60; CommentOutSetVarDeclarations=False;
CompareUsingTargetCollation=False; CreateNewDatabase=False; DatabaseSpecification=Microsoft.SqlServer.Dac.DacAzureDatabaseSpecification;
DeployDatabaseInSingleUserMode=False; DisableAndReenableDdlTriggers=True; DoNotAlterChangeDataCaptureObjects=True;
DoNotAlterReplicatedObjects=True; DoNotDropObjectTypes=; DropConstraintsNotInSource=True; DropDmlTriggersNotInSource=True;
DropExtendedPropertiesNotInSource=True; DropIndexesNotInSource=True; DropObjectsNotInSource=False; DropPermissionsNotInSource=False;
DropRoleMembersNotInSource=False; DropStatisticsNotInSource=True; GenerateSmartDefaults=False; IgnoreAnsiNulls=True;
IgnoreAuthorizer=False; IgnoreColumnCollation=False; IgnoreColumnOrder=False; IgnoreComments=False;
IgnoreCryptographicProviderFilePath=True; IgnoreDdlTriggerOrder=False; IgnoreDdlTriggerState=False; IgnoreDefaultSchema=False;
IgnoreDmlTriggerOrder=False; IgnoreDmlTriggerState=False; IgnoreExtendedProperties=False; IgnoreFileAndLogFilePath=True;
IgnoreFilegroupPlacement=True; IgnoreFileSize=True; IgnoreFillFactor=True; IgnoreFullTextCatalogFilePath=True; IgnoreIdentitySeed=False;
IgnoreIncrement=False; IgnoreIndexOptions=False; IgnoreIndexPadding=True; IgnoreKeywordCasing=True; IgnoreLockHintsOnIndexes=False;
IgnoreLoginSids=True; ExcludeObjectTypes=; IgnoreNotForReplication=False; IgnoreObjectPlacementOnPartitionScheme=True;
IgnorePartitionSchemes=False; IgnoreTablePartitionOptions=False; IgnorePermissions=False; IgnoreQuotedIdentifiers=True;
IgnoreRoleMembership=False; IgnoreRouteLifetime=True; IgnoreSemicolonBetweenStatements=True; IgnoreTableOptions=False;
IgnoreUserSettingsObjects=False; IgnoreWhitespace=True; IgnoreWithNocheckOnCheckConstraints=False; IgnoreWithNocheckOnForeignKeys=False;
AllowUnsafeRowLevelSecurityDataMovement=False; IncludeCompositeObjects=False; IncludeTransactionalScripts=False;
NoAlterStatementsToChangeClrTypes=False; PopulateFilesOnFileGroups=True; RegisterDataTierApplication=False;
RunDeploymentPlanExecutors=False; ScriptDatabaseCollation=False; ScriptDatabaseCompatibility=False; ScriptDatabaseOptions=True;
ScriptDeployStateChecks=False; ScriptFileSize=False; ScriptNewConstraintValidation=True; ScriptRefreshModule=True;
TreatVerificationErrorsAsWarnings=False; UnmodifiableObjectWarnings=True; VerifyCollationCompatibility=True; VerifyDeployment=True}
SqlCmdVariableValues : {}
#>
Crème de la crème – export everything
Imagine you would like to script out the whole instance, what would be the best way to do it in the SSMS?
Export-DbaInstance
# one of the best one-liners
Export-DbaInstance -SqlInstance $server -Path MySQLOops
<#
Directory: C:\Users\micha\MySQLOops\localhost,1433-09012020004734
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 02/09/2020 10:47 3844 1-sp_configure.sql
-a---- 02/09/2020 10:47 540 2-serverroles.sql
-a---- 02/09/2020 10:47 1365 3-dbmail.sql
-a---- 02/09/2020 10:47 0 4-databases.sql
-a---- 02/09/2020 10:47 584 5-logins.sql
-a---- 02/09/2020 10:47 458 6-resourcegov.sql
-a---- 02/09/2020 10:47 11968 7-extendedevents.sql
-a---- 02/09/2020 10:47 4338 8-sqlagent.sql
-a---- 02/09/2020 10:47 680 9-userobjectsinsysdbs.sql
#>
Import/Export data
SQL Server objects are not the only thing you can export or import. Every now and then DBA would need to shuffle some data. For this reason the Import and Export Wizard can be used or dbatools commands.
Copy-DbaDbTableData
# copy data between SQL Server instances
Copy-DbaDbTableData -SqlInstance $server -Database msdb -Table syscategories -Destination $server2 -DestinationDatabase model -AutoCreateTable
<#
SourceInstance : localhost,1433
SourceDatabase : msdb
SourceSchema : dbo
SourceTable : syscategories
DestinationInstance : localhost,14333
DestinationDatabase : model
DestinationSchema : dbo
DestinationTable : syscategories
RowsCopied : 21
Elapsed : 311.11 ms
#>
Export-DbaDbTableData
# export table as a collection of INSERT INTO statements
Get-DbaDbTable -SqlInstance $server -Database msdb -Table syscategories | Export-DbaDbTableData -FilePath MikeyDoesData.sql
Import-DbaCsv
# import CSV file into SQL Server instance
Import-DbaCsv -Path 'pigeon-racing.csv' -SqlInstance $server2 -Database imported -AutoCreateTable
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : 37f6c33a9599
Database : imported
Table : pigeon-racing
Schema : dbo
RowsCopied : 400
Elapsed : 622.27 ms
RowsPerSecond : 648
Path : C:\Users\micha\pigeon-racing.csv
#>
# import multiple CSV files at once
Get-ChildItem -Path . -Filter *CSV* | Import-DbaCsv -SqlInstance $server2 -Database imported -AutoCreateTable
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : 37f6c33a9599
Database : imported
Table : chopstick-effectiveness
Schema : dbo
RowsCopied : 186
Elapsed : 228.21 ms
RowsPerSecond : 817
Path : C:\Users\micha\chopstick-effectiveness.csv
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : 37f6c33a9599
Database : imported
Table : pigeon-racing
Schema : dbo
RowsCopied : 400
Elapsed : 239.08 ms
RowsPerSecond : 1678
Path : C:\Users\micha\pigeon-racing.csv
#>
Being able to import and export objects or data from SQL Server in a simple way makes life much easier.
Thank you,
Mikey
