paschott

--Create table to store posts
CREATE TABLE [dbo].[Posts](
	[Username] [nvarchar](255) NULL,
	[Type] [nvarchar](255) NULL,
	[Subtype] [nvarchar](255) NULL,
	[TS] [nvarchar](255) NULL,
	[Channel] [nvarchar](255) NOT NULL,
	[MessageText] [nvarchar](max) NULL,
	[MessageDate] [datetime2](7) NULL,
	[PostID] [bigint] IDENTITY(1,1) NOT NULL,

paschott

--replace the URL in the function with the appropriate URL for your file
SELECT TOP 1000 *
FROM sys.fn_get_audit_file('https://subscription.blob.core.windows.net/paths/auditfile.xel', default, default)
WHERE (event_time <= '2019-02-20T21:56:36.631Z')
/* additional WHERE clause conditions/filters can be added here */
and action_id IN ('DBAF') --look specifically for auth failures
ORDER BY event_time DESC

paschott

DECLARE @NewDataFileGrowth varchar(100) = '500MB'
DECLARE @NewLogGrowth varchar(100) = '100MB'

select 
  CASE WHEN physical_name like '%.ldf'
    THEN 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewLogGrowth + ')'
    ELSE 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewDataFileGrowth + ')'
  END as ChangeAutoGrowSettings, 
  DB_NAME(mf.database_id) database_name, 
  mf.name logical_name, 

paschott

#PowerShell script
################################
########## PARAMETERS ##########
################################ 
$projectFolder = "C:\SSIS\myProject\myProject"
$dtutilPath = "C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtutil.exe"
# The number changes per SQL Server version
# 130=2016, 120=2014, 110=2012
# Also check the drive where SQL Server is
# installed

paschott

Import-Module dbatools

$scripts = get-childitem "C:\ScriptsToRun\" -Filter *.sql | sort-object Name
$servers = "servername"
$database = "databasename"
    
foreach ($script in $scripts) {
    $OutputFile = $script.directoryname + "\" + $script.basename + ".txt"
    Write-Host $OutputFile
    Invoke-DbaQuery -SqlInstance $servers -File $script.FullName -Database $database -MessagesToOutput | Out-File -FilePath $OutputFile

paschott

--Adapted from http://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable
--If this is an existing table, do not drop/recreate the table, just adjust dates and run the appropriate population script
--Adjust code to ignore dates already in the calendar table.


SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET ARITHIGNORE ON

paschott

SELECT DISTINCT
   CAST(SERVERPROPERTY('ServerName') as nvarchar(100)) AS Principal,
   m.mirroring_partner_instance AS Mirror,
   DB_NAME(m.database_id) AS DatabaseName,
   SUM(f.size*8/1024) AS DatabaseSize,
   CASE m.mirroring_safety_level
      WHEN 1 THEN 'HIGH PERFORMANCE'
      WHEN 2 THEN 'HIGH SAFETY'
   END AS 'OperatingMode',
   RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port

paschott

--TableName:Backup_MostRecent
;with
db as (
	select [Instance] = @@SERVERNAME,
		[Database]  = name,
	    [RecoveryMode]   = DATABASEPROPERTYEX(name, 'Recovery'),
	    [CreationTime]   = crdate,
	    [Status]         = DATABASEPROPERTYEX(name, 'Status')
		from master..sysdatabases
		where name!='tempdb'

paschott

DECLARE @Time TIME,
        @RowsToDelete INT,
        @PurgeDataPriorToDate DATETIME2,
        @RowsDeleted INT,
        @EndLoopTime TIME,
        @StartLoopTime TIME
SELECT @RowsToDelete = 5000
SELECT @EndLoopTime = '20:00:00'
SELECT @StartLoopTime = '08:00:00'

paschott

#Note - will want to install "Chocolatey" first in order to use this file to install software.
# https://chocolatey.org/docs/installation
# Open a command prompt (cmd) and run the following:
#    @"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"
# ..
# or open a PowerShell Admin prompt and run:
#  Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))


# After Chocolatey is installed, open a PS prompt in Administrator mode,