Restore sql server database in vb.net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore sql server database in vb.net

Author

Topic

rock_finn
Starting Member

4 Posts

Posted - 2006-06-30 : 04:35:20

good day to all!
i'm not really sure if this is the right forum to ask but anyway.
we're building a vb.net project, and it should include a way of backing up and restoring our database (SQL Server 2003). my codes for backup is working just fine, but the restore is not. here's a sample of my restore code:

use master
RESTORE DATABASE DB_Sample
from disk = 'C:\temp\DB_Sample.BAK'

Actually when i run this code in SQL Query analyzer, it works. but when i use it my vb codes, a message appears saying "Exclusive access cannot be obtained because the database is in use." when i'm sure there's no one else using my database (its only in my local computer). Here's my sample code:

Dim strSql, strDatabaseName, strBackupName As String
strDatabaseName = "DB_Sample"
strBackupName = "C:\temp\DB_Sample.BAK"

strSql = "use master " & _
"RESTORE DATABASE " & strDatabaseName & " " & _
"from disk = '" & strBackupName & "' "

Dim connString As New SqlConnection
connString.ConnectionString = "server=(local);Trusted_Connection=false;user id=sa;password=;database=ANTAT
Dim sqlComm As New SqlCommand(strSql, connString)"

connString.Open()
sqlComm.ExecuteNonQuery()
connString.Close()

Thanks to those who could help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 04:42:32

Try using WITH REPLACE option.


RESTORE DATABASE DB_Sample 
FROM DISK = 'C:\temp\DB_Sample.BAK'
WITH REPLACE

Peter Larsson
Helsingborg, Sweden

rock_finn
Starting Member

4 Posts

Posted - 2006-06-30 : 04:56:11

tried your suggestion, but the same error appears. i'm thinking that the error might not be in the RESTORE DATABASE DB_Sample FROM DISK = 'C:\temp\DB_Sample.BAK' code but somewhere else. maybe in my connectionstring? but thats the same connectionstring i'm using with all my other sqlcommands.

thanks anyway.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-30 : 05:33:45

in your connection string use master instead of antat...and remove the use master text in your tsql

or you can add a ';' after use master

i prefer the first suggestion though as this simplifies it

--------------------
keeping it simple...

rock_finn
Starting Member

4 Posts

Posted - 2006-06-30 : 05:48:28

i've already changed the database to master. also removed the "use master". same error error appears. i'm just wondering whats wrong, coz when i open my sql analyzer using server=(local), user id=sa, password=, database=master, the codes work. but when i put it in vb.net, that error appears.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-30 : 11:10:46

you may want to change the access to single user only in dboptions then to make sure that only the dbo, which is you, can login

the error is saying that there are other users, you can verify by running your vb apps, then issue an sp_who to verify if someone is connected to the database, or check the current activity monitor in enterprise manager for a quicker view (don't forget to refresh)

--------------------
keeping it simple...

rock_finn
Starting Member

4 Posts

Posted - 2006-07-02 : 23:23:13

jen, thanks for your suggestions, but i'm not much of a database administrator. i dont know what to do with what i see in current activity monitor. but anyway, i tried building a new project with only a single form. in my form, i placed my code for backup and restoring of my database. i guess what, it worked! so i think my problem is in my original project. maybe there are some connections left open, and i just need to find those!

thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources