Just a day before I got a very interesting email. Here is the email (modified a bit to make it relevant to this blog post).

“Pinal,

We are facing a very strange issue.

One of our query  related to backup files and backup set has stopped working suddenly in SSMS. It works fine in application where we have and in the stored procedure but when we have it in our SSMS it gives following error.

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dbo.backupfile’.

Here are our queries which we are trying to execute.

SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;

This query gives us details related to backupset and backup files when the backup was taken.”

When I receive this kind of email, usually I have no answers directly. The claim that it works in stored procedure and in application but not in SSMS gives me no real data. I have requested him to very first check following two things:

1. If he is connected to correct server? His answer was yes.
2. If he has enough permissions? His answer was he was logged in as an admin.

This means there was something more to it and I requested him to send me a screenshot of the his SSMS. He promptly sends that to me and as soon as I receive the screen shot I knew what was going on.

Before I say anything take a look at the screenshot yourself and see if you can figure out why his queries are not working in SSMS. Just to make your life a bit easy, I have already given a hint in the image.

The answer is very simple, the context of the database is master database. To execute above two queries the context of the database has to be msdb. Tables backupset and backupfile belong to the database msdb only.

Here are two workaround or solution to above problem:

1) Change context to MSDB

Above two queries when they will run as following they will not error out and will give the accurate desired result.

USE msdb
GO
SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;

2) Prefix the query with msdb

There are cases above script used in stored procedure or part of big query, it is not possible to change the context of the whole query to any specific database. Use three part naming convention and prefix them with msdb.

SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM msdb.dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM msdb.dbo.backupfile;

Very simple solution but sometime keeps people wondering for an answer.

Reference: Pinal Dave (http://blog.sqlauthority.com)