SQL SERVER – Error: Fix – Msg 208 – Invalid object name ‘dbo.backupset’ – Invalid object name ‘dbo.backupfile’

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.

SQL SERVER - Error: Fix - Msg 208 - Invalid object name 'dbo.backupset' - Invalid object name 'dbo.backupfile' backupfileerror

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;

SQL SERVER - Error: Fix - Msg 208 - Invalid object name 'dbo.backupset' - Invalid object name 'dbo.backupfile' backupfileerror1

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Beginning of SQL Server Architecture – Terminology
Next Post
SQLAuthority News – Memories at Anniversary of SQL Wait Stats Book

Related Posts

60 Comments. Leave new

  • select query error in sql server
    September 6, 2016 7:44 am

    Que: select query error in sql server

    Answer: is this right?

    select * from
    (
    select dbo.CreditDebit.ID as SLNO,TransactionDate,Particular,Balance,Expenses,Credit_Expenses,Debit_Expenses,Expenses_Name FROM DBO.CreditDebit
    INNER JOIN DBO.CreditExpenses
    on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID inner join
    dbo.Expenses on dbo.Expenses.ID=dbo.CreditExpenses.Expenses_ID where cast(dbo.CreditDebit.TransactionDate as datetime) between ‘2016-07-07’ and ‘2016-09-07′
    –and @TransactionDate) and dbo.Expenses.Expenses_Name like @Expenses+’%’)) or dbo.Expenses.Expenses_Name like @Expenses+’%’
    –cast(dbo.CreditDebit.TransactionDate as datetime)
    )
    details
    pivot(
    sum(Expenses)
    for Expenses_Name in([Salary_Expenses],[Electric And Lift Expenses],[Miscellaneous_Expenses],[BuildingMaintainence_Expenses])

    )as pvt]

    Reply
  • Hi Pinal,

    When I try to create any table or stored procedure in a particular sql server database , i get the following error

    Msg 208, Level 16, State 1, Procedure capture_events, Line 18
    Invalid object name ‘SYSTEMLOGS.dbo.changelog’.

    Could not find anything on google. Don’t know the actual reason for this error.

    Reply
  • Hi Pinal,

    Working on an existing database and I have to create some tables, but these table are not accessible without schema name prefix while older tables are accessible.

    Kindly reply on this.

    Thanks in advance.

    Reply
  • This explanation is Awesome. I run into same problem and this article so helpful

    Reply
  • Thanks Zee.

    Reply

Leave a Reply

Menu