Backup and Restore Users on a Remote SQL Database with T-SQL and PowerShell

I’ve added a lot of skills to my tech toolbox over the years, and I’ve found PowerShell to be the most useful addition. As my skills with PowerShell increased, I’ve looked to use it more. That’s a great thing, because PowerShell is so prolific on Microsoft systems today, that in many instances it’s become the primary way to perform administration.

Using PowerShell or T-SQL?

When I began to work with SQL Server, I went through a predictable pattern. First, I became familiar with SQL Server Management Studio. After learning how to perform basic operations, I looked to PowerShell to see what I could do more efficiently.

PowerShell in SQL Server works a little differently than many other systems. This is mostly due to the already well-established language used to script SQL databases: Transact-SQL (T-SQL). So although PowerShell is great for automation and scripting, there’s also a handoff between PowerShell and T-SQL for performing database-related tasks.

Back up and restore user accounts on a database

Backing up and restoring user accounts on a database is one of those useful tasks in which T-SQL and PowerShell can help. I’m going to share a couple of scripts that help automate the process of copying users from one database and putting them onto another.

First, Backup-iLPDatabaseUsers evaluates the users of a database, saving them as a SQL creation script that can be used against any other database. The SQL script is then ready to be used to create the SQL logins, the database users, and the role assignments.

The backup script uses just a few simple parameters: name, SQLInstance, and path.

The SQL instance is in the form “SERVERNAMEINSTANCENAME,PORT#”, but you don’t need to include the port number if you have SQL set up on the standard port. The SQLInstance parameter uses the SQLEXPRESS instance on the local computer if you don’t specify an instance.

The name parameter is just the name of the database, and it cannot be left blank.

The path parameter is the location where you want the SQL file saved. If there’s already a file, then this will overwrite it. If you don’t specify a filename, then it will save it to your desktop with the name of the database, plus today’s date.

The only output of this script is the path to the file it creates. Since the path is output, you can save that pathname by invoking it like this:

function Backup-iLPDatabaseUsers {
  [CmdletBinding()]
  Param (
    [Alias("Instance")]
    [String]$SQLInstance = "$($env:COMPUTERNAME)SQLEXPRESS",

    [Parameter(Mandatory=$true)]
    [String]$Name,

    [String]$Path = "$($env:USERPROFILE)DESKTOP$($Name)-$(Get-Date -Format yyyy-MM-dd).sql"        
  )

Begin { 
  Import-Module sqlps -DisableNameChecking
  New-Item -Path $Path -ItemType File -Force | Out-Null
} 

Process {
  #Create a connection to the SQL Instance
  $Smo = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLInstance

  #Retrieve the database object
  $database = $smo.Databases | Where Name -eq $Name

  #Get the roles on the database
  $rolesToProcess = $database.Roles | Where Name -ne "Public"


    
  $rolesToProcess | Foreach-Object {
    $thisRole = $_.Name

    #Get the members for this role
    $members = $_.EnumMembers() | Where {$_ -ne "dbo"}
    
    #Add each member to the user creation script
    $members | Foreach-Object {
      $thisMember = $_    

$CreateScript= @"
USE [master]
GO
IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = '$thisMember')
BEGIN

Create Login [$thisMember] from windows 
END 

USE [TargetDBName]
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = '$thisMember') 
BEGIN

CREATE USER [$thisMember] FOR LOGIN [$thisMember]
END

EXEC sys.sp_addrolemember @rolename = N'$thisRole', @membername = N'$thisMember'
"@

        $CreateScript | Out-File -FilePath $Path -Append

        } #End MEMBER Foreach
    } #End ROLE Foreach
} #End PROCESS
} #End FUNCTION

Now you’ve got a very nice SQL script that can be used to process the user creation on a database. All you need to do now is restore with it!

Restore-iLPDatabaseUsers takes the user creation SQL script that was generated during the backup and applies it to the new database. It has parameters for the name of the database and the name of the SQL instance, and just like the Backup script, it uses the same default values for the parameters.

function Restore-iLPDatabaseUsers {
  [CmdletBinding()]

  Param (
    [Alias("Computername","Instance")]
    [String]$SQLInstance = "LOCALHOSTSQLEXPRESS",

    [Parameter(Mandatory=$true)]
    [String]$Name,

    [Parameter(ValueFromPipeline=$true)]
    [String]$Path = "$env:USERPROFILEDESKTOP$Name-$(Get-Date -Format yyyy-MM-dd).sql"        
  )

Begin {Import-Module sqlps -DisableNameChecking -Force}

Process {
  #Load the file
  $Query = Get-Content $Path -Raw

  #Set the name of the target DB
  $Query = $Query.Replace("TargetDBName",$Name)

  #Create the users
  invoke-sqlcmd -Query $Query -serverInstance $SQLInstance
} #END Process

} #END Function 

When I first wrote these scripts, I found that I didn’t like specifying the script location for the restore function. To make that easier, I did two things: I made the backup function output the file location, and I made the restore function accept the file location from the pipeline.

This made it easier to backup and restore.

$myFile = Backup-iLPDatabaseUsers–Name "Source"
Restore-iLPDatabaseUsers –Name "Target" –Path $myFile

And you can even use the pipeline to simplify it further.

Backup-iLPDatabaseUsers –Name "Source" | Restore-iLPDatabaseUsers –Name "Target"

Even if you’re using the database backup and restore in this way, with the functions seemingly going from the source database directly to the target, a creation script is still being created. This may come in handy if you want to keep a point-in-time snapshot of the users on the database or if you later decide to copy those settings onto even more databases.

It can be adapted in many different ways, so let me know how you used it and how it worked for you.

Related Article:

• How to Create a Simple Backup Job in SQL Server