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’ »
иконографияиконикухненски маси
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
Beware of a new default when installing Oracle 11g! This default can raise its ugly head and bite you in the ass! It just happened to me.
The default has to do with enforcing a password expiration when creating a user. I don’t know about you but we generally have applications (Oracle based as well as SQL Server) which rely on user logins specific for that application. These applications also have configurations in which you specify the user login, sometimes in several locations. Users and application managers come and go and along with them the knowledge of application configurations and where they are. Consequently, we do not change application passwords! As soon as you do, applications break and it could take hours or even days to get them working again.
With this in mind, you might seriously think about changing the Oracle 11g default policy as one of your steps immediately after installation. I did not change the policy as I was unaware of it. I had several user logins become disabled duet to expiring passwords. These also included SYSTEM and DBSNMP! Talk about a real PITA!
Here’s how you can change the password policy:
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED;
Oh! And by-the-way, if the password does expire, you cannot unexpire it. You are forced to supply a new password even if it is the same as the old one. Unfortunately in my case, the application was installed by a third party and they were not sure of the password.
del.icio.us Tags: Oracle 11g installation user login default policy passord expire
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])