Wednesday, December 4, 2013

Find out the backup lcoations/paths for multiple SQL Instances

If you are trying to consolidate backup dump zones/locations, we first need to analyze where the databases are being backed up to. Below query will give you the location of where the databases were backed up to. Run this in Central Management server and you can get a report of all the SQL Instances in one shot

SET NOCOUNT ON
SELECT CONVERT(CHAR(30), SERVERPROPERTY('Servername')) AS instance_name,
CONVERT(CHAR(30),bs.database_name) AS database_name,
CONVERT(CHAR(125),bf.physical_device_name) backup_path
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bf
ON bs.media_set_id = bf.media_set_id
JOIN master.dbo.sysdatabases db
ON bs.database_name = db.name
WHERE bs.backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
            AND type = 'D'
      )
      AND (DATABASEPROPERTYEX(bs.database_name,'Status') = 'ONLINE'
      AND DATABASEPROPERTYEX(bs.database_name,'IsInStandby') = 0)

Display the list of indexes and columns for a given table

You might find yourself in a situation where you have to analyze the indexes for a given table and want to know which columns are part of wh...