CODEWITHSUNDEEP
c#sqlsql-serverrestore
Dustine Tolete
Dustine Tolete

Reputation: 471

Invalid object name after restoring a database

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 ?

Upvotes: 0

Views: 869

Answers (2)

Andy Brown
Andy Brown

Reputation: 19161

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

Upvotes: 1

Heinzi
Heinzi

Reputation: 172270

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.

Upvotes: 0

Related Questions