Category: Uncategorized

Copy permissions from one user to another using dbatools

Often, there is a request to replicate permission to a new hire or team member using an existing user. Typically, I prefer to use Active directory groups manage permissions for team members.

However, if your SQL instance has native SQL user accounts, you cant easily maintain similar permissions among team members. Here is a way to do that using dbatools. Credit goes to Claudio Silva 

$sqlInstance = "SQLSERVER01"
$existingUser = "<srcUser>"
$newUser = "<newUser>"

$ExportedUser = Export-DbaUser -SqlInstance $sqlInstance -User $existingUser -PassThru

$NewUserPermissions = $ExportedUser -replace $($existingUser -replace '\\', '\\'), $newUser

# Copy the permission to the clipboard. Paste on your query editor and paste there.
$newUserPermissions | Set-Clipboard

Backup and Restore a SQL database with a two line script

Here is a simple method to backup and restore a database from Server A to Server B using dbatools. The script will backup DB1 from Server A and restore the backup onto Server B as DB2.

Please note that the SQL service account on both servers need to have access to the network shared folder in order for this to work.

Backup script:

Backup-DbaDatabase -sqlinstance ServerA -database DB1 -path \\NetworkShare\SQLBackup\ServerA -copyonly -compressbackup

Restore script:

Restore-DbaDatabase -SqlInstance ServerB -Path "\\NetworkShare\SQLBackup\ServerA\DB1_202201261123.bak" -UseDestinationDefaultDirectories -DatabaseName DB2 -ReplaceDbNameInFile

However, if you ever need to simply copy a database DB1 from Server A to Server B. You can use the below script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy one database
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Database DB1 -SharedPath \\NetworkShare\SQLBackup\ServerA

If you need to do a full-on migration of databases from Server A to Server B, here is the script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy all databases
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Alldatabases -SharedPath \\NetworkShare\SQLBackup\ServerA

Apache Spark and Databricks

What is Apache Spark?

  • An open-source big data platform for data science
  • Big Data includes massive data volume, streaming data, unstructured and semi-structured data, images, video, sound.
  • There is no IDE, you need bring your own tools
  • It is a query/data analytics engine, it is meant to run queries
  • It is NOT a storage engine. One would store data in a storage layer like an S3, DataLake, HDFS etc

What is Databricks?

  • Commercial product from the creators of Apache Spark
  • Complete development environment for Apache Spark
  • Numerous proprietary Spark enhancements
  • Ideal for Data Science team collaboration
  • Optimized for cloud, dont believe you can spin up on your own data center