Skip to content

Instantly share code, notes, and snippets.

View Stuart-Moore's full-sized avatar

Stuart Moore Stuart-Moore

View GitHub Profile
@Stuart-Moore
Stuart-Moore / backup_default_filename.ps1
Last active April 30, 2020 08:42
backup_default_filename.ps1
View backup_default_filename.ps1
DatabaseName_yyyyMMddHHmm-x-of-y
@Stuart-Moore
Stuart-Moore / dbaSecurityScanFixExamples.ps1
Created April 18, 2020 16:05
examples of using dbaSecurityScan to bring SQL Server database security back inline with a baseline config
View dbaSecurityScanFixExamples.ps1
# This is based on the module state on 18th April 2020, it will have moved on since then!
Import-module dbaSecurityScan
# Create config and test against a new database
$config = New-DssConfig -SqlInstance server1\sql2017 -Database db1
$config | ConvertTo-Json -Depth 5 | Out-File \\file\store\db1-SecurityConfig.json
# time passes and we want to check for security drift
# hydrate the config back into an object
View Copy-DbaDatabse-azmi.ps1
Copy-DbaDatabase -Source server1 -Destination MyNewMI.public.cus29s972e4513d6.database.windows.net,3342 -DestinationCredential $cred -SharedPath https://azblogdemo.blob.core.windows.net/sql -BackupRestore
@Stuart-Moore
Stuart-Moore / AzureBlob.ps1
Created April 5, 2019 13:44
Setting up an Azure Blob container for SQL Server Backups and Restores
View AzureBlob.ps1
Import-Module dbatools
Import-Module Az
Connect-AzAccount
# Currently returns the wrong URL, use https://aka.ms/DeviceLogon instead
#Check you're in the correct subscription
Get-AzContext
#check existing Storage Accounts
@Stuart-Moore
Stuart-Moore / Convert-LSN.ps1
Last active November 8, 2018 11:35
powershell function to convert SQL Server LSNs from hex to numeric or numeric to he
View Convert-LSN.ps1
function Convert-LSN {
<#
.SYNOPSIS
Converts Lsns betweent Hex and/or numeric formats
.DESCRIPTION
Function takes an LSN in either split Hexadecimal format () or numberic
It then returns both formats in an object
@Stuart-Moore
Stuart-Moore / RebaseStriped.ps1
Created November 25, 2017 23:00
Rebasing Striped backups
View RebaseStriped.ps1
$BackupHistory = Get-DbaBackupHistory -SqlInstance localhost\sqlexpress2016 -Database RestoreTimeStripe
$BackupHistory | ForEach {$_.FullName = $_.FullName |
ForEach {
($_ -replace 'c:\\dbatools\\RestoreTimeStripe\\stripe1','\\new\stripeA') -replace 'c:\\dbatools\\RestoreTimeStripe\\stripe2', '\\old\StripeB'
}
}
@Stuart-Moore
Stuart-Moore / ParallelScansMultipleServers.ps1
Last active November 25, 2017 23:16
Parallel header scans on multiple Servers
View ParallelScansMultipleServers.ps1
Import-Module PoshRsJob
$servers = ('localhost\sqlexpress2016','localhost\developer2016')
$folders = ('\\localhost\dbatools$\RestoreTimeClean','\\localhost\dbatools$\RestoreTimeDiffDemo','\\localhost\dbatools$\restoretimeDiff')
$InputObject=@()
$Counter = 0
ForEach ($folder in $folders){
$InputObject += [PSCustomObject]@{
ServerInstance = $servers[$Counter%($servers.count)]
@Stuart-Moore
Stuart-Moore / ParallelHeaderScan.ps1
Created November 25, 2017 21:57
Parallel Header scans on single server
View ParallelHeaderScan.ps1
Import-Module poshrsjob
$folders = ('C:\dbatools\RestoreTimeClean','C:\dbatools\RestoreTimeDiffDemo','c:\dbatools\restoretimeDiff')
$job = $Folders | Start-RsJob -ModulesToImport dbatools -ScriptBlock {
param($Folder)
Get-DbaBackupInformation -SqlInstance localhost\SqlExpress2016 -Path $Folder
}
$job | Wait-RsJob -ShowProgress
@Stuart-Moore
Stuart-Moore / ScanOverTime.ps1
Created November 25, 2017 20:39
Time spread Backup Scans
View ScanOverTime.ps1
#First Scan:
Get-DbaBackupInformation -SqlInstance Server1\Instance1 -Path c:\backups -ExportPath c:\BackupInformation.xml
#Some time later:
$BackupInformation = Get-DbaBackupInformation -Import - Path c:\BackupInformation.xml
$LastScanTime = (Get-Item c:\BackupInformation.xml).LastWriteTime
$BackupInformation += Get-ChildItem c:\backups | Where-Object {$_.LastWriteTime -ge $LastScanTime} | Get-DbaBackupInformation -SqlInstance Server1\Instance1
$BackupInformation | Export-CliXml -Path C:\BackupInformation.xml
@Stuart-Moore
Stuart-Moore / MultiEnvironment.ps1
Created November 24, 2017 11:15
Create multiple environments
View MultiEnvironment.ps1
$BackupInformation = Get-DbaBackupInformation -sqlinstance localhost\sqlexpress2016 -Path C:\dbatools\RestoreTimeDiffDemo
$Environments = @('Dev','Test','Stage')
ForEach ($Environment in $Environments){
$BackupInformation | Restore-DbaDatabase -sqlinstance localhost\sqlexpress2016 -DestinationFilePrefix $Environment -RestoredDatababaseNamePrefix ("$($Environment)_") -TrustDbBackupHistory
}