Recently, I was tasked with a job to ensure all of our SQL Servers were configured to backup all databases to disk, and that all of these backup files existed in their backup location.
As we had over a dozen SQL server with multiple databases per SQL server this was not a task to be done manually.
So, I created the following script to get the job done. Basically it works as follows:
Check-SqlBackups -ComputerName SQLSERVER -SqlInstance INSTANCE -UserName USER -Password PASS
SQLInstance, Username, and Password are optional parameters.
This will enumerate all databases on SQLSERVER and for each database tell me the state of the backup. Was it backed up to disk, was a Snapshot taken (e.g. via VSS) etc. An example of the result is as follows
|
1 2 3 4 5 6 7 8 9 10 |
BackupPath : C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\Budget.bak SnapshotBackupExists : False ComputerName : Ben-PC DiskBackupExists : True DatabaseSize : 7168 DiskBackupStartDate : 8/12/2015 10:29:38 PM SnapBackupStartDate : Instance : LOCALHOST\SQLEXPRESS BackupFileExists : True Database : Budget |
There is still a bit more work I could do this to enumerate all SQL Instances on a SQL Server, fortunately these are few and far between.
Saved a world of pain 🙂 Hopefully it is of use to someone else, if not, it is a least in and handy place for me to get hold of again! 🙂
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
#Get last 30 days of backups $ageInDays = 30 $sql_backup = "SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.database_name, msdb.dbo.backupset.is_snapshot, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - $ageInDays) AND msdb.dbo.backupset.type='D' ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date" $sql_2000_backup = $sql_backup.Replace("msdb.dbo.backupset.is_snapshot,","") Function Invoke-sqlCmd ($Query, $ServerInstance, $UserName,$Password){ $SqlConnection = New-Object System.Data.SqlClient.SqlConnection if ($UserName) { $SqlConnection.ConnectionString = "Server = $ServerInstance;user id=$UserName;password=$Password" } else { $SqlConnection.ConnectionString = "Server = $ServerInstance; Integrated Security = True" } $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet [void]$SqlAdapter.Fill($DataSet) $SqlConnection.Close() return $DataSet.Tables[0] } function Check-BackupPath ($ComputerName = $env:COMPUTERNAME, $LocalPath) { if ($LocalPath -eq $null) { return $false } $path = $LocalPath.Replace("\","\\") $test = gwmi cim_datafile -Filter "Name='$path'" -ComputerName $ComputerName if ($test -ne $null) {return $true } else { return $false } } function Check-SqlBackups ($ComputerName = $env:COMPUTERNAME, $SqlInstance = $ComputerName,$UserName,$Password) { try { $backups = Invoke-sqlCmd -Query $sql_backup -ServerInstance $SqlInstance -UserName $UserName -Password $Password $diskBackups = $backups | ? {$_.is_snapshot -eq $false} $snapshotBackups = $backups | ? {$_.is_snapshot} } catch #Pre 2003 SQL { $backups = Invoke-sqlCmd -Query $sql_2000_backup -ServerInstance $SqlInstance -UserName $UserName -Password $Password $diskBackups = $backups $snapshotBackups = $null } finally { $databases = Invoke-sqlCmd -Query "EXEC sp_databases" -ServerInstance $SqlInstance -UserName $UserName -Password $Password $backups = foreach ($database in $databases) { $lastDiskBackup = $diskBackups | ? {$_.database_name -eq $database.database_name} | sort backup_start_date -Descending | select -First 1 $lastSnapBackup = $snapshotBackups | ? {$_.database_name -eq $database.database_name} | sort backup_start_date -Descending | select -First 1 $diskBackupExists = $false $snapBackupExists = $false if ($lastDiskBackup) { $diskBackupExists = $true } if ($lastSnapBackup) { $snapBackupExists = $true } $props = @{ ComputerName = $ComputerName Instance = $SqlInstance Database = $database.DATABASE_NAME DatabaseSize = $database.DATABASE_SIZE DiskBackupExists = $diskBackupExists DiskBackupStartDate = $lastDiskBackup.backup_start_date BackupPath = $lastDiskBackup.physical_device_name BackupFileExists = (Check-BackupPath -ComputerName $ComputerName -LocalPath $lastDiskBackup.physical_device_name) SnapshotBackupExists = $snapBackupExists SnapBackupStartDate = $lastSnapBackup.backup_start_date } New-object psobject -Property $props } $backups } } |