Skip to content

SQLServerHelper

Description

PowerShell module for managing SQL Server instances, databases, and related assets.

GitLab Repository

Features

  • Manage SQL Server databases, including creation, migration, backup, and restoration.
  • Handle database security, including user and role management.
  • Automate Active Directory group creation and assignment for SQL databases.
  • Integrate with external tools like Passwordstate and Infoblox for credential and DNS management.
  • Support for Always-On Availability Groups and contained databases.

Functions

Database Management

  • New-Database: Create a new SQL database with custom roles, users, and extended properties.
  • Remove-Database: Remove a SQL database and clean up associated assets like AD groups and DNS aliases.
  • Copy-SqlServerDatabase: Perform a backup and restore of a SQL Server database with user and permission management.
  • Invoke-BackupSQLDB: Backup a SQL Server database to a specified location.
  • Invoke-BackupRestoreSQLDB: Perform a backup and restore operation for a SQL Server database.
  • Restore-SqlBackupFromShare: Restore a SQL Server database from a shared backup location.

Security Management

  • Get-DatabaseSecurity: Retrieve the security settings of a SQL Server database.
  • Restore-DatabaseSecurity: Restore security settings for a SQL Server database.
  • Reset-DatabasePermissions: Restore default database permissions after a restore.
  • New-SQLReadUser: Create a new SQL Read-Only user in a specified database.
  • New-D09SQLUser: Create a new SQL user with specified permissions (Read, Write, or Admin).
  • New-SQLSecGroup: Create a new SQL Security Group and assign it to a database.
  • New-BISQLSecGroup: Create a new SQL Security Group for databases on BI servers and assign it to a database.

Migration

  • Invoke-SQLMigrateDB: Migrate a database between SQL servers, including user and DNS alias migration.

Extended Properties

  • Add-ExtendedProperty: Add or update an extended property in a SQL Server database.

Validation

  • Test-SQLDatabaseExist: Test if a SQL Server database exists on a specified server instance.

Dependencies

  • dbatools: Required for advanced SQL Server management tasks such as backups, restores, migrations, and security management.
  • ActiveDirectory module: Required for managing Active Directory groups and users.
  • Passwordstate API: Required for storing and retrieving credentials securely.
  • Infoblox API: Required for managing DNS aliases associated with SQL Server databases.

Changelog

  • 4/10/2018: Initial release of the SQLServerHelper module.
  • 1/04/2025: Updated functions and added documentation.