Day: February 7, 2013

Restore RedGate Backups using Powershell

This actually iterates a list of folders in a directory, grabs the latest backup from each folder, restores it and then does an integrity check on said restored database.  Unfortuantely, RedGate has no cmdlets for doing this, so it’s strictly passing sql strings into Invoke-sqlcmd based.  This also checks the filelist in the backups and will move the data/log files to a location that you specify.  Use at your own risk.

function RestoreDatabase([string]$servername, [string]$databaseName, [string]$backupFile, [string]$mdfFile, [string]$ldfFile)
{
    try
    {
        $restoreCmd = "
            EXECUTE master..sqlbackup 
            '-SQL `"RESTORE DATABASE [$($databaseName)] 
            FROM DISK = ''$($backupFile)'' 
            WITH RECOVERY, 
            DISCONNECT_EXISTING,  
            REPLACE,
        "
    
        $cmd = "EXECUTE master..sqlbackup '-SQL `"RESTORE FILELISTONLY FROM DISK = [$backupFile]`" '"
        $dataFiles = Invoke-Sqlcmd -ServerInstance $srv -Database 'master' -Query $cmd
        
        foreach($dataFile in $dataFiles)
        {
            if($dataFile.Type -eq "D")
            {
                $move = "MOVE ''$($dataFile.LogicalName)'' TO ''$($mdfFile)" + $dataFile.PhysicalName.Substring($dataFile.PhysicalName.LastIndexOf("\")+1) + "'',"
            }
            if($dataFile.Type -eq "L")
            {
                $move = "MOVE ''$($dataFile.LogicalName)'' TO ''$($ldfFile)" + $dataFile.PhysicalName.Substring($dataFile.PhysicalName.LastIndexOf("\")+1) + "'',"
            }
            $restoreCmd += $move
            $move = ""
        }
        
        $restoreCmd = $restoreCmd.substring(0, $restoreCmd.length-1) + "`"'" 
        Invoke-sqlcmd -serverinstance $srv -database 'master' -query $restoreCmd -QueryTimeout 0 
        
        $sqlout = sqlcmd -E -S $srv -d $databaseName -Q "dbcc checkdb WITH NO_INFOMSGS"
        
        if($sqlout -ne $null)
        {
            Send-MailMessage -SmtpServer $mailServer -To $to -Subject "Database Consistancy Check Failure for $($databaseName)" -Body $sqlout.ToString() -From $from
        }
    }
    catch
    {
        $err = $error[0]|format-list -force  
        send-mailmessage -smtpserver $mailServer -to $to -from $from -subject 'Error in database consistancy checks on $srv' -body $err
        write-host $err
    }

	
}

function Main()
{
    foreach($dir in (gci -path $backupsDir | where{$_.PSIsContainer}))
    {
        $file = gci -path $dir.fullName | where{$_.Name -like "FULL_*" -and $_.Extension -eq ".sqb"} | sort CreationTime -descending | SELECT -first 1
        
        write-host "Restoring database $($dir.name)  --  " (Get-Date -Format "MM/dd/yyyy HH:mm:ss").ToString()
        RestoreDatabase $srv $dir.Name $file.FullName "c:\SQLData\" "c:\SQLLogs\"
        write-host "Finsihed restoring database $($dir.Name)" (Get-Date -Format "MM/dd/yyyy HH:mm:ss").ToString()
    }
}

import-module sqlps -DisableNameChecking

$mailServer = 'mail.yourdomain.com'
$to = 'you@yourdomain.com'
$from = 'donotreply@yourdomain.com'
$backupsDir = "c:\SQLData\Backups"
$srv = "ServerInstance"

main

Remove Sql Login using Powershell

Quick script to remove a login from sql server using powershell.  This will iterate through the databases and the users in said databases to remove the users from them before dropping the login itself.  This uses sqlpsx.  Use at your own risk.

Import-Module SqlServer
$srv = "ServerInstance"
$loginName = "LoginName"

$login = Get-SqlLogin -sqlserver $srv | where{$_.Name -eq $loginName}
if($login -eq $null)
{
	return;
}

$dbs = Get-SqlDatabase -sqlserver $srv -force
foreach($db in $dbs)
{
	$logins = $db.EnumLoginMappings();
	foreach($dbLogin in $logins)
	{	
		#Write-Host $dbLogin.LoginName
		if($dbLogin.LoginName -eq $loginName)
		{
			Remove-SqlUser -dbname $db.Name -sqlserver $srv -name $dbLogin.LoginName
		}
	}
}
Remove-SqlLogin -sqlserver $srv -name $loginName