SELECT*

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

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 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

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.