Posts

Showing posts from February, 2020

OLAHALLENGREN OUTPUT FILE PATH INFO

The below parameter you need to change in the Ola script and Execute his maintenance script. SET @OutputFileDirectory = 'G:\Backups\Siva'  This path will be automatically generated by Ola script. G:\Backups\Siva\DatabaseBackup_FULL_ $(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt The below powershell query will change teh output file path of FULL,DIFF,LOG,INDEX and INTEGRITY check backups. Backup jobs are created by Ollahallengren script. cls $InstanceName = 'RAMESH\PROD' $FullPath = 'D:\ramesh\mahesh\USER_DATABASES_FULL_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt' $DIFFPath = 'D:\ramesh\mahesh\USER_DATABASES_DIFF_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt' $LOGPath = 'D:\ramesh\mahesh\USER_DATABASES_LOG_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_S...

Creating database mail with DBA TOOLS powershell script

The below query will configure database profile and account on multiple sql server named instance. cls $instanceNames = @( "defaultinstance" , "named\instance1" , "named\instance2" ) $instanceNames | ForEach-Object { $InstanceName = $_ New-DbaDbMailAccount ` -SQLinstance $InstanceName ` -Name "DBHealth" ` -DisplayName "AOAG Health Failed" ` -Description "DatbaseHealthCheck" ` -EmailAddress "support@sql.com" ` -ReplyToAddress "support@sql.com" ` -MailServer "mailserveramehere" ` -Force New-DbaDbMailProfile ` -SqlInstance $InstanceName ` -MailAccountName "DBHealth" ` -Name "DBHealthProfile" ` }

Creating SQL Server agent job step in SQL Server through Powershell.

# creating sql server agent step in sql server through powershell. cls Import-Module sqlps -DisableNameChecking $instanceName = "RAMESH" #Provide sql server named instance or default instance here $server = New-Object ` -TypeName Microsoft.SqlServer.Management.Smo.Server ` -ArgumentList $instanceName $getJobs = $server .JobServer.Jobs $getJobs | ForEach -Object{ $IndividualJob = $_ #now let's add a simple T-SQL Job Step $jobStep = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep( $IndividualJob , "TestStep" ) #TestStep is a step name. $jobStep .Subsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem] :: TransactSql $result = $jobStep .Command = "SELECT GETDATE()" $jobStep .OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction] :: GoToNextStep $jobStep .OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction] :: QuitWithFailure $jobStep .ID=1 $jobStep .Create() } x...

msdb database is in Recovery Pending state.

Image
Suddenly one of my SQL Server named instances system database named msdb database gone into Recovery Pending state as shown in the below image. Don't worry just check the data and log file of the database  by running SELECT * FROM sys.master_files, if you can find the data and log file of the databases are in same path , your job would be very easy , just run the below command. use master go ALTER DATABASE msdb SET ONLINE The above command will bring the database online.