whats wrong with this script? i don''t see a problem.

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  July 25, 2006 at 11:55 am

  #61464

  just checking out this script, but can't

  figure why it's in error.

  all it does is put together a list of databases, and

  their backup history compared to space on disk.

  here is the error i'm getting:

  Server: Msg 207, Level 16, State 3, Line 5

  Invalid column name 'logical_name'.

  here is the script. kinda long, but not too bad.

  /* Work with current database if a database name is not specified */

  DECLARE @dbname sysname

  SET @dbname = DB_NAME()

  SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format

  CONVERT(char, backup_start_date, 108) AS [Time],

  @dbname AS [Database Name],

  -- [filegroup_name] AS [Filegroup Name],

  --logical_name AS [Logical Filename],

  --physical_name AS [Physical Filename],

  CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],

  Growth AS [Growth Percentage (%)]

  FROM

  (

  SELECT b.backup_start_date,

  a.backup_set_id,

  (a.backed_up_page_count),

  --a.logical_name,

  --a.[filegroup_name],

  --a.physical_name,

  (

  SELECT CONVERT(numeric(5,2),

  (((a.backed_up_page_count*8192) * 100.00) / (i1.backed_up_page_count*8192))-100)

  FROM msdb.dbo.backupfile i1

  WHERE i1.backup_set_id =

  (

  SELECT MAX(i2.backup_set_id)

  FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3

  ON i2.backup_set_id = i3.backup_set_id

  WHERE i2.backup_set_id < a.backup_set_id AND
  i2.file_type='D' AND
  i3.database_name = @dbname AND

  --i2.logical_name = a.logical_name AND
  --i2.logical_name = i1.logical_name AND

  i3.type = 'D'
  ) AND
  i1.file_type = 'D'
  ) AS Growth
  FROM msdb.dbo.backupfile a JOIN msdb.dbo.backupset b
  ON a.backup_set_id = b.backup_set_id
  WHERE b.database_name = @dbname AND
  a.file_type = 'D' AND
  b.type = 'D'

  ) as Derived
  WHERE (Growth <> 0.0) OR (Growth IS NULL)

  ORDER BY logical_name, [Date]

  thoughts?

  _________________________

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  July 25, 2006 at 1:32 pm

  #651457

  Hello,

  In the query, you are not using the column "logical_name" but in the order by clause you have mentioned the same.

  Remove the same and the query works fine.

  Thanks

  
  Lucky

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  July 25, 2006 at 2:03 pm

  #651464

  oh no... thats embarassing.

  please don't tell any one.

  _________________________