파워쉘 - SQL Server 관리

ps-sqlserver-management.ps1

# =====================================
# SQL Server를 제어하기 위한 어셈블리 로드
# =====================================
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

# =====================================
# SQL Server 버전
# =====================================
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "."
$ver = $srv.Version.Major

# =====================================
# SQL Server 접속 TCP 포트 변경
# =====================================
$SQLPORT = 포트번호
# 방화벽 등록
New-NetFirewallRule -DisplayName “SQLServer 포트” -Direction Inbound -Action Allow -Protocol TCP -LocalPort $SQLPORT
# 포트 변경
$wmiObj=Get-WmiObject -namespace "root\Microsoft\SqlServer\ComputerManagement$ver" -class "ServerNetworkProtocolProperty" -filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'"
$wmiObj.SetStringValue($SQLPORT)

# =====================================
# SQL Server 인증 모드를 혼합 모드로 변경
# =====================================
$ObjSmo= New-Object ("Microsoft.SqlServer.Management.Smo.Server") .
$ObjSmo.set_loginmode("mixed")
$objsmo.alter()

# =====================================
# SQL Server 재시작
# =====================================
# Agent를 재시작 하는 경우에는 ServiceName='SQLSERVERAGENT'
$sqlservice = Get-WmiObject -namespace "root\Microsoft\SqlServer\ComputerManagement$ver" -class "SqlService" -filter "ServiceName='MSSQLSERVER'"
$sqlservice.StopService()		# 서비스 중지
$sqlservice.StartService()		# 서비스 시작

# =====================================
# SQL Server Login 생성
# =====================================
$DBuser = "userid"
$DBpwd = "password"
$login = new-object Microsoft.SqlServer.Management.Smo.Login(".", $DBuser)
$login.LoginType = 'SqlLogin'
$login.PasswordPolicyEnforced = $false
$login.PasswordExpirationEnabled = $false
$login.Create($DBpwd)
$login.AddToRole('sysadmin')	# sysadmin role 부여

# =====================================
# SQL Server 서비스 상태 변경
# =====================================
<#
	SQL Server : name='MSSQLSERVER'
	Agent : name='SQLSERVERAGENT'
	ReportServer : name='ReportServer'
#>
$Netuser = "userid"
$Netpwd = "password"

$Svc = Get-WmiObject -namespace "root\CIMV2" -class "win32_service" -filter "name='MSSQLSERVER'"
if ($Svc.Status -eq 'OK')
{
	# 서비스의 시작 계정 변경
    $Svc.Change($null,$null,$null,$null,$null,$null,".\$Netuser","$Netpwd")
    # 모드 변경 (automatic:자동, disabled:사용안함)
    $Svc.changestartmode("automatic")
    # 서비스 시작
    $Svc.startservice()
    # 서비스 중지
    $Svc.stopservice()
}

# =====================================
# SQL Server DB 분리
# =====================================
$server = new-Object Microsoft.SqlServer.Management.Smo.Server(".")
$dbName = "dbname"
$server.DetachDatabase($dbName, $FALSE, $TRUE)

# =====================================
# SQL Server DB 생성
# =====================================
$db = "dbname"
$SQLDirPath = "d:\datapath\"
$server = new-Object Microsoft.SqlServer.Management.Smo.Server(".")
$DataBase = New-Object Microsoft.SqlServer.Management.Smo.Database($server, $db)
$FileGrowth = new-object Microsoft.SqlServer.Management.Smo.FileGroup($DataBase, "PRIMARY")
$DataBase.FileGroups.Add($FileGrowth)
$DataBaseDataFile = new-object Microsoft.SqlServer.Management.Smo.DataFile($FileGrowth, $db)
$FileGrowth.Files.Add($DataBaseDataFile)
$DataBaseDataFile.FileName = $SQLDirPath + $db + ".mdf"
$DataBaseLogFile = new-object Microsoft.SqlServer.Management.Smo.LogFile($DataBase, $db)
$DataBaseLogFile.FileName = $SQLDirPath + $db + ".ldf"
$DataBase.Create()

# =====================================
# SQL Server .bak 복원
# =====================================
$db = "dbname"
$bakFilePath = "d:\bak\db.bak"
$SQLDirPath = "d:\datapath\"
$res = new-object Microsoft.SqlServer.Management.Smo.Restore
$res.Devices.AddDevice($bakFilePath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$res.Database = $db
$res.NoRecovery = $FALSE
$res.ReplaceDatabase = $TRUE
$res.Action = "Database"
$res.PercentCompleteNotification = 20
# MDF
$resFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$resFile.LogicalFileName = $db
$resFile.PhysicalFileName = $SQLDirPath + $db + ".mdf"
$res.RelocateFiles.Add($resFile)
# LDF
$resLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$resLog.LogicalFileName = $db + "_log"
$resLog.PhysicalFileName = $SQLDirPath + $db + "_log.ldf"
$res.RelocateFiles.Add($resLog)
# 복원 실행
$res.SqlRestore($server)

# =====================================
# SQL Server 유지관리계획 JOB 등록
# =====================================
$db = "dbname"
# 1. 작업 등록
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
$jb = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $srv.JobServer, "jobname"
$jb.NetSendLevel = [Microsoft.SqlServer.Management.SMO.Agent.CompletionAction]::Always
$jb.Category = "Database Maintenance"
$jb.Create()
$jb.ApplyToTargetServer("(local)")

# 2. 작업에서 실행할 STEP 등록
$jbstp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $jb, "jobname_step"
# 실행 스크립트
$script = 
	@"
	-- 통계 업데이트
	USE [$db]
	GO
	EXEC sp_updatestats;
	GO
	-- FULL 백업
	declare @$db nvarchar(100)
	set @$db = N'$FullBackupPath$db-' + convert(nvarchar(20), getDate(), 112) + convert(varchar(2), datepart(hh, getdate())) + '.bak'
	BACKUP DATABASE [$db] TO  DISK = @$db WITH  NOINIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT
	-- 차등 백업
	declare @$db nvarchar(100)
	set @$db = N'$DiffBackupPath$db-' + convert(nvarchar(20), getDate(), 112) + convert(varchar(2), datepart(hh, getdate())) + '.bak'
	BACKUP DATABASE [$db] TO  DISK = @$db WITH  NOINIT, NOUNLOAD, DIFFERENTIAL, NOSKIP, STATS = 10, NOFORMAT
	"@
$jbstp.Command = $script
$jbstp.OnSuccessAction = [Microsoft.SqlServer.Management.SMO.Agent.StepCompletionAction]::QuitWithSuccess
$jbstp.OnFailAction =[Microsoft.SqlServer.Management.SMO.Agent.StepCompletionAction]::QuitWithFailure
$jbstp.Create()

# 3. 작업은 실행 할 스케줄 등록
$jbsch = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $jb, "jobname_schedule"
# Daily:일단위, Weekly:주단위
$jbsch.FrequencyTypes =  [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily
# Daily : 사용안함
# Weekly : 1=1주마다, 2=2주마다 ...
$jbsch.FrequencyRecurrenceFactor = 1
# Daily : 몇일마다 (1=매일)
# Weekly : 무슨요일에 (2=월요일)
$jbsch.FrequencyInterval = 2
# Once : 일에 한번 만 실행
# Hour : 시간 단위로
$jbsch.FrequencySubDayTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Once
# Hour인 경우 시간 단위 (1=한시간 간격)
$jbsch.FrequencySubDayInterval = 1
# 실행 시작 시각 : 시,분,초 (6,0,0 = 오전 6시 정각)
$jbsch.ActiveStartTimeOfDay = New-Object -TypeName TimeSpan -argumentlist 6, 0, 0      # 오전 6시에
# Hour인 경우 실행 종료 시각 : 시, 분, 초
$jbsch.ActiveEndTimeOfDay = New-Object -TypeName TimeSpan -argumentlist 22, 0, 0
$jbsch.Create()