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’ »
Archive for the ‘SQL Server’ Category
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])
Don’t you just hate it when you open one of your server logs in Management Studio and then you have to wait forever to review it because it never stops reading? I’ve done it enough times and decided to put an end to it or at least greatly reduce the incidence.
Using Powershell and a simple script to execute Sp_Cycle_Errorlog, I now have a weekly, Windows scheduled task which executes the sproc to cycle the logs for all my SQL Servers.
foreach ($svr in get-content D:\Scripts\Servers.TXT ){
$svr
Invoke-Expression 'SQLCMD -E -S $svr -Q "Exec Sp_Cycle_Errorlog"' | Out-Null
}
First off, I need to give recognition to Joel Mansford for his blog which gave me the answer to my problem.
Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!?
After creating a linked server on a 64bit SQL Server 2005 instance, I then needed to create a view which referenced the linked server. The linked server is a SQL Server 2000 32bit instance. However, when I tried to create the view, I got this error: Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI”.
This really proved to be a conundrum because I had just recently created the same linked server and view on another SQL 2005 instance. However, I had not yet connected the fact the 2005 instance was 32bit as opposed to the one I was now working on, which is 64bit.
I wrestled with it being a permissions issue for a while but ruled that out when I could not create the view even while using a domain admin account having authority of all the instances. A search on Google, using the above mentioned error message, turned up Joel’s blog. His specific problem was relative to SQL Server 7 but the answer applied to SQL Server 2000 also. He had found the original post of the ultimate solution and was good enough to include it in his blog which I have an excerpt here.
I found an extremely helpful post by Marek Adamczuk at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1. Marek explains the 32-bit editions are missing a stored procedure on the master database called sp_tables_info_rowset_64.
Marek’s solution is to create a wrapper on the master database as follows (his code not mine):
create procedure sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null as declare @Result int set @Result = 0 exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
It would appear the existing sproc does the same thing but just needs to be referenced using the 64bit name. Kudos to both Joel and to Marek for making this solution available!