#100DaysOfCode - Day 6: DBATools (PowerShell)

#100DaysOfCode - Day 6: DBATools (PowerShell)

1 min

In looking to improve the workflow for cloning remote databases locally, I got to leverage some of my powershell skillz (sorry) to sort of iron out that process. In doing some research on the topic, I found a great open-source library called dbatools, which seems to be quite the right fit for what I’m looking for.

PowerShell is an incredibly flexible language, and having such portable scripts that are able to do a lot of the heavy lifting for databases can be really practical. In working with databases, security is especially a difficult point, so I’ve really enjoyed being able to use Powershell where you can store a secure string in a persistent variable (e.g. by running $cred = get-credential, entering the password, and using those fields in scripts via $cred.UserName and $cred.Password). This tool is also quite useful for connection strings, which you can store in the .Password field and easily access securely later.

From all the functions available, I’ve found that I particularly like the following:

  • Connect-DbaInstance: connect to, and store the connection to, a sql server or database, local or remotely.
  • Backup-DbaDatabase: backup a database to a fileshare accessible by the SQL server (you can use webpages, such as web blobs).
  • Start-DbaMigration: copy a sql server and all it’s databases (or just a database, dependinging what your connection is to) into another database, using either a Backup/Restore procedure (non-disruptive), or a Detach/reattach (disruptive)
  • Copy-DbaDatabase: copy a specific database list

~ Moxnr

Written on October 28, 2020