Problem with SQL Server Script...

[Raphaël Désalbres]

Hello,

I'm having the following problem.

Using the following script works OK when run from Query Analyzer

use master
go
Drop database db_Accounting
go
RESTORE DATABASE db_Accounting FROM DISK='D:\mydb.bak'

But, my question is, how can I make it work through VB.NET code?

Thanks,

Raphaël Désalbres

 

[Raphaël Désalbres]

I tried without the "GO", but it still doesn't work...

like this:
Me.cnAccountingProgram.Close()
Me.cnAccountingProgram.Dispose()
Dim cnMaster As New SqlConnection("Initial Catalog=Master; Data
Source=(local); Integrated Security=SSPI")
Dim cmd1 As New SqlCommand
cnMaster.Open()
cmd1.Connection = cnMaster
cmd1.CommandText = "DROP DATABASE DB_Accounting"
cmd1.ExecuteNonQuery()
cmd1.CommandText = "RESTORE DATABASE DB_Accounting FROM DISK='D:\mydb.bak'"
cmd1.ExecuteNonQuery()
cnMaster.Close()

But I still get an error...

 

[Michael Levy]

You're probably getting tripped up on the GOs. GO is a batch delimiter that
is only understood by the SQL Server tools (QA, OSQL, etc). You'll have to
split your script at the GOs to form seperate calls so the server.

-Mike

 

[William \(Bill\) Vaughn]

You need to read his answer.
Execute each of the sections (delimited with GO) individually--but without
the GO batch marker.
What errors are you getting?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Raphaël Désalbres]

Hello,

I think I wasn't clear in my question: I need to drop a database and restore
the backup in code (VB.NET).

How can I do?

Thanks,

Raphaël......

 

[William \(Bill\) Vaughn]

Ok, I'm paying attention now.
First, you don't have to (should not) drop the database before you restore
it.
However, to do so you need to have sufficient rights--the SA account has
those rights. See BOL for details.
So, to restore all you need is (assuming your connection string includes
"Database=master;")...

RESTORE DATABASE db_Accounting FROM DISK='D:\mydb.bak'

This also assumes there are no other connections that have the target
database open. In some versions you need to start the server in single-user
mode.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________