Help with Scripts

• M&M

  SSC-Insane

  Points: 21700

  More actions

  January 17, 2012 at 6:45 am

  #249356

  We are having around 100 servers and we need below information

  1) Find number of databases in each instance

  2) Find on which date last backup happened

  3) Get the location of the last backup file

  4) Space remaining on the backup folders

  If you have any powershell scripts handy for this, could you please share it.

  M&M

• M&M

  SSC-Insane

  Points: 21700

  More actions

  January 20, 2012 at 12:46 am

  #1436299

  I found this link useful.

  script to get last SQL Server backup date[/url]

  M&M

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  January 20, 2012 at 12:52 am

  #1436302

  Thanks for sharing!!!

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Mr. Holio

  Hall of Fame

  Points: 3314

  More actions

  January 20, 2012 at 3:45 am

  #1436353

  mohammed moinudheen (1/17/2012)

  ---

  We are having around 100 servers and we need below information

  1) Find number of databases in each instance

  2) Find on which date last backup happened

  3) Get the location of the last backup file

  4) Space remaining on the backup folders

  If you have any powershell scripts handy for this, could you please share it.

  This can be done even if your powershell knowledge is quite limited.

  What backup? Full, diff, trn? Assuming you meant free space on disks?

  If your backups are spread across different disks use this and change it if you need other backup (eg log) info

  IF OBJECT_ID('tempdb..#disk') IS NOT NULL DROP TABLE #disk

  CREATE TABLE #disk(

  driveVARCHAR(1)

  ,freeNUMERIC)

  DECLARE @qry VARCHAR(200)

  SET @qry = 'EXEC master..xp_fixeddrives'

  INSERT INTO #disk EXEC(@qry)

  SELECT DISTINCT

  s.name AS 'Database',

  (CASE WHEN b.backup_start_date IS NULL THEN '01/01/1900 00:00:00' ELSE b.backup_start_date END) AS 'Backup Date',

  REPLACE(m.physical_device_name,COALESCE(RIGHT(m.physical_device_name, NULLIF(CHARINDEX(REVERSE('\'), REVERSE(m.physical_device_name)), 0)-1), m.physical_device_name),'') AS [Folder],

  CAST((d.free/1024) AS DECIMAL(10,2)) AS [Free Space (GB)]

  FROM master..sysdatabases s

  LEFT OUTER JOINmsdb..backupset b ON s.name = b.database_name

  AND b.backup_start_date = (SELECT MAX(backup_start_date)

  FROM msdb..backupset

  WHERE database_name = b.database_name AND type = 'D')

  LEFT OUTER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id

  LEFT OUTER JOIN #disk d ON d.drive = LEFT(m.physical_device_name,1)

  WHEREs.name <> 'tempdb'

  ..and to get the count you would do

  SELECT COUNT(name) AS [Count] FROM master..sysdatabases WHERE name <> 'tempdb'

  Now you just need to display the stuff in tables for each instance which isn't really hard..assuming you have a list of instances in a txt file.

  foreach ($svr in get-content "C:\sql.txt")

  {

  $con = "server=$svr;database=master;Integrated Security=sspi"

  $cmd =

  "#query1 goes here

  "

  $cmd2 =

  "#query2 goes here

  "

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) | out-null

  $da2 = new-object System.Data.SqlClient.SqlDataAdapter ($cmd2, $con)

  $dt2 = new-object System.Data.DataTable

  $da2.fill($dt2) | out-null

  $svr

  $dt2 | Format-Table -autosize

  $dt | Format-Table -autosize

  }

  close enough?:-)

  __________________________
  Allzu viel ist ungesund...

• M&M

  SSC-Insane

  Points: 21700

  More actions

  January 20, 2012 at 9:08 am

  #1436580

  Holio, Thank you for taking the time. This is enough 🙂

  M&M