Click here to Skip to main content
15,878,430 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello, this is Vikash Gohil.

I have to Create an Application based on the following needs.

There are 2 databases with Same name and Structure under 2 SQL2000 instances.

The Application will connect to both databases.

It will then take backup of both databases using TSQL.

Then It will Copy some data from 2nd database to 1st database.

Then Take backup of 1st database again.

Restore the backup ,taken in previous step, in the 2nd database.

Again restore the 1st database with the backup taken in step 1.

Now I have managed to Takes backups and Copy data between databases using TSQL

The problem occurs while restoring these databases.

It shows error : Exclusive Access rights to the database cannot be obtained. restore database fails.

I have also set database to single user mode before restoring but the same problem exists.

each process on separate database is performed using its own sql connection object.

I use the below code for restoring the databases.

Dim SqlC as New SqlCommand
SqlC.Connection = MyConn
SqlC.CommandText = "ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
SqlC.ExecuteNonQuery
SqlC.CommandText = "RESTORE DATABASE [DBName] FROM DISK = '[filepath]'"
SqlC.ExecuteNonQuery
SqlC.CommandText = "ALTER DATABASE [DBName] SET MULTI_USER"
SqlC.ExecuteNonQuery

Can Somebody Help me in this case.

Any Help Would be greatly appreciated.

Thanks in Advance.

Awaiting a reply soon.
Posted
Updated 31-Aug-10 1:48am
v2
Comments
Lantei 31-Aug-10 8:02am    
ok a little advice before I even ttempt an answer : I it would be great if you had implemented everything in a single procedure / script and then call it into your VB code..enhances performance and speed of your application

1 solution

i'd have a look at this tsql restore a database
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900