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.

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)

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

  1. lol I’ve been using sql for years and I still get this error. It pays to check you have the basics covered before you go off thinking there is some serious connection issues and what not.

    Like

  2. this error come out to me today, I could not solve the problem until I disconnected n connected again, that way I solve this problem , of course I had to show my database in “availables bata bases”

    Like

  3. There is a ‘master’ drop down table to the right of the ‘New Query’ box. Make sure to select the correct category and you should find that will resolve your issue.

    Like

  4. If you are using the right database and the intellisense is still underlining objects you have just created, refresh it in SSMS.

    Edit -> Intellisense -> Refresh Local Cache

    Or, CTRL+SHIFT+R
    No more red squigly line!

    Like

    • Hi…. I was getting the same error. I checked my Database name in top dropdown, it was preselected correctly. Then I used the query USE [myDatabaseName] and it still dint work. Suddenly saw your post and tried ctrl+shift+R and it worked. Thanks alot

      Like

  5. Hi, Pinal

    You saved me many hours! “2) Prefix the query with msdb” and “Use three part naming convention”!

    thanks a lot!

    Frank Wang

    Like

  6. HI,
    when i am doing Report server configuration for the existing database am getting this error.
    ERROR: The database you have selected is not Report server database.Please select a SQL Server database that contains report server specific tables and stored procedures and try again.

    can anyone help on this issue.

    Thanks ,
    Mallikharjuna

    Like

  7. Nice, your last tip made me try Customer.dbo.CUSTOMER instead of dbo.CUSTOMER and IT WORKED!!

    in my case I was using generic sql to test my connection/jndi setup etc and for everyone’s edification the simple code:

    SELECT * FROM Customer.dbo.CUSTOMER

    Results

    Zip: ${row.Zip}

    Like

  8. Hi I have similar issue. Please see below my query

    IF OBJECT_ID(N’tempdb..#lastweek’, N’U’) IS NOT NULL
    DROP TABLE #lastweek;

    declare @sql varchar(max)

    create table #lastweek
    (
    [MY ID] varchar(15)
    ,NAME varchar(100)
    ,[EMMISSION Level]varchar(100)
    ,[Project]varchar(100)
    ,[LAST TOOL] varchar(100)
    ,[TOOLS Actual Date] Datetime
    ,[IMPLEMENTATION Actual Date] Datetime
    )

    set @sql=(‘insert into #mdslastweek select [MY ID], NAME, [EMMISION LEVEL], [Project],
    [Last TOOLS], [TOOLS Actual Date], [IMPLEMENTATION Actual Date] from DWH_Data_CARS.dbo.tbl_CAR_TOOLS_’
    +CONVERT(varchar(4),datepart(year,getdate()))
    +case when datepart(month,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) else
    +CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) end
    +case when datepart(d,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) else
    +CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) end)

    I want to select from this "DWH_Data_CARS.dbo.tbl_CAR_TOOLS_20140609 which is the last week of today's data populated on that last week date.

    That is why I have this query

    FROM DWH_Data_Integrity.dbo.tbl_NTP3_MDS_'
    +CONVERT(varchar(4),datepart(year,getdate()))
    +case when datepart(month,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) else
    +CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) end
    +case when datepart(d,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) else
    +CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) end)

    but now I am getting the following error: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'DWH_Data_CARS.dbo.tbl_CAR_TOOLS_20140615'.

    I need help urgently thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s