We help IT Professionals succeed at work.
Get Started

Select in MSSQL 2005 vs 2008R2

Last Modified: 2012-05-12
I wrote the following to find all files belonging to the latest backup set and verify that they actually exist on disk. It works as it should on MSSQL 2008 and Denali. But when I run it on MSSQL 2005 it 'hangs'.

SELECT      physical_device_name AS FilePath,
bset.media_set_id AS BackupSetMediaID,
bset.type AS BackupType,
backup_finish_date AS BackupDate                  
FROM msdb.dbo.backupset bset
INNER JOIN msdb.dbo.backupmediafamily bmf ON bset.media_set_id = bmf.media_set_id
WHERE database_name IN
      ---List of all the non system databases---
      (SELECT name FROM sys.databases
      WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'))
-- All backups since and including last full backup
AND bset.media_set_id >=(SELECT MAX(b.media_set_id) FROM msdb.dbo.backupset b WHERE b.type = 'D' and b.database_name = bset.database_name)

Seems to be related to the following statement 'and b.database_name = bset.database_name'.
Does anyone have any idea why this works in 2008 but not 2005? and what do I do about it?
Watch Question
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE