subesh_bulbul (6/25/2008)

---

i had worked on guidline for the purpose that i have taken a backup of 'msdb' from my another office and restore on suspected one.it has been restore and...

I wouldn't worry so much about the questions asked. I'd worry about the questions not been asked.

You really shouldn't be flushing the cache regularly.

What are the symptoms of the problem that you are seeing?

You don't need the distinct inside the IN. In is just checking for existance. It doesn't matter how many times a value is there.

Other than that, I don't think there's...

Here's a step-by-step guide to recreating the MSDB database from the installation media.

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

Here's a partially cleaned up exec plan if anyone wants to take a look.

Is the exec plan correct in estimating that only 2 rows are returned?

I asee 2 immediate problems....

Thanks. I'll check those out.

Do you have replication on either of these databases?

Is there any chance that someone has been messing with the files?

Very important, do you have a backup?

The client tool is management studio, I'm asking about the database engine you're trying to attach to. Thing is, that's a SQL 2000-specific error

Open a query to the SQL instance...

Indexes? Any chance of the execution plan?

How many rows does the query return?

Do you have a backup?

Can you look at the error log and see if there's any entry there that indicates why the database is in recovery-pending?

There are many warnings about running profiler on a production server, but those warnings are for the profiler GUI. The recommendation for tracing a production system is to use the...

Can you post the table structure, the indexes and the aprox row count please.

How long does it currently run?

If possible, can you get the exec plan (set showplan_all on), run...

So, the live database is missing some data and the backup is missing some data. Is that correct?

Is there any chance that someone deleted it?

Do you have a backup that...

Have you tried Redgate's SQL Data Compare?