BackUp and Restore SQL Database Using TSQL

See more:

SQL-server-2000

VB

SQL-Server

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.