CODEWITHSUNDEEP
vb.net
HABDEM
HABDEM

Reputation:

Restoring a database using vb.net

Can anyone help me how to restore a database from vb.net,I tried to restore using stored procedure by taking retore template script from sql server2005. but there is error "the database is already in use please use a master database.."

Upvotes: 0

Views: 2078

Answers (3)

VikingProgrammer
VikingProgrammer

Reputation: 555

I just wrote this in my project so I thought I'd share my method.

I'm calling the backup and restore by firing the SQL at the server using an SqlCommand.CommandText and setting the SqlParameters for database and filename as follows:

Simple backup:

BACKUP DATABASE @dbName 
    TO DISK=@fileName
    WITH FORMAT

Then restore it using : 

USE master
RESTORE DATABASE @dbName
   FROM DISK = @fileName

There are plethora of options on the BACKUP and RESTORE commands but I just wanted a quick sledge-hammer approach and this works nicely.

Thanks to Denis for the 'USE master' tip, which just fixed my 'in use' error!

Upvotes: 0

dance2die
dance2die

Reputation: 36905

From the error it doesn't look like there is an actual VB.net programmatic error.
It seems like the problem lies on the database restoration stored procedure.

Before restoring database, make sure that
• Your connection is not using the database you are restoring - That is one of the possible reasons for that error, "the database is already in use please use a master database.."
• Other connections to the target database should be closed off - Close all connection to it.

Denis Troller has mentioned "USE master" and make sure that your script has that statement as the very "first" statement in your restore batch script.

Upvotes: 0

Denis Troller
Denis Troller

Reputation: 7501

I assume you used the same connection string you usually use to connect to the database you are actually restoring. From you error message, I'd say you should create your connection to the server with a different InitialCatalog parameter (the error message indicates you should use "master").

The other option is to stick a "USE master" at the beginning of the script. Here is a small description of the USE statement.

Upvotes: 2

Related Questions