Invalid object name after restoring a database

Question

i have my c# application, and i want to restore a database using my application . my sql code is like this:

alter database SSDPrototypeV3 set offline with rollback immediate
restore database SSDPrototypeV3
from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\dustinepogi.bak'
alter database SSDPrototypeV3 set online

when i run this on my application, it successfully restores my db . but when i try to run a sql query (like a select statement), it says that i have an invalid object name, specifically on the table that i performed my select statement . but then, if i reload my application after closing, it is fully restored . how can i get rid of this problem ?

Andy Brown · Accepted Answer · 2013-05-12 17:14:33Z

1

Assuming you are using SqlClient (and you are using connection pooling):

switch to master first
do your restore
clean your connection pool: using SqlConnection.ClearAllPools (clears all pools for the provider) or ClearPool (clears just the pool associated with the specific connection string)
run your commands against your original database

And, hopefully, off you go

answered May 12, 2013 at 17:14

Andy Brown

19.2k3 gold badges55 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Heinzi · Accepted Answer · 2013-05-12 15:54:23Z

0

A database connection has a current database that all queries are executed against. The initial database parameter of the connection string determines the current database used when the connection is opened.

I assume that the current database changes back to master when you take your SSDPrototypeV3 database offline. You have two options to fix this:

Change the current database back by executing use SSDPrototypeV3 or
close and reopen the connection.

answered May 12, 2013 at 15:54

Heinzi

173k61 gold badges386 silver badges554 bronze badges

2 Comments

Dustine Tolete Over a year ago

i've tried closing the connection on my application, and switched it to master, but i still got the same problem/error .

Heinzi Over a year ago

@DustineTolete: You shouldn't switch to master, you should switch to your DB! Does select * from SSDPrototypeV3.dbo.someTable work?

Collectives™ on Stack Overflow

Invalid object name after restoring a database

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related