Restore All Databases from Backup Path Using DBATOOLS

I had a circumstance where I needed to restore 100’s of databases from a source server to a destination server, and we had to do it manually one by one, or manually build restore scripts for all databases using T-SQL scripts, or automate the process using PowerShell. I’m planned to use PowerShell to move the databases, however while searching, I came across DBATOOLS, which appears to be a quick and straightforward operation. To accomplish this, we must first install the DBATOOLS Module, and then update it if it is already installed. We used to get Database Refresh requests from Production to DEV/TEST on a regular basis as Database Administrators.

I installed and upgraded DBATOOLS on my machine, then I developed 3 databases to test migration from one instance to another. I already have database backup files in a folder to conduct our work, and now I’m testing to start restoring all 3 databases using DBATOOLS in PowerShell. To restore a database using DBATOOLS, we need to know the Restore command in PowerShell. We can check this in PowerShell using the Get-help *restore* command, which will display all of the available restore commands as well as a synopsis to help us figure out which restore command we can use to complete our task.

I use the command Restore-DbaDatabase from the DBATOOLS commands listed above. I’m only using this command since my backup files are already in a directory with Databases Full Backups, and I need to restore databases from these backup files.

In the above screenshot you can see 3 backup full files for test purpose, I use above backup path in our restore command to restore databases in my destination instance. Also I need to specify some parameters in Restore command like Destination Instance Name, backup Path, destination DATA Directory and Destination LOG Directory

Below is the Complete Restore command used in PowerShell :

You can see in the above Snippet that I ran the Restore command with the parameters passed in, and at the top you can see the restore database operation is in progress, as well as the percent completion of the restore is showing in  a Progress bar. Once the Database Restore is complete, it generates an Output that contains the information details. In the Output, we can see the Script Field, which contains the T-sql script used by DBATOOLS to restore databases. We may copy that T-sql script and use it for future restorations.

All 3 databases were successfully restored to my destination instance and the databases are ONLINE.

We may generate Restore Script for all Databases in a Backup path without restoring them to the Destination Instance by using the same command. We just need to add one parameter to the Restore command: -OutputScriptonly. Below is the Full Restore command :

Restore-DbaDatabase -SqlInstance DESKTOP-02JIB76\INS17 -Path 'E:\Backup\INS17' -DestinationDataDirectory 'D:\INS17\DATA' -DestinationLogDirectory 'D:\INS17\LOG' -WithReplace -OutputScriptOnly

When you execute above command it generates the Restore commands for all 3 databases and provides like below :


You can copy the Restore script to your SSMS and will looks like below :

Testing looks Good , I will use this in my current organization Environment .

Automate Migrating Databases From Source to Destination USing PowerShell

Using a combination of PowerShell and T-SQL scripting, create a simple solution for automating database migration across SQL Server versions. In my instance, I need to locate multiple databases that can be migrated using a standard procedure or at the very least a set of standard activities before proceeding to a set of customized actions. In these situations, you may want to create generic migration scripts and create a system to automate their execution. If you’re going to iterate through numerous test sessions and want to migrate a new copy before each one, a general technique like this can be extremely useful.

The activities that can be taken to make the migration process easier are as follows:

1) The backup database from the Source Instance will be transferred.
2) Restore the database that will be moved to the new instance.
3) Refresh the allocation usage.
4) Data purity can be used to check database consistency.
5) Set the level of page verification.
6) Set the database compatibility level to match the version of the destination instance (e.g. 120).
7) Statistics should be updated.
8) Remove any orphaned schemas (schemas automatically created for each database user to maintain compatibility but with no objects belonging to them).
9) Fix Orphaned Users if any exists.
10) Run Rebuild or re-organize all databases based on Migration Change window and the databases size.

Today, I’m automating the databases backup and restore of the first two Point. I still need to work on automating the other points, which are currently in process. I’m hoping that this backup and restore PowerShell script may be useful to some of the DBAs, and that I can share it now.

When you issue backup and restore commands in T-SQL, you’ll use handlers that are immediately called back by SMO, and you’ll provide a simple progress notification like the one provided by SSMS or sqlcmd. Please keep in mind that the completion handler just displays the error message.
Get-SQLInstance, DatabaseFullBackup, and RestoreDatabaseFromFullBackup are only a few of the functions in the Powershell script.

Below is the PowerShell script that will implement backup/restore procedures for each database which we want to migrate to complete the migration :

#region Load Assemblies and Global Error Handling
[System.Reflection.Assembly]::LoadWithPartialName( `
	"Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName( `
	"Microsoft.SqlServer.SMOExtended") | Out-Null;

# Simple global exception handling to see SQL Server errors
trap {
		$Exc = $_.Exception;
		while ( $Exc.InnerException )
		{
			$Exc = $Exc.InnerException;
			Write-Warning ("(generic trap) " + $Exc.Message);
		};
		break;
};
#endregion

#region Event Handlers
$PercentCompleteHandler = `
	[Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] `
	{ 
		Write-Host ([string]$_.Percent + " percent processed.");
	};
$CompleteHandler = `
	[Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] `
	{
		Write-Host $_.Error.Message;
	};
#endregion

#region Functions definition
function Get-SQLInstance($InstanceName, $Login, $Password)
{
	$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" `
							  $InstanceName;
	if ($Login -eq $null) {
		$SQLInstance.ConnectionContext.LoginSecure = $true;
	}
	else {
		$SQLInstance.ConnectionContext.LoginSecure = $false;
		$SQLInstance.ConnectionContext.Login = $Login;
		$SQLInstance.ConnectionContext.Password = $Password;
	};
	# Force connection to get an early error message
	$SQLInstance.ConnectionContext.Connect();
	return $SQLInstance;
};
function DatabaseFullBackup ($SQLInstance, $DatabaseName, $BackupPath)
{
	$Backup = New-Object "Microsoft.SqlServer.Management.Smo.Backup";
	$Backup.Action = "Database";
	$Backup.Database = $DatabaseName;
	$Backup.Initialize = $true;
	$Backup.CopyOnly = $true;
	$Backup.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak" `
								, "File");

	$Backup.add_PercentComplete($PercentCompleteHandler);
	$Backup.add_Complete($CompleteHandler);

	$Backup.SqlBackup($SQLInstance)
};
function RestoreDatabaseFromFullBackup ($DestinationSQLInstance, $DatabaseName `
								, $BackupPath, $DataFilesPath, $LogFilesPath)
{
	$Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore";
	$Restore.FileNumber = 1;
	$Restore.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak"`
								, "File");

	foreach ($File in $Restore.ReadFileList($DestinationSQLInstance))
	{
		$NewFile = New-Object "Microsoft.SqlServer.Management.Smo.relocatefile";
		$NewFile.LogicalFileName = $File.LogicalName;
		
		#Primary Data File
		if	($File.FileID -eq 1 -and $DataFilesPath -ne "")
		{	
			$NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName `
											+ "_" + $File.LogicalName + ".mdf");
		}
		#Secondary Data File
		elseif	($File.Type -eq "D" -and $DataFilesPath -ne "")
		{	   
			$NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName`
											+ "_" + $File.LogicalName + ".ndf");
		}
		#Log File
		elseif	($File.Type -eq "L" -and $LogFilesPath -ne "")
		{
			$NewFile.PhysicalFileName = ($LogFilesPath + "\" + $DatabaseName `
											+ "_" + $File.LogicalName + ".ldf");
		};

		if ($NewFile.PhysicalFileName -ne $null) {
			[void]$Restore.RelocateFiles.add($Newfile);
		};
	};
	
	$Restore.Database = $DatabaseName;
	$Restore.ReplaceDatabase = $true;
	$Restore.NoRecovery = $false;
	$Restore.Action = "Database";

	$Restore.add_PercentComplete($PercentCompleteHandler);
	$Restore.add_Complete($CompleteHandler);

	$Restore.SqlRestore($DestinationSQLInstance); 
};
#endregion

#region Main
#Parameters
$SourceSQLInstanceName = "DESKTOP-02JIB76";                    # Mention your Source Instance Name 
$DestinationSQLInstanceName = "DESKTOP-02JIB76\INS17";         # Mention your Destination InstanceName

$BackupPath = "E:\Backup\INS17\";                              # Mention your Backup Path which can be your Local Path or Shared Path

$DataFilesPath = "D:\INS17\DATA";                              # Mention your Destination DATA File Path
$LogFilesPath = "D:\INS17\LOG";                                # Mention your Destination LOG File Path

$DatabaseNames = "BLOB","panda","dbareports";                  # Mention your Databases Names which are needed to Migrate

#Main
$SourceSQLInstance = Get-SQLInstance $SourceSQLInstanceName;
$DestinationSQLInstance = Get-SQLInstance $DestinationSQLInstanceName;

foreach ($DatabaseName in $DatabaseNames)
{
	Write-Host ("`nBackup database [" + $DatabaseName + "]");
	DatabaseFullBackup 	$SourceSQLInstance $DatabaseName $BackupPath;

	Write-Host ("`nRestore database [" + $DatabaseName + "]");
	RestoreDatabaseFromFullBackup 	$DestinationSQLInstance $DatabaseName `
									$BackupPath $DataFilesPath $LogFilesPath;

};
#endregion

You’ll need to update a few parameters in the script above, such as SourceInstanceName, Destination Instance Name, Backup Path, DATA File Path, LOG File Path and the Databases Names .

Once all of the parameters have been adjusted to your environment, the script will begin taking database backups from the source instance and restoring them to destination instance.

You can see below snippet where I don;t have any backup files , any Data and Log files:

Also I don’t have any Databases in my Destination Instance.

Now I’m backing up and restoring my “BLOB”,”panda”,”dbareports” databases with the PowerShell Script above. When I run the script, it will back up the first database, restore it to the Destination Instance after the backup is complete, and then take backups for the second database, It will begin restoring after the backup has been completed.

You can see in above Screenshot when Panda Database backup completed immediately it start s to Restore Panda Database in Destination , once the restore completed it will start backup for next Database (dbareports database) and will restore

The script completed migration of databases which I mentioned the DatabasesField

All three databases have been successfully migrated from the Source Instance to the Destination Instance. Now all you have to do is run the script in PowerShell ISE and it will take care of migrating your provided databases.

Starting and Stopping All SQL Server services on a server with PowerShell

Suddenly, I received a request to shut down a number of SQL Server instances in order to relieve some load. A lot is defined as 60 or more instances spread across 30 servers.

Manually logging onto each server and stopping services is a massive chore, especially because some servers have numerous instances. So I decided to go with a powershell script to automate the process.

PowerShell has two important cmdlets: stop-service and start-service. I’m sure I don’t have to go into great detail about what each one does! They don’t, however, accept a remote computer option, and while you could use them in conjunction with the invoke-command cmdlet to perform true remoting, this isn’t available on many of our servers.

In addition, the script must shut down all SqL services, including the SQL Engine, SQL Agent, SSIS, SSAS, and SSRS, among others.

The PowerShell script below creates two functions: one to stop all SQL services and the other to start them all. However, it stops and starts all SQL services, but some of them throw an error; even then, the script does not stop; instead, it moves on to the next service to perform the requested operation.

PowerShell is used for automation and configuration management framework. As PowerShell is available for Linux platforms, you can subsequently use it to start and stop SQL Server services on both Windows and Linux computers. Keep in mind that PowerShell Core is still in beta, so the code below used may or may not work on Linux machines.

The Get-Service cmdlet can be used to view service status in PowerShell by giving the service name in the –Name parameter. The following is the syntax for viewing the default SQL Server instance’s service status:

Get-Service -Name MSSQLSERVER

We may use the Set-Service cmdlet with the following parameters to start or stop a service using PowerShell:

Set-Service -Name MSSQLSERVER -Status Running -PassThru

where -Name Parameter is Providing ‘InstanceName’, Status Parameter is for the service’s expected state We must provide Running for the –Status argument if we want to start the service and Stopped if we want it to stop, -PassThru Parameter is to display the Output of the given command in the console .

Function to Create Stop All SQL Related services on a Machine :

#Stops all SQL Server services on a specific server with this function.
# Stop-AllSQLServerServices Node1 
function Stop-AllSQLServerServices
{
    [cmdletbinding()]
    Param([string]$Server
    , [bool]$StopSQL=$true
    , [bool]$StopAgent=$true
    , [bool]$StopSSRS=$true
    , [bool]$StopBrowser=$true
    , [bool]$StopSSIS=$true
    , [bool]$StopTextDaemon=$true
    , [bool]$StopSSAS=$true)
 
    #Get all the services on the server
    $Services = get-service -ComputerName $Server
 
    if($StopAgent -eq $true)
    {
        #check the SQL Server Agent services
        write-verbose "Checking Agent Services"
 
        #get all named agent instances and the default instance
        ForEach ($SQLAgentService in $Services | where-object {$_.Name -match "SQLSERVERAGENT" -or $_.Name -like "SQLAgent$*"})
        {
            #check the servcie running status
            if($SQLAgentService.status -eq "Running")
            {
                #if stopped, start the agent
                write-verbose "Stopping SQL Server Agent $($SQLAgentService.Name)"
                $SQLAgentService.Stop()
            }
            else
            {
                #write comfort message that the service is already running
                write-verbose "SQL Agent Service $($SQLAgentService.Name) is already stopped."
            }
        }
    }
    else
    {
        write-verbose "Skipping checking Agent services"
    }
 
    if($StopSSRS -eq $true)
    {
        #check the SSRS services
        write-verbose "Checking SSRS Services"
 
        #get all reporting service services
        ForEach ($SSRSService in $Services | where-object {$_.Name -match "ReportServer"})
        {
            #check the status of the service
            if($SSRSService.status -eq "Running")
            {
                #if stopped, start the agent
                write-verbose "Stopping SSRS Service $($SSRSService.Name)"
                $SSRSService.Stop()
            }
            else
            {
                #write comfort message that the service is already running
                write-verbose "SSRS Service $($SSRSService.Name) is already stopped."
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SSRS services"
    }
 
    if($StopSSIS -eq $True)
    {
 
        #get the SSIS service (should only be one)
        write-verbose "Checking SSIS Service"
 
        #get all services, even though there should only be one
        ForEach ($SSISService in $Services | where-object {$_.Name -match "MsDtsServer*"})
        {
            #check the status of the service
            if($SSISService.Status -eq "Running")
            {
                #if its stopped, start it
                write-verbose "Stopping SSIS Service $($SSISService.Name)"
                $SSISService.Stop()
            }
            else
            {
                #write comfort message
                write-verbose "SSIS $($SSISService.Name) already stopped"
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SSIS services"
    }
 
    if ($StopBrowser -eq $true)
    {
 
        #Check the browser, start it if there are named instances on the box
        write-verbose "Checking SQL Browser service"
 
        #get the browser service
        $BrowserService = $services | where-object {$_.Name -eq "SQLBrowser"}
 
        if($BrowserService.Status -eq "Running")
        {
            #if its stopped start it
            write-verbose "Stopping Browser Server $($BrowserService.Name)"
            $BrowserService.Stop()
        }
        else
        {
            #write comfort message
            write-verbose "Browser service $($BrowserService.Name) is already stopped"
        }
    }
    else
    {
        write-verbose "Skipping checking Browser service"
    }
 
    if($StopTextDaemon -eq $True)
    {
 
        # Start the full text daemons
        write-verbose "Checking SQL Full Text Daemons"
 
        ForEach($TextService in $Services | where-object {$_.Name -match "MSSQLFDLauncher"})
        {
            #check the service status
            if ($TextService.Status -eq "Running")
            {
                #start the service
                write-verbose "Stopping Full Text Service $($TextService.Name)"
                $TextService.Stop()
            }
            else
            {
                write-verbose "Text service $($TextService.Name) is already stopped."
            }
        }
    }
    else
    {
        write-verbose "Skipping checking Text Daemon services"
    }
 
    if($StopSSAS -eq $True)
    {
 
        # start the SSAS service
        write-verbose "Checking SSAS services"
 
        ForEach($SSASService in $Services | where-object {$_.Name -match "MSSQLServerOLAP"})
        {
            #check the service status
            if ($SSASService.Status -eq "Running")
            {
                #start the service
                Write-verbose "Stopping SSAS Service $($SSASService.Name)"
                $SSASService.Stop()
            }
            else
            {
                write-verbose "SSAS Service $($SSASService.Name) is already stopped."
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SSAS services"
    }
 
     if($StopSQL -eq $true)
    {
        #check the SQL Server Engine services
        write-verbose "Checking SQL Server Engine Services"
 
        #get all named instances and the default instance
        foreach ($SQLService in $Services | where-object {$_.Name -match "MSSQLSERVER" -or $_.Name -like "MSSQL$*"})
        {
            #Check the service running status
            if($SQLService.status -eq "Running")
            {
                #if stopped start the SQL Server service
                write-verbose "Stoppin SQL Server Service $($SQLService.Name)"
                $SQLService.Stop()
            }
            else
            {
                #Write comfort message that the service is already running
                write-verbose "SQL Server Service $($SQLService.Name) is already stopped"
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SQL Engine services"
    }
 
}

I used PowerShell to run the Stop function, which stopped almost all services except for one, as shown in the output:

Function to Create Start All SQL Related services on a Machine :

#All of the SQL Server services on a specific server are started with this function.
# Start-AllSQLServerServices Node1
 
function Start-AllSQLServerServices
{
    [cmdletbinding()]
    Param([string]$Server
    , [bool]$StartSQL=$true
    , [bool]$StartAgent=$true
    , [bool]$StartSSRS=$true
    , [bool]$StartBrowser=$true
    , [bool]$StartSSIS=$true
    , [bool]$StartTextDaemon=$true
    , [bool]$StartSSAS=$true)
 
    #Get all the services on the server
    $Services = get-service -ComputerName $Server
 
    if($StartSQL -eq $true)
    {
        #check the SQL Server Engine services
        write-verbose "Checking SQL Server Engine Services"
 
        #get all named instances and the default instance
        foreach ($SQLService in $Services | where-object {$_.Name -match "MSSQLSERVER" -or $_.Name -like "MSSQL$*"})
        {
            #Check the service running status
            if($SQLService.status -eq "Stopped")
            {
                #if stopped start the SQL Server service
                write-verbose "Starting SQL Server Service $($SQLService.Name)"
                $SQLService.Start()
            }
            else
            {
                #Write comfort message that the service is already running
                write-verbose "SQL Server Service $($SQLService.Name) already running"
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SQL Engine services"
    }
 
    if($StartAgent -eq $true)
    {
        #check the SQL Server Agent services
        write-verbose "Checking Agent Services"
 
        #get all named agent instances and the default instance
        ForEach ($SQLAgentService in $Services | where-object {$_.Name -match "SQLSERVERAGENT" -or $_.Name -like "SQLAgent$*"})
        {
            #check the servcie running status
            if($SQLAgentService.status -eq "Stopped")
            {
                #if stopped, start the agent
                write-verbose "Starting SQL Server Agent $($SQLAgentService.Name)"
                $SQLAgentService.Start()
            }
            else
            {
                #write comfort message that the service is already running
                write-verbose "SQL Agent Service $($SQLAgentService.Name) already running"
            }
        }
    }
    else
    {
        write-verbose "Skipping checking Agent services"
    }
 
    if($StartSSRS -eq $true)
    {
        #check the SSRS services
        write-verbose "Checking SSRS Services"
 
        #get all reporting service services
        ForEach ($SSRSService in $Services | where-object {$_.Name -match "ReportServer"})
        {
            #check the status of the service
            if($SSRSService.status -eq "Stopped")
            {
                #if stopped, start the agent
                write-verbose "Starting SSRS Service $($SSRSService.Name)"
                $SSRSService.Start()
            }
            else
            {
                #write comfort message that the service is already running
                write-verbose "SQL Agent Service $($SSRSService.Name) already running"
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SSRS services"
    }
 
    if($StartSSIS -eq $True)
    {
 
        #get the SSIS service (should only be one)
        write-verbose "Checking SSIS Service"
 
        #get all services, even though there should only be one
        ForEach ($SSISService in $Services | where-object {$_.Name -match "MsDtsServer*"})
        {
            #check the status of the service
            if($SSISService.Status -eq "Stopped")
            {
                #if its stopped, start it
                write-verbose "Starting SSIS Service $($SSISService.Name)"
                $SSISService.Start()
            }
            else
            {
                #write comfort message
                write-verbose "SSIS $($SSISService.Name) already running"
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SSIS services"
    }
 
    if ($StartBrowser -eq $true)
    {
 
        #Check the browser, start it if there are named instances on the box
        write-verbose "Checking SQL Browser service"
 
        #check if there are named services
        if(($services.name -like "MSSQL$*") -ne $null)
        {
            #get the browser service
            $BrowserService = $services | where-object {$_.Name -eq "SQLBrowser"}
 
            if($BrowserService.Status -eq "Stopped")
            {
                #if its stopped start it
                write-verbose "Starting Browser Server $($BrowserService.Name)"
                $BrowserService.Start()
            }
            else
            {
                #write comfort message
                write-verbose "Browser service $($BrowserService.Name) already running"
            }
        }
        else
        {
            #if no named instances, we don't care about the browser
            write-verbose "No named instances so ignoring Browser"
        }
    }
    else
    {
        write-verbose "Skipping checking Browser service"
    }
 
    if($StartTextDaemon -eq $True)
    {
 
        # Start the full text daemons
        write-verbose "Checking SQL Full Text Daemons"
 
        ForEach($TextService in $Services | where-object {$_.Name -match "MSSQLFDLauncher"})
        {
            #check the service status
            if ($TextService.Status -eq "Stopped")
            {
                #start the service
                write-verbose "Starting Full Text Service $($TextService.Name)"
                $TextService.Start()
            }
            else
            {
                write-verbose "Text service $($TextService.Name) already running."
            }
        }
    }
    else
    {
        write-verbose "Skipping checking Text Daemon services"
    }
 
    if($StartSSAS -eq $True)
    {
 
        # start the SSAS service
        write-verbose "Checking SSAS services"
 
        ForEach($SSASService in $Services | where-object {$_.Name -match "MSSQLServerOLAP"})
        {
            #check the service status
            if ($SSASService.Status -eq "Stopped")
            {
                #start the service
                Write-verbose "Starting SSAS Service $($SSASService.Name)"
                $SSASService.Start()
            }
            else
            {
                write-verbose "SSAS Service $($SSASService.Name) already running."
            }
        }
    }
    else
    {
        write-verbose "Skipping checking SSAS services "
    }
}

I used PowerShell to run the Start function, which started almost all services except some of them, as shown in the output:

We may also accomplish this using DBATOOLS, and the commands are provided below. Before executing these commands, we must first install the DBATOOLS Module.


Get-DbaService

Start-DbaService

Stop-DbaService

Restart-DbaService

SQL Agent jobs Status Report Using PowerShell

As a SQL DBA we need to know if SQL Agent jobs are running successfully on all the Servers which we are supporting, for this we have SCOM monitoring in place and which is linked to SNOW ticketing tool means If any SQL Agent Fails it triggers a SNOW ticket to our DBA Queue and we work on it.

But recently there was a SCOM upgrade happened and after that we our DBA team are not getting any sort of alerts for some servers which we are supporting this includes not getting SNOW tickets for SQL Agent Job Failures , this becomes a problematic for us to Login each and every server to check the jobs status (Servers which are Missed by SCOM to generate a ticket ).

Manual checking is creating a lot of work for us So, thought of Automating it to get the SQL Agent Jobs Status Report generated for every day ( in each Shift at least once ). In my career starting there is no monitoring tool setup in my environment to check the status of jobs ,databases e.tc., At that time our Team Leader had done some good automation to reduce our DBA’s work in that one of the Automation is to get Status Report of SQL Agent Jobs for all the Servers which are provided in TEXT file and get the report stored in an Excel file .

I have that script with me and saved in my Scripts repository , today that script is reQuired for me in my current organization So, I have did some testing in my Personal Laptop and it works well.

This Automation was done using a PowerShell where we need to provide the list of servers in a text file to check for SQL Agent jobs Status .Also we need an Excel already installed on your Remote VDI or Citrix where you are running this script and the Login account which you are using for running the PowerShell script needs to have Admin permission to all the SQL Instances mentioned in the text file.

I have created a text file with name ‘ Servers ‘ and provided list of Instances in the text file that you want to check .

I have opened the PowerShell ISE with run As Administrator and copied below script to it :

#############################################################################################
#
# NAME: SQL Agent Job Status Report to Excel.ps1
# Iterates through the sqlservers.txt file to populate
# Excel File with color coded status
## ————————————————————————

# Get List of sql servers to check
$sqlservers = Get-Content 'C:\temp\Servers.txt'; # Change the Path here where you stored the servers text file

# Create a .com object for Excel
$xl = new-object -comobject excel.application
$xl.Visible = $true # Set this to False when you run in production

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

$date = Get-Date -format f
$Filename = ( get-date ).ToString('ddMMMyyyHHmm')

$cells = $ws.Cells

# Create a description

$cells.item(1, 3).font.bold = $True
$cells.item(1, 3).font.size = 18
$cells.item(1, 3) = "Jobs Status Report $date"
$cells.item(5, 9) = "Last Job Run Older than 1 Day"
$cells.item(5, 8).Interior.ColorIndex = 43
$cells.item(4, 9) = "Last Job Run Older than 7 Days"
$cells.item(4, 8).Interior.ColorIndex = 53
$cells.item(7, 9) = "Successful Job"
$cells.item(7, 8).Interior.ColorIndex = 4
$cells.item(8, 9) = "Failed Job"
$cells.item(8, 8).Interior.ColorIndex = 3
$cells.item(9, 9) = "Job Status Unknown"
$cells.item(9, 8).Interior.ColorIndex = 15


#define some variables to control navigation
$row = 3
$col = 2

#insert column headings

$cells.item($row, $col) = "Server Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$col++
$cells.item($row, $col) = "Job Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 40
$col++
$cells.item($row, $col) = "Enabled?"
$cells.item($row, $col).font.size = 16    
$Cells.item($row, $col).Columnwidth = 15
$col++    
$cells.item($row, $col) = "Outcome"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 12
$col++
$cells.item($row, $col) = "Last Run Time"
$cells.item($row, $col).font.size = 16    
$Cells.item($row, $col).Columnwidth = 15
$col++

   
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;


# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
    # Create an SMO Server object
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
 
    # For each jobs on the server
    foreach ($job in $srv.JobServer.Jobs) {

        $jobName = $job.Name;
        $jobEnabled = $job.IsEnabled;
        $jobLastRunOutcome = $job.LastRunOutcome;
        $Time = $job.LastRunDate ;

        # Set Fill Colour for Job Enabled
        if ($jobEnabled -eq "FALSE")
        { $colourenabled = "2"}
        else {$colourenabled = "48" }         

        # Set  Fill Colour for Failed jobs
        if ($jobLastRunOutcome -eq "Failed") {
            $colour = "3" # RED
        }
            
        # Set Fill Colour for Uknown jobs
        Elseif ($jobLastRunOutcome -eq "Unknown")
        { $colour = "15"}       #GREY        

        else {$Colour = "4"}   # Success is Green    
        $row++
        $col = 2
        $cells.item($Row, $col) = $sqlserver
        $col++
        $cells.item($Row, $col) = $jobName
        $col++
        $cells.item($Row, $col) = $jobEnabled    
        #Set colour of cells for Disabled Jobs to Grey
    
        $cells.item($Row, $col).Interior.ColorIndex = $colourEnabled
        if ($colourenabled -eq "48") { 
            $cells.item($Row , 1 ).Interior.ColorIndex = 48
            $cells.item($Row , 2 ).Interior.ColorIndex = 48
            $cells.item($Row , 3 ).Interior.ColorIndex = 48
            $cells.item($Row , 4 ).Interior.ColorIndex = 48
            $cells.item($Row , 5 ).Interior.ColorIndex = 48
            $cells.item($Row , 6 ).Interior.ColorIndex = 48
            $cells.item($Row , 7 ).Interior.ColorIndex = 48
        } 
        $col++

        $cells.item($Row, $col) = "$jobLastRunOutcome"
        $cells.item($Row, $col).Interior.ColorIndex = $colour

        #Reset Disabled Jobs Fill Colour
        if ($colourenabled -eq "48") 
        {$cells.item($Row, $col).Interior.ColorIndex = 48}

        $col++

        $cells.item($Row, $col) = $Time 
    
        #Set teh Fill Colour for Time Cells

        If ($Time -lt ($(Get-Date).AddDays(-1)))
        { $cells.item($Row, $col).Interior.ColorIndex = 43}
        If ($Time -lt ($(Get-Date).AddDays(-7)))
        { $cells.item($Row, $col).Interior.ColorIndex = 53} 
              
    }
    $row++
    $row++

    # Add two Yellow Rows
    $ws.rows.item($Row).Interior.ColorIndex = 6
    $row++
    $ws.rows.item($Row).Interior.ColorIndex = 6
    $row++
}


$wb.Saveas("C:\temp\SQLAgentJobstatusReport$filename.xlsx") #Change the Path where ever you want the Excel Report to Store
$xl.quit()
Stop-Process -Name EXCEL

I have executed the script , immediately an Excel is opened at the backend where the jobs information is getting populated in it , you can find that in below screenshot :

Once the Report collection is finished the excel file will get closed automatically and saved the populated jobs information in it, you can open the excel file from where you have given the path to Save it in above PowerShell Script.

After opening the Excel file you can see all the servers Jobs Information with Color coded for easy identification like below :

Also after each server it will Separate with Yellow Color coding like below :

From the Servers list I provided one of my SQL Instance is in Stopped State for that server While pulling the information of jobs this script does not get any information but it left the Space for us to know that one of the server information is not pulled by script also the script didn’t stop and it continue to next server to get the needed information.

I have Implemented this in my Current organization and it reduces our manually efforts .

Note : Before Implementing please do some testing from your end and get the confident to Implement in your Environment.

Export Multiple Blob Files Data From Sql Table to Physical Location Using PowerShell

BLOB Data can be in a Audio file, pdf file, doc file, jpg/ png/ any image file of data types which can be loaded to a Sql table and saved in a Sql Database.

I have Imported or loaded some jpg files and pdf files into a Table in Sql Database to save the data. Generally this data loading to Sql table will be done by application team , if they need the data to be exported from a Sql table application will contact us .

I have exported this Blob data using PowerShell Script which is simple to use , for this approach used Ado.Net to query the data and used binary writer to write the files on a drive

I have Created a Database ‘BLOB‘ and a table ‘dbo.BlobData‘ , inserted some jpg and pdf files into the table .

From above screenshot you can see both PDF and JPG files in a Table with 27 rows. Now I am going to export those all files to a physical Location in one go.

I want to export the files to below shown location, currently we don’t see any files:

Below is the PowerShell Script I used to export the data , you need to change Parameters like InstanceName, DatabaseName, Destination Path and SELECT Statement .


## You can export LARGE Blob to file            
## with GetBytes-Stream.         
# In the below Code you need to change some parameters which are needed to get successfull export of blob files from a Table to disk.
            
$Server = "DESKTOP-02JIB76";         # Change to your Instance Name.            
$Database = "Blob";                  # Change to your Database Name
$Dest = "C:\temp\Blob\Export\";             # Change Path here.            
$bufferSize = 8192;               # Stream buffer size in bytes.            
# Select-Statement for name & blob            
# with filter.            
$Sql = "SELECT [PictureName]
              ,[PictureData]
        FROM dbo.BlobData";            
             
# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=True;" +            
                        "Initial Catalog=$Database";            
$con.Open();            
             
# New Command and Reader            
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;            
$rd = $cmd.ExecuteReader();            
             
# Create a byte array for the stream.            
$out = [array]::CreateInstance('Byte', $bufferSize)            
             
# Looping through records            
While ($rd.Read())            
{            
    Write-Output ("Exporting: {0}" -f $rd.GetString(0));                    
    # New BinaryWriter            
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write;            
    $bw = New-Object System.IO.BinaryWriter $fs;            
                
    $start = 0;            
    # Read first byte stream            
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    While ($received -gt 0)            
    {            
       $bw.Write($out, 0, $received);            
       $bw.Flush();            
       $start += $received;            
       # Read next byte stream            
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    }            
             
    $bw.Close();            
    $fs.Close();            
}            
             
# Closing & Disposing all objects            
$fs.Dispose();            
$rd.Close();            
$cmd.Dispose();            
$con.Close();            
             
Write-Output ("Finished");

I have executed above script in a PowerShell and all 27 files are exported to our destination location :

In below screenshot you can see all 27 files are successfully exported from a table :

We can also get this Blob data out by some other methods like below :

. Export Data using BCP T-Sql from SSMS but this required xp_cmdshell needs to be ENABLED

. Export Data using BCP Command from Command Prompt.

. Export Data using OLE Processes which needs Ole Automation Procedures to be ENABLED