I came across another opportunity for using Powershell when setting up mirroring for a large database at our DR site. The database was over 73Gig. The problem arose when it took a very long time to copy the backup and transaction logs and run the database restore. By the time the copy had finished another several transaction logs had been produced. While applying the *.bak restore, yet another several transaction logs had been produced. Continue reading ‘Restore All SQL Transaction Logs using Powershell’ »
Posts tagged ‘Powershell’
иконографияиконикухненски маси
Some quick methods of obtaining a midnight DateTime value for almost any date.
Clear-Host # Get Midnight for current date Get-Date -Hour 0 -Minute 00 -Second 00 # Another way [datetime]::Today # Get Midnight for date a week ago (Get-Date -Hour 0 -Minute 00 -Second 00).AddDays(-7) # Or use this ([datetime]::Today).AddDays(-7) # Get Midnight for date a month ago Get-Date -Month $((Get-Date -Format "MM") - 1) -Hour 0 -Minute 0 -Second 0 # Get Midnight for a date one year ago Get-Date -Year $((Get-Date -Format "yyyy") - 1) -Hour 0 -Minute 0 -Second 0
I don’t often have to restore a database. However, while having to restore a few dozen to set up database mirroring, I came across a few with multiple database files. My first ‘mirroring’ script did not account for this. I quickly wrote a new restore script to accommodate the possibility of multiple files.
<#
.SYNOPSIS
Restore to a NEW Database
.DESCRIPTION
Restores an SQL Server backup file to a new database.
1) Uses existing Logical and physical file names and restores to the
SQL server default file locations.
2) Restores multiple files.
3) Can specify NoRecover (necessary for mirrored database)
.PARAMETER file
Full path and file name for the backup file. Must be local
.PARAMETER Server
The name\instance of the SQL Server.
.PARAMETER database
The name of the database to be restored
.EXAMPLE
PS C:\> Invoke-SqlRestore -file 'D:\Backups\mydb.bak' `
-server 'MyServr\SQLinstance' -database NEWDB
.NOTES
AUTHOR: John P. Wood
CREATED: July, 2010
VERSION: 1.0.3
.LINK
http://www.webofwood.com
#>
Param(
[string]$file,
[string]$server,
[string]$database,
[switch]$norecovery=$true
)
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Function New-SMOconnection {
Param ([string]$server)
$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
$conn.applicationName = "PowerShell SMO"
$conn.StatementTimeout = 0
$conn.Connect()
if ($conn.IsOpen -eq $false) {
Throw "Could not connect to server $($server)."
}
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
$smo
}
Function Get-FileName {
Param([string]$path)
$names = $path.Split('\\')
$names[$names.Count - 1]
}
Function Invoke-SqlRestore {
Param(
[string]$backupFile,
[Microsoft.SqlServer.Management.Smo.Server]$smo
)
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") `
($backupFile, "File")
# Get local paths to the Database and Log file locations
If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
Else { $DBPath = $smo.Settings.DefaultFile}
If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
Else { $DBLogPath = $smo.Settings.DefaultLog}
# Load up the Restore object settings
$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
$Restore.Action = 'Database'
$Restore.Database = $database
$Restore.ReplaceDatabase = $true
if ($norecovery.IsPresent) { $Restore.NoRecovery = $true }
Else { $Restore.Norecovery = $false }
$Restore.Devices.Add($backupDevice)
# Get information from the backup file
$RestoreDetails = $Restore.ReadBackupHeader($smo)
$DataFiles = $Restore.ReadFileList($smo)
# Restore all backup files
ForEach ($DataRow in $DataFiles) {
$LogicalName = $DataRow.LogicalName
$PhysicalName = Get-FileName -path $DataRow.PhysicalName
$RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$RestoreData.LogicalFileName = $LogicalName
if ($DataRow.Type -eq "D") {
# Restore Data file
$RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
}
Else {
# Restore Log file
$RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName
}
[Void]$Restore.RelocateFiles.Add($RestoreData)
}
$Restore.SqlRestore($smo)
# If there are two files, assume the next is a Log
if ($RestoreDetails.Rows.Count -gt 1) {
$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
$Restore.FileNumber = 2
$Restore.SqlRestore($smo)
}
}
Clear-Host
# Get a new connection to the server
$smo = New-SMOconnection -server $server
Write-Host "Starting restore to New Database $database on $server."
Try {
Invoke-SqlRestore -backupFile $file -smo $smo
}
Catch {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException) {
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
}
Throw $ex
}
Finally {
$smo.ConnectionContext.Disconnect()
}
Write-Host "Restore ended without any errors."
1) Uses existing Logical and physical file names and restores to the SQL server default file locations. 2) Restores multiple files. 3) Can specify NoRecover (necessary for mirrored database)
Management recently decided to use database mirroring as our DR solution. Because mirroring is done at the database level and not at the server level, I had a lot, a very lot of databases to be mirrored. To make it easier I decided to cobble together a simplistic script to do this.
I typically use a ‘management’ server for most of my needs and have therefore written the script to use UNC type pathing for the primary as well as mirror server. You will also notice I defaulted several of the parameters. This is handy when working on a set of servers for multiple databases.
<#
.SYNOPSIS
Set up a mirrored database
.DESCRIPTION
Backs up a database and tlog, copies it to the destination,
Restores the database on the mirror server, sets up the partner,
and starts the mirror.
.PARAMETER database
The name of the database to be mirrored
.PARAMETER SourceServer
The name of the primary server
.PARAMETER SourcePath
Local Path for the backup
.PARAMETER DestServer
The name of mirror server
.PARAMETER DestPath
Local path for restore file
.EXAMPLE
PS C:\> Invoke-Mirror -database 'string value' 1
-SourceServer 'string\string' -SourcePath 'string' `
-DestServer 'string\string' -DestPath 'string'
.NOTES
AUTHOR: John P. Wood
CREATED: July, 2010
VERSION: 1.0.5
The SQL connections rely on Windows authentication and assumes Endpoints
already exist. Error checking is minimal (i.e. no check is made to
verify the recovery model is FULL).
#>
Param(
[Parameter(Mandatory=$true)]
[string]$database,
[string]$SourceServer='lcfsqlvs3\sqlvs3',
[string]$SourcePath='U:\SQL Backups',
[string]$DestServer='ldrsqlvs3\sqlvs3',
[string]$DestPath='U:\SQL Backups'
)
Set-StrictMode -Version 2
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Function Get-FileName {
Param([string]$path)
$names = $path.Split('\\')
$names[$names.Count - 1]
}
Function New-SMOconnection {
Param (
[string]$server
)
$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
$conn.applicationName = "PowerShell SMO"
$conn.StatementTimeout = 0
$conn.Connect()
if ($conn.IsOpen -eq $false) {
Throw "Could not connect to server $($server) for database backup of $($dbname)."
}
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
$smo
}
Function Invoke-SqlBackup {
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$dbbk.BackupSetDescription = "Full backup of " + $database
$dbbk.BackupSetName = $database + " Backup"
$dbbk.Database = $database
$dbbk.MediaDescription = "Disk"
$device = "$SourcePath\$bkpfile"
$dbbk.Devices.AddDevice($device, 'File')
$smo = New-SMOconnection -server $SourceServer
Try {
$dbbk.SqlBackup($smo)
$dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
$dbbk.SqlBackup($smo)
$smo.ConnectionContext.Disconnect()
}
Catch {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException)
{
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
};
continue
}
Finally {
if ($smo.ConnectionContext.IsOpen -eq $true) {
$smo.ConnectionContext.Disconnect()
}
}
}
Function Invoke-SqlRestore {
Param(
[string]$filename
)
# Get a new connection to the server
$smo = New-SMOconnection -server $DestServer
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($filename, "File")
# Get local paths to the Database and Log file locations
If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
Else { $DBPath = $smo.Settings.DefaultFile}
If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
Else { $DBLogPath = $smo.Settings.DefaultLog}
# Load up the Restore object settings
$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
$Restore.Action = 'Database'
$Restore.Database = $database
$Restore.ReplaceDatabase = $true
$Restore.NoRecovery = $true
$Restore.Devices.Add($backupDevice)
# Get information from the backup file
$RestoreDetails = $Restore.ReadBackupHeader($smo)
$DataFiles = $Restore.ReadFileList($smo)
# Restore all backup files
ForEach ($DataRow in $DataFiles) {
$LogicalName = $DataRow.LogicalName
$PhysicalName = Get-FileName -path $DataRow.PhysicalName
$RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$RestoreData.LogicalFileName = $LogicalName
if ($DataRow.Type -eq "D") {
# Restore Data file
$RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
}
Else {
# Restore Log file
$RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName
}
[Void]$Restore.RelocateFiles.Add($RestoreData)
}
Try {
$Restore.SqlRestore($smo)
# If there are two files, assume the next is a Log
if ($RestoreDetails.Rows.Count -gt 1) {
$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
$Restore.FileNumber = 2
$Restore.SqlRestore($smo)
}
$smo.ConnectionContext.Disconnect()
}
Catch {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException)
{
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
};
continue
}
Finally {
if ($smo.ConnectionContext.IsOpen -eq $true) {
$smo.ConnectionContext.Disconnect()
}
}
}
Function Set-Mirror {
Param([string]$server,[string]$database,[string]$partner)
$conn = "Server=$server; Integrated Security=SSPI; Database=Master"
$cn = New-Object "System.Data.SqlClient.SqlConnection" $conn
$cn.Open()
$cmd = New-Object "System.Data.SqlClient.SqlCommand"
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "ALTER DATABASE $database SET PARTNER = 'TCP://" + $partner + ":5022'"
$cmd.Connection = $cn
$cmd.ExecuteNonQuery()
$cn.Close()
Trap {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException)
{
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
};
continue
}
}
$srcUNC = Join-Path "\\$($SourceServer.Split('\\')[0])" $($SourcePath.Replace(':','$'))
if (-not(Test-Path $srcUNC)) { New-Item $srcUNC -ItemType directory | Out-Null}
$destUNC = Join-Path "\\$($DestServer.Split('\\')[0])" $($DestPath.Replace(':','$'))
if (-not(Test-Path $destUNC)) { New-Item $destUNC -ItemType directory | Out-Null}
$bkpfile = $($SourceServer.Replace("\", "$")) + "_" + $database + "_FULL_" + $(get-date -format yyyyMMdd-HHmmss) + ".bak"
Invoke-SqlBackup
Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose
Invoke-SqlRestore -filename $bkpfile
# Establish Mirroring from the mirrored database
Set-Mirror -server $DestServer -database $database -partner $($SourceServer.Split('\\')[0])
# Start the mirror
Set-Mirror -server $SourceServer -database $database -partner $($DestServer.Split('\\')[0])
Powershell is a fantastic tool to use for management of multiple computers. I have been slowly converting many of our administrative functions from a hodge-podge mixture of CMD, BAT, VPS, Python, and Perl scripts. One daily administrative talks is copying a variety of backups from a variety of Windows servers to our data ’warehouse’ where they are then copied to tape.
I recently found the “Jobs” cmdlets in Powershell V2.0. With “Jobs” you can asynchronously process multiple tasks (e.g. copying backups from many remote machines to data storage on the current local machine). In my case, running the copies synchronously results in the process spanning over to the next morning. Backups are stored daily. If a backup is taken on Wednesday, I can’t have it being stored in our warehouse under Thursday.
Continue reading ‘Asynchronous Processing using Powershell Jobs’ »