Welcome back to the SQL Server Jenkins labs! In the first part of the labs we created a Jenkins job that restored a database based on historical data. This time, let’s try something very similar, but add some flavor to it.
We’re going to setup a job that copies a single database in its current state from one server to another. It is very similar to the previous lab exercise, except, this one will take care of much more things at once. Let’s add into the mix most of the activities that accompany the database restore, such as:
- Changing database owner after restore is completed;
- Cleaning up existing permissions on the restored database;
- Restoring previous permissions, in case the database is being replaced with a fresh copy.
On top of that, let’s make some of them optional, increasing the flexibility of the solution.
Writing the code
Once again, there is no reason not to use dbatools commands for this purpose, similarly how most of us wouldn’t want to reinvent the wheel. The scenario is going to be quite simple:
- Define parameters – connections, database names, paths, etc.
- Backup the source database to a network share with COPY ONLY
- Export permissions from the target database if needed
- Restore the target database
- Change database owner
- Remove all users from the database if needed
- Re-apply old permissions if needed
The restore process, when it comes to replacing an existing database, is a very delicate operation, so let’s make sure that the code would stop whenever an error is occurred. This will be achieved by using -EnableException switch of the dbatools functions, that will throw an error instead of a simple warning, which is a default behavior. Let’s also define $ErrorActionPreference = 'Stop' to ensure that the script is not executed any further once the error has occurred. Lastly, it would be a good idea to save existing permissions to the disk drive in case a manual intervention is required.
Most of the output will be hidden in the Verbose stream, which allows us to control the amount of output we want to see on our end. Just add a -Verbose switch, if you need to see more!
Here is the script we are going to use this time. It has slightly more action inside, but it still consists of the same building blocks, most of which are regular dbatools commands. Let’s name it Copy-Database.ps1:
[Cmdletbinding()]
Param (
[Parameter(Mandatory)]
[string]$SourceServer,
[Parameter(Mandatory)]
[string]$SourceDatabase,
[string]$TargetServer,
[Parameter(Mandatory)]
[string]$TargetDatabase,
[Parameter(Mandatory)]
[string]$BackupFilePath,
[string]$DBOwner,
[switch]$KeepPermissions,
[switch]$DropUsers
)
#Stop on any error by default
$previousErrorActionPreference = $ErrorActionPreference
$ErrorActionPreference = 'Stop'
#Temporary permissions will be stored here
$permissionsFile = ".\Permissions-$TargetDatabase.sql"
#If target server is not specified, it's assumed that it's going to be the same server
if (!$TargetServer) { $TargetServer = $SourceServer }
if (Test-Path $BackupFilePath) {
#Removing old temporary backup if it still exists for some reason
Write-Verbose "Removing old backup file $BackupFilePath"
Remove-Item $BackupFilePath
}
#Run copy-only backup
Write-Verbose "Initiating database backup`: $SourceServer.$SourceDatabase to $BackupFilePath"
$backup = Backup-DbaDatabase -SqlInstance $SourceServer -Database $SourceDatabase -BackupFileName $BackupFilePath -CopyOnly -CompressBackup -Checksum -EnableException
if (!$backup.BackupComplete) {
throw "Backup to $BackupFilePath was not completed successfully on $SourceServer.$SourceDatabase"
}
#Record and store permissions
if ($KeepPermissions -or $DropUsers) {
$permissions = Export-DbaUser -SqlInstance $TargetServer -Database $TargetDatabase
Write-Verbose "Exported permissions from $TargetServer.$TargetDatabase`: $permissions"
Write-Verbose "Storing permissions of $TargetServer.$TargetDatabase in a file $permissionsFile"
$permissions | Out-File -FilePath $permissionsFile
}
#Restore the database on top of existing DB
Write-Verbose "Initiating database restore`: $BackupFilePath to $TargetServer.$TargetDatabase"
$backup | Restore-DbaDatabase -SqlInstance $TargetServer -DatabaseName $TargetDatabase -WithReplace -ReplaceDbNameInFile -EnableException
#Update database owner, stop throwing errors at this point, as further actions are not critical
$ErrorActionPreference = $previousErrorActionPreference
if ($DBOwner) {
Write-Verbose "Updating database owner to $DBOwner"
Set-DbaDatabaseOwner -SqlInstance $TargetServer -Database $TargetDatabase -TargetLogin $DBOwner
}
#Drop users if requested
if ($DropUsers) {
$users = Get-DbaDatabaseUser -SqlInstance $TargetServer -Database $TargetDatabase -ExcludeSystemUser
foreach ($user in $users) {
Write-Verbose "Dropping user $($user.Name) from $TargetServer.$TargetDatabase"
try {
$user.Drop()
}
catch {
# No need to throw, maybe a user owns a schema of its own
Write-Warning -Message $_
}
}
}
#Restore permissions
if ($KeepPermissions) {
Write-Verbose "Restoring permissions of $TargetServer.$TargetDatabase from a file $permissionsFile"
Invoke-DbaSqlQuery -SqlInstance $TargetServer -Database $TargetDatabase -Query $permissions
}
#Remove backup file
if (Test-Path $BackupFilePath) {
Write-Verbose "Removing backup file $BackupFilePath"
Remove-Item $BackupFilePath
}
#Remove permissions file
if (Test-Path $permissionsFile) {
Write-Verbose "Removing permissions file $permissionsFile"
Remove-Item $permissionsFile
}
Creating a new Jenkins job
Configuration check
Let’s re-iterate on this and ensure that our Jenkins instance has all necessary tools to implement the job we have in mind.
Plugins list
The following Jenkins plugins are going to be used in this job:
Agent setup
Ensure that the Jenkins executor(s), which you intend to use, runs under an account that was granted with sysadmin privileges on both source and target SQL Servers. Make sure to confine the job to that particular executor(s) using labels.
Also let’s ensure that executor node(s) has the latest version of dbatools installed:
Install-Module dbatools
Finally, let’s copy our Copy-Database.ps1 script to the local folder using the same exact path across all the Jenkins nodes, and ensure that job executor has r/w access to that folder. If you want to use a remote folder (file share), make sure that your Execution Policy is set to RemoteSigned on each node: Set-ExecutionPolicy -ExecutionPolicy RemoteSigned.
For advanced Jenkins users I would recommend using a Source Control Management repository to store your code and retrieve it using the SCM section in the job definition.
Creating the job
Finally, it’s time to create the job! Start with clicking ‘New’ item on the left-hand side of the Jenkins window:
Input a new project name and make sure to choose a Freestyle project, which is a most generic Jenkins job type:
In the job configuration window, set the project to be parameterized (click the corresponding checkbox). Add 4xString parameters (server and database names for both source and destination), and 2xBoolean parameters (those will represent our optional switches):
Parameter name of each of the added parameters will become an environment variable, so make sure to not to use special characters in the parameter name.
Provide a descriptive definition for the parameters:
- String parameter 1
- Name:Â SourceServer
- Default value:
- Description:Â Full server name of the source instance
- Trim the string: Yes
- String parameter 2
- Name: SourceDatabase
- Default value:
- Description: Name of the source database to copy
- Trim the string: Yes
- String parameter 3
- Name: TargetServer
- Default value:
- Description: Full server name of the target instance
- Trim the string: Yes
- String parameter 4
- Name: TargetDatabase
- Default value:
- Description: Name of the target database. Will be replaced, if exists.
- Trim the string: Yes
- Boolean parameter 1
- Name: KeepPermissions
- Default value: Unchecked
- Description: Check if you want to re-apply all the previously existed permissions on top of the restored database
- Boolean parameter 2
- Name: DropUsers
- Default value: Unchecked
- Description: Check if you want to drop all the users from the target database upon restore.
If you want this project to run only on specific agent nodes, click Restrict where this project can be run and provide space-separated list of labels in the field:
Now we have our job parameterized and running only on specific nodes, the only thing left is to pass those parameters to the Powershell script. In the Build section click Add build step and select Windows PowerShell:
In the Command section let’s put a call to our script file, that should be already accessible for the agent nodes, and pass all the parameters from your job:
& D:\Scripts\Copy-Database.ps1 -SourceServer $env:SourceServer `
-TargetServer $env:TargetServer `
-SourceDatabase $env:SourceDatabase `
-TargetDatabase $env:TargetDatabase `
-KeepPermissions:([bool]::Parse($env:KeepPermissions)) `
-DropUsers:([bool]::Parse($env:DropUsers)) `
-BackupFilePath "\\Some\Path\$($env:SourceDatabase).bak" `
-DBOwner sa `
-Verbose
Several things to note about this script:
$env:ServerName is a call for an environment variable, that was defined by the job parameter.[bool]::Parse($env:KeepPermissions) is used to convert the job’s parameter to [bool]. All job parameter are strings in essence and boolean parameters will have one of the following values: ‘true‘ or ‘false‘. We’re simply converting them to [bool]Â using a public method Parse of the [bool] class.-DropUsers:([bool]::Parse($env:DropUsers)) is used to pass a boolean value to a switch parameter, which requires a colon notation.
Save the job by pressing the Save button on the bottom of the screen.
 Running the job
Let’s start the job now! Click Build with Parameters from the job overview screen:
Input all the parameters and start the job:
In the Build History section of the page, you will see the progress of the job. By clicking on it you would be able to see the details, such as Console Output:
Conclusion
There are multiple ways of introducing automation to your environment, and Powershell is definitely one of them, yet, sometimes it’s hard for people to embrace automation without a simple UI that would help them to do the first step.
Jenkins is one of such tools that enables anyone to use automated solutions with little to no knowledge of the code that is executed behind the curtain. It can be just a frontend for your Powershell scripts, or it can become an ultimate tool in your hands that controls multiple aspects of your day-to-day tasks, and is integrated with your whole enterprise.
Cheers! And stay tuned – there is more to come!
nvarscar[ ] :: dbops blog 