January 17, 2012 at 6:45 am
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
January 20, 2012 at 12:46 am
January 20, 2012 at 12:52 am
Thanks for sharing!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 20, 2012 at 3:45 am
mohammed moinudheen (1/17/2012)
We are having around 100 servers and we need below information1) 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...
January 20, 2012 at 9:08 am
Holio, Thank you for taking the time. This is enough 🙂
M&M
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply