Information Technology: Multi server PowerShell script to backup SQL Server databases automatically

Multi server PowerShell script to backup SQL Server databases automatically

Eighty-two percent of the enterprises expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the performance of databases. Think about the storage requirement and backup strategy to meet the Recovery Time Objective and the Recovery Point Objective. RPO and RTO are two of the most important parameters of a disaster recovery or data protection plan.

Database backup overview

Let us take a look at some of the most common ways to back up the SQL Server database, and some of the best and most feasible solutions for data protection and disaster recovery scenarios.
Let us focus on handling huge volumes of data using various techniques and/or methodologies. Some of us may have questions on how to decide the best-suited backup strategies for our environments; on automating and managing SQL Server database backup; on whether we should automate the database backup process using T-SQL, SSIS, or PowerShell or some other tool or technique; what the data recovery and protection plans available are; whether the SQL engine provides the required capabilities to schedule a job and run it across multiple servers; whether customization options are available; whether we have a robust method to perform backup activity.
Let find out the answers to those questions. I’m sure you’ll not be disappointed!

Getting started

A database administrator must make sure that all databases are backed up across environments. Understanding the importance of database backup is critical. Setting the right recovery objective is vital, and hence, we need to consider the backup options carefully. Configuring the retention period is another area to ensure integrity of the data.
Backing up data regularly is always a good strategy on the one hand, and on the other, we must regularly test the backup copies to ensure that they’re tested and validated for smooth working of the systems, and to prevent any sort of corruption or, under extreme conditions, a disaster. The well-tested SQL Server database backup script that we’re going to discuss provides an essential safeguard for protecting the (critical) data stored in the SQL Server databases. Backups are also very important to preserve modifications to the data on regular basis.
With a well-configured SQL Server database backup, one can recover data from many failures such as:

Hardware failures
User-generated accidents
Catastrophic disasters

Let us now look at the various options and methodologies which can be used to initiate a database backup.
There are different ways to back-up a database:

SSMS – Backups can be performed manually using SQL Server Management Studio
SQL Agent Job – Using a T-SQL script for backup
Using Maintenance Plan – SSIS Packages
SMO (SQL Server Management Objects) – PowerShell Scripts
Using ApexSQL Backup

This article talks about the use SQL Server Management Objects (SMO) and its advantages in making life easier. SMO is a complete library of programmatically accessed objects that enable an application to manage a running instance of Microsoft SQL Server Services. PowerShell is used to create this SQL Server databases backup script using the SMO classes. The script backs up specific or all databases of an instance to the backup location, or a local/remote/network share, and manages the backup files as per the retention period set.
Let’s look at how other DBAs in the industry are tackling massive data growth, what their most important goals are, and strategy to backup SQL Server databases automatically. Let us also look at some of the third party tools for backup management.
The first three options are very well discussed in the How to backup multiple SQL Server databases automatically article. Now that the first three points are already covered, let’s look at the use of the PowerShell SMO options with ApexSQL Backup. We’ll learn about how we can increase the database performance and eliminate downtime to give users the best experience possible using ApexSQL Backup. Today’s challenge is to give customers the most visually appealing and contextually rich insights possible in a user-friendly interface. ApexSQL has all the rich features and has the intelligence to manage and deploy a SQL backup plan to many SQL instances. A good way to start this process is to test the feasibility by downloading the free trial version of the tool.

Initial preparation

The goal of many organizations is to manage the backup of SQL Server databases automatically. We’ll go through the necessary steps to create the PowerShell SQL Server database backup script shortly. We can list any number of SQL servers and databases using the script. We can also create multiple jobs to initiate backup on multiple servers.
Pre-requisites

Enable XP_CMDSHELL

EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

Before you proceed, set the execution policy on PowerShell
Load the necessary modules if they’re not loaded automatically
Add full rights on file share or local or remote location where you’d like the backups stored
Add default permissions to BACKUP DATABASE and BACKUP LOG to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles

The following code section details

Handling multiple SQL Server databases automatically
Managing local or remote database backups
Email notification upon completion of every successful backup
Setting the retention period
Scheduling automated jobs

Constructing the PowerShell script

Let us walk through the script, step-by-step, along with looking at the details of the setup and configuration of the script. The complete script can be found in Appendix (A)
Step 1: Declare the variable, and load the SMO library

Step 2: Define the email functionality

Step 3: Looping through databases

Step 4: Initiate Database backup and Email body preparation

Step 5: Manage database backup file

Let’s now go about using this script to back up the database by scheduling an SQL Job. Using Object Explorer, expand the SQL Server Agent. Right-click on Jobs. Select New job.

In the General tab of the window that pops up, enter the name, owner and the description for the job. Let’s call this SQLBackupCentralizedJob.

In the Steps tab, click on New to configure the job.
In the General tab,

Mention the step name as SQLBackup,
Set the job type to Transact-SQL script (T-SQL)
Select the master database in the Database box.
Paste the following script that will be used for this job in the Command box. Click OK.
```
master..xp_cmdshell 'PowerShell.exe F:\PowerSQL\MSSQLBackup.ps1'
```
Click OK.

We have now successfully created the job.

Right-click on SQLBackup_CentralizedJob and run it.

We can check the backup folder for the backup files; it would tell us the progress, like so:

Invoke_SQLDBBackup -SQLServer HQDBT01 -BackupDirectory f:\SQLBackup -dbList "SQLShack_Demo,ApexSQLBAckup" -rentention 3 -Mail Yes
Invoke_SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL -dbList "SafetyDB,rtc,rtcab1" -rentention 3 -Mail Yes

Verify the email

Back up multiple SQL databases with ApexSQL Backup

ApexSQL Backup is a third-party software solution that can be used to define and/or manage the backup/restore processes and perform various maintenance operations. The tool is capable of performing the backup of SQL Server databases automatically.
The backup of multiple SQL databases can be configured in a few simple steps:
Step 1: Register the server

Select Home tab
Click Add button
- Enter the SQL Server
- Select authentication type
Click Ok

Step 2: The Backup Configuration Wizard
Now you should see all the databases that are available on that server. Click on the Backup button on the Home ribbon tab to configure the jobs.

The main tab of the backup wizard is for backup settings and defining the backup configuration. This section has three options

Backup
1. Select the SQL Server from the drop-down list; you can select the server you’d like to configure the backup for
2. In the Databases, browse and check the intended databases for backup
3. Click OK
  Next, set the backup type, defining the backup location and its standards
4. Select the type of the database backup
5. To have a better experience, set the Job name and the Job description. It’s usually a good practice to do so.
6. Click on the Add button in Destination group, and set the backup destination path, or configure custom naming rules.
7. Browse for the destination path in the Folder text box.
8. In the Filename box, configure the format of the backup filename by clicking on the corresponding tags—you can select from the seven available tags. Each of those can be included in the backup filename. Check the summary and click OK if everything is configured as required. You can click on ApexSQL defaults to reset the configuration.
9. Click OK
  
  Now, let us schedule the backup job using the Schedule wizard. This wizard is invoked using the overflow (…) button.
10. In the wizard, set the frequency, daily frequency and activity schedule of database backup as desired. Check the summary at the bottom of the page to confirm that the configuration is done as required. Click OK to save changes in the Schedule Wizard.
11. Click OK.
The Advanced tab
We can add various media, verification, compression and encryption options along with encryption settings in the Advanced tab of the wizard.
1. Set the retention period in the cleanup tab to clean-up the database backup files
2. Click OK.
Notification
Use the notification tab to set the type of alerts you would like an email notification sent for.
1. Use Options tab to select the notification events
2. Click the Add button to add recipient details
3. Click OK to commit.

Schedules

Run now

Disable

Wrapping Up

ApexSQL Backup

References

Appendix (A)

Function Get-SQLDBBackup 
{ 
 param (
    
        [Parameter (Mandatory=$true,Position=0)][String]$SQLServer,  
        [Parameter(Mandatory=$true,Position=1)][String]$BackupDirectory, 
        [Parameter(Mandatory=$true,Position=2)][String]$dbList,
        [Parameter(Mandatory=$true,Position=3)][int]$retention,
        [Parameter(Mandatory=$true,Position=3)][String]$Mail) 

#loading SMO library
  
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 


# Setting the backup data in yyyyMMdd_HHmmss that is 20170619_130939
  
$BackupDate = get-date -format yyyyMMdd_HHmmss 

Function sendEmail  

 { 
param($from,$to,$subject,$smtpServer)  
[string]$recipients="$to"
$body = New-Object System.Net.Mail.MailMessage $from, $recipients, $subject, $body 
$smtpServer = $smtpServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)

 } 


#Define the SMO class library
   
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer
#Iterating the SQL Server databases of a given instance
   
foreach ($Database in $Server.databases) 
    { 
        # Teh 
        foreach($db in $DbList.split(",")) 
        { 
            if($Database.Name -eq $db) 
            { 
                $DatabaseName = $Database.Name 

                $DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup") 
                $DatabaseBackup.Action = "Database"
                $DatabaseBackup.Database = $DatabaseName 
                #Set the directory for backup location 
                $BackupFileName=$BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK"
                #write-host $BackupFileName
                $DatabaseBackup.Devices.AddDevice($BackupFileName, "File") 
                
                 try 
                   {
                   Write-Progress -Activity "Please wait! Backing up SQL databases... " -Status "Processing:" -CurrentOperation "Currently processing:  $DatabaseName" 
                   $DatabaseBackup.SqlBackup($Server)  
                   $body=@"
Notification that a $DatabaseName is backed up! successfully with date and time stamp $BackupDate
"@
                    }
                   catch 
                   {
                   $body=@"
Notification that a $DatabaseName is backed failed! with an error message $_.Exception.Message
"@
  }

                write-host $status
                           
                if ($Mail ='Yes') { 
                
                  sendEmail -To "pjayaram@sqlshack.com" -Subject " $SQLServer -> $DatabaseName Backup Status" `
                   -From "pjayaram@sqlshack.com" -Body $body  `
                   -smtpServer "hqmail.abc.com"
                        
                    }
                # Preparing the UNC path for the database backup file handling
                # fetching the drive letter. First argument is that starting position in the string, and the second is the length of the substring, starting at that position. 
                $drive=$BackupFileName.substring(0,1)
                $len=$BackupDirectory.length
                #write-host $len
                #Selecting the string portion of a directory that is fetching the string starting from the character posittion
                $path=$BackupDirectory.substring(3,$len-3)
                #write-host \\$SQLServer\$drive$\$path
                # Listing the files which is older than 3 minutes in this caseon a server. It can be local or remote location
                $file=get-ChildItem \\$SQLServer\$drive$\$path -recurse -Filter $DatabaseName*.bak | Select-object LastWriteTime,directoryname,name |where-object {$_.LastWriteTime -lt [System.DateTime]::Now.Addminutes(-$rentention)}
                #Iterating each file and remove the file with remove-item cmdlet
                foreach($f in $file)
                {
                $filename=$f.directoryname+'\'+$f.name
                write-host 'File deleted' $filename
                remove-item $filename -Force
                }
            #$DatabaseBackup | select LogicalName, Type, Size, PhysicalName | Format-Table -AutoSize
            } 
        } 
    } 
}
  
 Get-SQLDBBackup -SQLServer HQDBT01 -BackupDirectory f:\SQLBackup -dbList "SQLShack_Demo,ApexSQLBackup" -rentention 3 -Mail Yes
 Get-SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL -dbList "SafetyDB,rtc,rtcab1" -rentention 3 -Mail Yes

Information Technology

Thursday, April 25, 2019

Multi server PowerShell script to backup SQL Server databases automatically

Multi server PowerShell script to backup SQL Server databases automatically

Database backup overview

Getting started

Initial preparation

Constructing the PowerShell script

Back up multiple SQL databases with ApexSQL Backup

Wrapping Up

References

Appendix (A)

No comments:

Post a Comment

My Blog List

Report Abuse