Posts

Showing posts from May, 2021

BACKUP LOG Terminating abnormally, MSG 3013, LEVEL 16, STATE 1, LINE 4

Image
I got the below error when we trying to take the transaction log backup of model databases. So as a part of solution, we change the recovery model of the model database from FULL to SIMPLE and then immediately changed it from SIMPLE to FULL. This resolved the issue. There is also a possibility that the below commands might not work in all cases. In that situation RESTART SQL server instance. This worked in my environments. USE [master] GO ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT GO USE [master] GO ALTER DATABASE [model] SET RECOVERY FULL WITH NO_WAIT GO

Add-DBADbRolemember,dbatools

The below command will create a new user at database level and add that user to the required roles. cls $UserName = 'UserNameHere' $InstanceName = 'InstanceNameHere' New-DbaDbUser -SqlInstance $InstanceName -Database DatbaseNameHere -Username $UserName Add-DbaDbRoleMember -SqlInstance $InstanceName ` -Database DatabaseNameHere ` -Role "db_ddladmin" , "db_executor" , "db_datawriter" , "db_datareader" , "db_spexec" ` -User $UserName -Verbose

Get-DbaAgentJobHistory,dbatools

The below powershell command will give the information about SQL Server agent job and its status. cls Get-DbaAgentJobHistory ` -SqlInstance ServerNameHere ` -Job 'Job Name Here' ` -WithOutputFile| Where-Object {( $_ .Rundate -like "*05/06/2021*" )} -Verbose| Sort-Object Rundate -Descending| Format-Table ComputerName,SQLInstance,Job,RunDate,Status,Stepid,Message -Wrap

New-DbaLogin,dbatools

The below command will create an SQL Server authentication login on both the servers at a time. Once you run the command a pop window ask for password, then provide the password.  cls New-DbaLogin ` -SqlInstance SERVER1,SERVER2 ` -Login LoginNameHere -Verbose #Handling Windows login. The main thing we #need to remember here is DOMAIN name. cls New-DbaLogin ` -SqlInstance Server1 ` -Login DomainName\Login1,DomainName\Login2 -Verbose