MS SQL Server

 Here is the script.

DECLARE @name VARCHAR(50) -- database name  

DECLARE @path VARCHAR(256) -- path for backup files  

DECLARE @fileName VARCHAR(256) -- filename for backup  

DECLARE @fileDate VARCHAR(20) -- used for file name  

DECLARE @DeleteBakDate DATETIME = Dateadd(day, -5, Getdate()); -- 5-day retention for .bak files

DECLARE @DeleteTxtDate DATETIME = Dateadd(day, -30, Getdate()); -- 30-day retention for .txt files

DECLARE @outputFile VARCHAR(256) -- output log file

-- specify database backup directory

SET @path = '\\servername\foldername\'

-- specify filename format for backup

SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) + '_' + Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')

-- specify output file for job with date format

SET @outputFile = @path + 'Backup_Job_Output_' + @fileDate + '.txt'

DECLARE db_cursor CURSOR read_only FOR  

  SELECT NAME  

  FROM master.dbo.sysdatabases  

  WHERE NAME NOT IN ('master', 'model', 'msdb', 'tempdb') -- exclude these databases

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  

  BEGIN  

      SET @fileName = @path + @name + '_' + @fileDate + '.bak'  

      -- Backup database and capture the output using xp_cmdshell

      DECLARE @cmd VARCHAR(1024)

      SET @cmd = 'ECHO Backing up database ' + @name + ' to ' + @fileName + ' >> ' + @outputFile

      EXEC xp_cmdshell @cmd

      BACKUP DATABASE @name TO DISK = @fileName  

      WITH copy_only, noformat, noinit, skip, norewind, nounload, compression,

           STATS = 10

      -- Log the backup operation result to the output file

      SET @cmd = 'ECHO Backup of database ' + @name + ' completed successfully to ' + @fileName + ' >> ' + @outputFile

      EXEC xp_cmdshell @cmd

      FETCH NEXT FROM db_cursor INTO @name  

  END  

-- Delete old .bak files (older than 5 days)

EXEC master.sys.Xp_delete_file 0, @path, 'BAK', @DeleteBakDate, 0

-- Delete old .txt files (older than 30 days)

EXEC master.sys.Xp_delete_file 0, @path, 'TXT', @DeleteTxtDate, 0

CLOSE db_cursor  

DEALLOCATE db_cursor  

-- Final log entry

DECLARE @cmdFinal VARCHAR(1024)

SET @cmdFinal = 'ECHO Backup process completed at ' + CONVERT(VARCHAR(20), GETDATE(), 120) + ' >> ' + @outputFile

EXEC xp_cmdshell @cmdFinal

 Try this script 

======

DECLARE @BackupPath NVARCHAR(500) = 'D:\Backups\' -- Change this to your desired path

DECLARE @BackupRetentionDays INT = 10

DECLARE @LogRetentionDays INT = 30

DECLARE @CurrentDate DATETIME = GETDATE()

DECLARE @LogPath NVARCHAR(500) = @BackupPath + 'Logs\'

DECLARE @FileName NVARCHAR(500)

DECLARE @DatabaseName NVARCHAR(128)

DECLARE @LogMessage NVARCHAR(MAX)

DECLARE @BackupDeleteDate DATETIME = DATEADD(DAY, -@BackupRetentionDays, @CurrentDate)

DECLARE @LogDeleteDate DATETIME = DATEADD(DAY, -@LogRetentionDays, @CurrentDate)

-- Create directories if they don't exist

DECLARE @CreateDirSQL NVARCHAR(1000)

SET @CreateDirSQL = 'xp_cmdshell ''if not exist "' + @BackupPath + '" mkdir "' + @BackupPath + '"'''

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

EXEC(@CreateDirSQL)

EXEC('xp_cmdshell ''if not exist "' + @LogPath + '" mkdir "' + @LogPath + '"''')

-- Log file setup

DECLARE @LogFile NVARCHAR(500) = @LogPath + 'BackupLog_' + 

                                REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), @CurrentDate, 120), ':', ''), '-', ''), ' ', '_') + '.txt'

-- Initialize log

SET @LogMessage = 'Backup process started at: ' + CONVERT(NVARCHAR(30), @CurrentDate, 120) + CHAR(13) + CHAR(10)

EXEC master.dbo.xp_cmdshell 'echo %LogMessage% > "' + @LogFile + '"', no_output

-- Cursor to iterate through all user databases

DECLARE db_cursor CURSOR FOR 

SELECT name 

FROM sys.databases 

WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') 

AND state = 0 -- Online databases only

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

    BEGIN TRY

        -- Generate backup file name

        SET @FileName = @BackupPath + @DatabaseName + '_Full_' + 

                       REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), @CurrentDate, 120), ':', ''), '-', ''), ' ', '_') + '.bak'

        -- Backup command

        BACKUP DATABASE @DatabaseName 

        TO DISK = @FileName

        WITH INIT, STATS = 5, COMPRESSION

        -- Log success

        SET @LogMessage = 'SUCCESS: Backup completed for database: ' + @DatabaseName + ' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

        EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

    END TRY

    BEGIN CATCH

        -- Log error

        SET @LogMessage = 'ERROR: Failed to backup database: ' + @DatabaseName + ' - ' + ERROR_MESSAGE() + ' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

        EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

    END CATCH

    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

CLOSE db_cursor

DEALLOCATE db_cursor

-- Cleanup old backup files (older than backup retention period)

BEGIN TRY

    DECLARE @DeleteBackupCmd NVARCHAR(1000)

    SET @DeleteBackupCmd = 'forfiles /p "' + @BackupPath + '" /s /m *.bak /d -' + CAST(@BackupRetentionDays AS NVARCHAR(3)) + ' /c "cmd /c del /q @path"'

    EXEC xp_cmdshell @DeleteBackupCmd

    SET @LogMessage = 'Cleanup: Old backup files deleted (older than ' + CAST(@BackupRetentionDays AS NVARCHAR(3)) + ' days)' + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END TRY

BEGIN CATCH

    SET @LogMessage = 'ERROR during backup cleanup: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END CATCH

-- Cleanup old log files (older than log retention period)

BEGIN TRY

    DECLARE @DeleteLogCmd NVARCHAR(1000)

    SET @DeleteLogCmd = 'forfiles /p "' + @LogPath + '" /s /m *.txt /d -' + CAST(@LogRetentionDays AS NVARCHAR(3)) + ' /c "cmd /c del /q @path"'

    EXEC xp_cmdshell @DeleteLogCmd

    SET @LogMessage = 'Cleanup: Old log files deleted (older than ' + CAST(@LogRetentionDays AS NVARCHAR(3)) + ' days)' + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END TRY

BEGIN CATCH

    SET @LogMessage = 'ERROR during log cleanup: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END CATCH

-- Final log entry

SET @LogMessage = 'Backup process completed at: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

-- Reset xp_cmdshell for security

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

PRINT 'Backup process completed. Check log file at: ' + @LogFile

======

This blog post will break down the provided PowerShell code, Understanding when and why your server rebooted is crucial for troubleshooting performance issues, security incidents, and overall system health. PowerShell, a powerful scripting language, provides a convenient way to extract this information from the Windows Event Log. This blog post will dissect the following PowerShell command and explain how it can be used to uncover your server’s reboot history:

[code type="SQL"] Get-WinEvent -FilterHashtable @{ LogName = 'System' Id = @(6005, 6006, 1074) StartTime = (Get-Date).AddDays(-60) # Last 60 days EndTime = Get-Date } -MaxEvents 50 | # Recent 50 messages Select-Object TimeCreated, Id, Message[/code]

Here are the results looks like: 

Understanding the Results:

Executing this command will output a list of events related to system shutdowns and restarts within the last 60 days. Each entry will display the event time, event ID, and a brief description of the event.

 Here’s a fantastic PowerShell script that can simplify our daily tasks by helping us search for events in the Windows Event Viewer. To use this script, open PowerShell ISE and run it. The output will be generated in the specified location. In this example, we’ve chosen the C:\Results folder, but feel free to customize it according to your requirements. You can also add or remove event IDs as needed. The script will create a CSV file that we can review

Code shown as follows:

Above PowerShell code will get generated output file in the C:\Results folder

Here is another good PowerShell script that can make a SQL Admin’s life easier. This script can be used to check the health of SQL servers and retrieve information about them. The collected data will be saved in both CSV and HTML formats. Additionally, an error log file is generated, allowing you to manually review any failed servers.

To implement this, follow these steps:

1. Create a folder named “CommandTree” in the C:\ drive of the server.
2. Place the “ServerList.txt” and “TsqlCommand.sql” files in the CommandTree folder.
3. The PowerShell script will connect to each server listed in the ServerList.txt file and execute the T-SQL command provided in the TsqlCommand.sql file.
4. The output files (CSV and HTML formats) will be generated in the same location. 

[code type="SQL"] # Define paths on WINSRVR1 $basePath = "C:\commandTree" $sqlCommandPath = "$basePath\tsqlcommand.sql" $serverListPath = "$basePath\serverlist.txt" $logPath = "$basePath\connectionlog.txt" # Renamed from errorLogPath to logPath $consolidatedCsvPath = "$basePath\consolidated_results.csv" $consolidatedHtmlPath = "$basePath\consolidated_results.html" # Function to log messages function Write-Log { param ( [string]$Message, [string]$Status ) $logMessage = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - $Status - $Message" Add-Content -Path $logPath -Value $logMessage if ($Status -eq "Error") { Write-Host $logMessage -ForegroundColor Red } else { Write-Host $logMessage -ForegroundColor Green } } # Function to connect, execute query, and return results function Get-SqlQueryResults { param( [string] $ServerInstance ) try { # Read SQL command $sql = Get-Content $sqlCommandPath -Raw # Execute query and capture results $results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $sql # Add a column for the server name $results | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $ServerInstance -Force Write-Log -Message "Successfully processed $ServerInstance" -Status "Success" return $results } catch { $errorMessage = $_.Exception.Message Write-Log -Message "Error processing $($ServerInstance): $errorMessage" -Status "Error" return $null } } # Main script execution try { # Check if required files exist if (-not (Test-Path $sqlCommandPath) -or -not (Test-Path $serverListPath)) { throw "Required files not found. Please ensure tsqlcommand.sql and serverlist.txt exist in $basePath" } # Read server list $servers = Get-Content $serverListPath # Initialize an array to store all results $allResults = @() # Process each server foreach ($server in $servers) { Write-Host "Processing $server..." -ForegroundColor Cyan $serverResults = Get-SqlQueryResults -ServerInstance $server if ($serverResults) { $allResults += $serverResults } } # Save consolidated results to CSV if ($allResults.Count -gt 0) { $allResults | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Csv -NoTypeInformation -Path $consolidatedCsvPath Write-Log -Message "Consolidated CSV results saved to $consolidatedCsvPath" -Status "Success" # Generate consolidated HTML report $htmlHeader = @"

Consolidated Results for All Servers

"@ $htmlFooter = "" $htmlBody = $allResults | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | ConvertTo-Html -Fragment $htmlHeader + $htmlBody + $htmlFooter | Out-File -FilePath $consolidatedHtmlPath -Encoding UTF8 Write-Log -Message "Consolidated HTML results saved to $consolidatedHtmlPath" -Status "Success" } else { Write-Log -Message "No results to save." -Status "Warning" } Write-Log -Message "Script completed. Consolidated results saved to CSV and HTML files in $basePath on WINSRVR1." -Status "Success" } catch { Write-Log -Message "An error occurred: $_" -Status "Error" } [/code]

 Hello this is the code test 

SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,s.text,
start_time,
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DBCC')

SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,s.text,
start_time,
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','DBCC')