Often there’s a need to perform backup of all multiple databases on an
instance or maybe multiple databases on multiple instance across environment.
Now, this may well become a laborious task if you don’t have any fancy tool to
do the job for you and that’s exactly where PowerShell comes to rescue.
PowerShell can be used to perform such task easily.
Let’s Get Started!!!
How to Use the Script:
{
<#
.SYNOPSIS
Backup User databases on specified Instances across Environment.
.DESCRIPTION
This Script uses SMO to backup databases across environment based on parameters provided.
This will create CopyOnly Backup's to avoid breaking Backup Chain.
.PARAMETER backupdirectory
Backup Location for CopyOnly Backups either on local system or on shared location.
.PARAMETER instanceList
List of instances for whih backup needs to be performed.
You may specify a Text file like d:\List.txt containing list of instances.
.EXAMPLE
Backup all user databases on specified Instance.
Sql-BackupDatabases -backupdirectory d:\backup -instanceList "My\Instance"
Sql-BackupDatabases -backupdirectory d:\backup -instanceList d:\list.txt
.NOTES
The SQL Server Service account must have access to Backup Location and You should use an account that has access to all SQL Instances across environment.
#>
param([parameter(Mandatory=$true)] [string] $backupdirectory, $instanceList)
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.SMOExtended")
if ($instanceList -like "*.txt")
{
$list=get-content $instanceList
}
else { $list=$instanceList}
if($backupdirectory -eq ""){write-host "please provide Backup Location"}
foreach ($l in $list)
{
$instance=Connect-Instance -instanceName $l
$bkdir = $backupdirectory +"\"+ $l
if (!(Test-Path $bkdir))
{
New-Item -Path $bkdir -ItemType Directory
}
$database=$instance.Databases["master"]
$dbs = $database.ExecuteWithResults("select name from sys.databases where name not in ('master','model','tempdb','msdb') and state_desc='ONLINE'")
$dbs = $dbs.tables[0]
foreach ($db in $dbs)
{
Backup-Database -data $db.name -server $instance
}
}
}
function Connect-Instance
{
param ([string] $instanceName)
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instanceName
$s.connectioncontext.loginsecure=$false
$credential=get-credential
##$s.connectioncontext.set_login("sa")
$s.connectioncontext.set_login($credential.UserName)
##$credential=Credentials
##$s.connectioncontext.set_securepassword($credential)
$s.connectioncontext.set_securepassword($credential.Password)
return $s
}
### Instruction To generate password use below command #####
in powershell command prompt type: [byte[]] $key=(1..16)
$pass="Your Password"| ConvertTo-SecureString -Force -AsPlainText | ConvertFrom-SecureString –Key $key
This will generate a secure password using Key variable. Use the contents of $pass variable for $password value in below function
##############################################
function Credentials
{
[byte[]] $key=(1..16)
$password="use $pass value from above"
$credential=new-object system.management.automation.pscredential "sa",($password | convertto-securestring –key $key)
return $credential.Password
}
Function Backup-Database
{
Param ([string] $data,$server)
$back= new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$back.database=$db.name
$dbname = $db.name
$back.action="Database"
$back.CopyOnly=$true
$back.devices.adddevice($bkdir + "\" + $db.name + ".bak","File")
$back.backupsetname="BackupTest"
$percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
}
$back.add_PercentComplete($percent)
$back.add_Complete($complete)
Try
{
$back.sqlbackup($server)
Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
}
Catch
{
Write-Output $_.Exception.InnerException
}
}
Let’s Get Started!!!
How to Use the Script:
- Copy below function to notepad file and save as .psm1
- Follow instruction in script before function Credentials
- Import psm1 file using command : Import-Module <Location of psm1 file>
- Type Get-Help Sql-BackupDatabases to get information and instructions about script
{
<#
.SYNOPSIS
Backup User databases on specified Instances across Environment.
.DESCRIPTION
This Script uses SMO to backup databases across environment based on parameters provided.
This will create CopyOnly Backup's to avoid breaking Backup Chain.
.PARAMETER backupdirectory
Backup Location for CopyOnly Backups either on local system or on shared location.
.PARAMETER instanceList
List of instances for whih backup needs to be performed.
You may specify a Text file like d:\List.txt containing list of instances.
.EXAMPLE
Backup all user databases on specified Instance.
Sql-BackupDatabases -backupdirectory d:\backup -instanceList "My\Instance"
Sql-BackupDatabases -backupdirectory d:\backup -instanceList d:\list.txt
.NOTES
The SQL Server Service account must have access to Backup Location and You should use an account that has access to all SQL Instances across environment.
#>
param([parameter(Mandatory=$true)] [string] $backupdirectory, $instanceList)
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.SMOExtended")
if ($instanceList -like "*.txt")
{
$list=get-content $instanceList
}
else { $list=$instanceList}
if($backupdirectory -eq ""){write-host "please provide Backup Location"}
foreach ($l in $list)
{
$instance=Connect-Instance -instanceName $l
$bkdir = $backupdirectory +"\"+ $l
if (!(Test-Path $bkdir))
{
New-Item -Path $bkdir -ItemType Directory
}
$database=$instance.Databases["master"]
$dbs = $database.ExecuteWithResults("select name from sys.databases where name not in ('master','model','tempdb','msdb') and state_desc='ONLINE'")
$dbs = $dbs.tables[0]
foreach ($db in $dbs)
{
Backup-Database -data $db.name -server $instance
}
}
}
function Connect-Instance
{
param ([string] $instanceName)
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instanceName
$s.connectioncontext.loginsecure=$false
$credential=get-credential
##$s.connectioncontext.set_login("sa")
$s.connectioncontext.set_login($credential.UserName)
##$credential=Credentials
##$s.connectioncontext.set_securepassword($credential)
$s.connectioncontext.set_securepassword($credential.Password)
return $s
}
### Instruction To generate password use below command #####
in powershell command prompt type: [byte[]] $key=(1..16)
$pass="Your Password"| ConvertTo-SecureString -Force -AsPlainText | ConvertFrom-SecureString –Key $key
This will generate a secure password using Key variable. Use the contents of $pass variable for $password value in below function
##############################################
function Credentials
{
[byte[]] $key=(1..16)
$password="use $pass value from above"
$credential=new-object system.management.automation.pscredential "sa",($password | convertto-securestring –key $key)
return $credential.Password
}
Function Backup-Database
{
Param ([string] $data,$server)
$back= new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$back.database=$db.name
$dbname = $db.name
$back.action="Database"
$back.CopyOnly=$true
$back.devices.adddevice($bkdir + "\" + $db.name + ".bak","File")
$back.backupsetname="BackupTest"
$percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
}
$back.add_PercentComplete($percent)
$back.add_Complete($complete)
Try
{
$back.sqlbackup($server)
Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
}
Catch
{
Write-Output $_.Exception.InnerException
}
}
ReplyDeletewow very nice article glad to see your article ....
do not forget to visit our website as well: Sejarah Togel D: D
thanks for the info very nice and creative
successful greeting always: Togel Online D: D
nice article.....
While Microsoft Help is right there, on our systems and a click away on the Internet, it may not be something that many users are able to use.reset windows 10 password
ReplyDeletehttps://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
B3X
glassagram
ReplyDeleteallsmo
instagram gizli hesap görme
revelio
bestwhozi
A0RM