dbatools – Page 3 – Database Blog

Copy databases from One SQL instance to another -dbatools

Back with another quick tip to copy databases from one sql instance to another using dbatools one-line command script:

Copy-DbaDatabase -Source SQLSERVER01 -Destination SQLSERVER02 -Database STAGE_DB_Name -BackupRestore -SharedPath \\NetworkPath\SQLBackup\SQLSERVER01 -NewName PROD_DB_Name

-Source: Name of the source SQL server

-Destination: Name of the destination SQL server where you are going to restore the source DB

-BackupRestore: Will use a backup and restore methodology to copy over the database

-SharedPath: This network path should be accessible to both source and destination servers

-NewName: Use this if you want to restore the database with a different name from source

Update Service accounts for SQL server services using dbatools

Recently, I found a neat little trick using dbatools to update service accounts for SQL Server service and agent. It is generally a good security practice to run the services under a dedicated domain account. The default local system account has more privileges than necessary. Great thing about this method is that it is similar to updating service account using SQL Server Configuration Manager. The process will grant all the minimum necessary permissions to the service account and you don’t need to do a thing!

$cred = Get-Credential domain\serviceaccount
Get-DbaService -ComputerName sqlserver01 | Out-GridView -Passthru | Update-DbaServiceAccount -ServiceCredential $cred

Soon after I ran this, I got an error that the account could not be set. I was able to revert the account back to local system account and start back the services. Now I went onto investigate the root cause for the error. I also ensured that the account is not locked out, password is accurate. Finally I looked at the System logs under event view logs on the server. Bingo, now we are getting somewhere. This is the error I saw:

The MSSQLSERVER service was unable to log on as domain\ServiceAccount with the currently configured password due to the following error: 
Logon failure: the user has not been granted the requested logon type at this computer.
 
Service: MSSQLSERVER 
Domain and account: domain\ServiceAccount
 
This service account does not have the required user right "Log on as a service."
 
User Action
 
Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.
 
If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.

I opened the security policy on the server (run secpol.msc) and found that the account was not present in the User policy named ” Log on as a service “. After talking to the systems engineer, I found out that the server is managed through a GPO. We had them update the GPO to include the service account in the policy. Once the GPO took effect, we were able to run the above dbatools command and set the service account successfully.

Obviously, this process of setting service account also requires that you restart the services in order for the change to take effect. Hope you enjoyed this!

Importing from a CSV to a database table

A common request that i get frequently is to load contents of CSV into the database. Until i came across dbatools, I used to perform this operation using GUI.

dbatools offers two ways to do this:

Import-DbaCsv
Import-Csv and Write-DbaDataTable

Which one should you choose? It is a personal choice, I have found that Import-DbaCsv is better choice for larger CSVs because it is optimized to keep memory usage low.

When you use Import-DbaCsv, it’s using some kind of streaming methodology to move the data efficiently between the disk and the SQL Server.

The output from the command shows table information, the number of rows copied and even how quickly it copied them. This is useful when you are testing a script with a smaller set of data as you can extrapolate the time it will take to load the data in your production environment.

Get-ChildItem -Path C:\temp\data.csv | Import-DbaCsv -SqlInstance $sqlinstance1 -Database StagingDB -Table data1

ComputerName  : SQLDEV01
InstanceName  : MSSQLSERVER
SqlInstance   : SQLDEV01
Database      : StagingDB
Table         : data1
Schema        : dbo
RowsCopied    : 2450
Elapsed       : 55.16 ms
RowsPerSecond : 44663
Path          : C:\temp\data.csv

The beauty of the command is that you can even import multiple CSVs at once! You can do so using the command shown below:

Get-ChildItem C:\temp\data*.csv | Import-DbaCsv -SqlInstance sql2017 -Database StagingDB -AutoCreateTable

ComputerName  : SQLDEV01
InstanceName  : MSSQLSERVER
SqlInstance   : SQLDEV01
Database      : StagingDB
Table         : data-tracks1
Schema        : dbo
RowsCopied    : 2450
Elapsed       : 73.02 ms
RowsPerSecond : 33712
Path          : C:\temp\data-tracks1.csv

ComputerName  : SQLDEV01
InstanceName  : MSSQLSERVER
SqlInstance   : SQLDEV01
Database      : StagingDB
Table         : data-tracks2
Schema        : dbo
RowsCopied    : 1312
Elapsed       : 65.41 ms
RowsPerSecond : 20160
Path          : C:\temp\data-tracks2.csv

Using Import-Csv with Write-DbaDataTable

Import-Csv is a powerful command that turns the text within CSV files to objects. Generally, Import-Csv is piped right to Write-DbaDataTable

# Import the CSV and write to the database
Import-Csv -Path .\DatabaseServerinfo.csv | Write-DbaDataTable -SqlInstance $sqlinstance1 -Database StagingDB -Table Databases -AutoCreateTable

Piping to Write-DbaDataTable is convenient and extremely fast for small batches, but slows down for larger datasets (similar to SQL’s RBAR concept). If you intend to import a large dataset, use Import-DbaCsv or the following syntax instead:

Import-DbaCsv -Path \\NetworkPath\server\bigdataset.csv -SqlInstance sql2017 -Database StagingDB -Table NewTable -AutoCreateTable

Ports required to use dbatools – SQL Server

Protocol	Default port	Sample Command	Percentage of Commands
SQL Database Engine	1433	Get-DbaDatabase	62%
WS-Management	5985 or 5986	New-DbaClientAlias	25%
SQL WMI	135	Enable-DbaAgHadr	4%
SMB	139	Invoke-DbaDbLogShipping	4%
SMB over IP	445	Get-DbaPfDataCollectorCounterSample	<1%

Copy data between SQL server tables

For adhoc data copy requests, I have always used the SSMS GUI to copy data from one instance to another. That is until I have recently thought to use dbatools to replace my workflow. I am glad I came across the Copy-DbaTableData command. This replaces my workflow with a one line command. The more I use dbatools, I love the simplicity of it and the constant improvements that go into this open source tool.

Copy-DbaDbTableData -SqlInstance SQLSERVER01 -Destination SQLSERVER02 -Database DB1 -Table dbo.test_data -DestinationTable dbo.test_data1 -AutoCreateTable

AutoCreateTable – Allows to create a table on destination database if it does not exist already

DestinationTable – Allows you to give a different table name if you dont want to use the same name as the source table

Copy-DbaDbTableData -SqlInstance SQLSERVER01 -Destination SQLSERVER02 -Database DB1 -Table dbo.test_data -DestinationTable dbo.test_data1 -Truncate -keepIdentity

Use the truncate option if you would like to truncate the destination table before copying the rows.

Use the keepidentity option if you would like to retain the identity column values in the destination table.

Share this:

Share this:

Share this:

Share this:

Share this: