SELECT*

 The below command will change the directory from one drive to another drive and one folder to another folder in a different drive. Here CD /D is common for all the commands.

One day my manager came me to and asked me that there is a errors when we run integrity checks of database . We need to fix that through REPAIR_ALLOW_DATA_LOSS option. So what i did was I have taken downtime and ran the below query very immediately. Then database has gone to Single User mode and stuck there and immediately thrown a DEADLOCK ERROR .   I would have run this command without GO statement. So I thought I would bring the database to  MULTI_USER at least, even that is not happening and showing the same DEADLOCK error again and again, then I have run the 2nd 3rd queries to bring the database to normal stage and re run the script to mitigate consistency errors. But DONT RUN the first below query with GO command in the middle of each sentence. USE master ; ALTER DATABASE [DatbaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; GO DBCC CHECKDB ( 'DatabaseName' , REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS ; GO ALTER DATABASE [DatabseName] SET MULTI_USER ; So ...

 The below script automatically take the Transaction log backup of respective database and will automatically shrink the log file of that particular database to 2GB. But the limitation is,this script works when the database has single log file, it may not work if your database has two transaction log files(.ldf)   This script generates print commands, so while executing put those print commands in comment and uncomment EXEC commands below to execute. DECLARE @ dbName VARCHAR ( 50 ), @ log_reuse_wait_desc VARCHAR ( 50 ), @ path varchar ( 1000 ), @ DateTime varchar ( 50 ), @ stmt VARCHAR ( 4000 ), @ shrink VARCHAR ( 4000 ), @ logfileName VARCHAR ( 100 ), @ DevicePath varchar ( 1000 ) SET @ DateTime = CONVERT ( VARCHAR ( 100 ),getdate(), 113 ) SET @ DateTime = REPLACE ( REPLACE ( @ DateTime, ':' , '_' ), ' ' , '_' ) /* Provide database name and path here */ SET @ dbName = 'test' SELECT @ DevicePath = DeviePath from ( SELECT TOP...

 The below query will retrieve SQL Server instance info from multiple servers. The below PowerShell command only works when we install "sqlserver" module. If any columns has lengthy values please provide that column in the last Install-Module sqlserver Import-Module sqlserver cls $ServerList = @ ( "Server1" , "Server2" ) $ServerList | ForEach -Object{ $SingInstance = $_ $list = @ {Name= "WindowsServer" ; Expression={ $SingInstance .( $_ .Name)}},( Get-ChildItem -Path "SQLSERVER:\SQL\$_" ) $list }| Format-Table @ {Name= "WindowServerName" ; Expression={ $_ .ComputerNamePhysicalNetBIOS}},Status,State,ServerVersion, @ {Name= "InstanceID" ; Expression={ $_ .ServiceInstanceId} ` },ServiceName, @ {Name= "ServiceMode" ; Expression={ $_ .ServiceStartMode}},Product,ProductLevel,Edition,DisplayName,ServiceAccount -AutoSize -Wrap #,ClusterName,AvailabilityGroups,,DatabaseEngineEdition

 Some times we could see that USERS of the database would be in DISABLED mode after restoring the database from the .BAK file though their corresponding logins are in place with appropriate permissions. If you face an issue like this run the below command to bring the users into ENABLED state. USE DatabaseNameHere GO GRANT CONNECT TO [DomainName \ Name]

  Restoring database using dbatools-Restore-DbaDatabase cls Restore-DbaDatabase ` -SqlInstance InstanceNamehere ` -Path C : \Temp\BakFileNameHere.bak ` -DatabaseName RestoreDatabaseNamehere ` -DestinationDataDirectory F : \MSSQL12.MSSQLSERVER\MSSQL\DATA\DatabaseData_Data.mdf ` -DestinationLogDirectory G : \LOGs\DatabaseName_Log.ldf ` -Verbose