Problem with SQL Server Script...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rapha?l D?salbres - 26 Feb 2005 01:54 GMT

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

Reply to this Message

Michael Levy - 26 Feb 2005 16:42 GMT

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

> Hello,
>
[quoted text clipped - 13 lines]
>
> Rapha?l D?salbres

Reply to this Message

Rapha?l D?salbres - 26 Feb 2005 15:59 GMT

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...

> 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
[quoted text clipped - 19 lines]
>>
>> Rapha?l D?salbres

Reply to this Message

William \(Bill\) Vaughn - 26 Feb 2005 18:39 GMT

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?

Signature

____________________________________
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.
__________________________________

>I tried without the "GO", but it still doesn't work...
>
[quoted text clipped - 39 lines]
>>>
>>> Rapha?l D?salbres

Reply to this Message

Rapha?l D?salbres - 27 Feb 2005 16:03 GMT

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" <billvaRemoveThis@nwlink.com> escreveu na mensagem
news:%23pGdCKDHFHA.3088@tk2msftngp13.phx.gbl...

> You need to read his answer.
> Execute each of the sections (delimited with GO) individually--but without
[quoted text clipped - 44 lines]
>>>>
>>>> Rapha?l D?salbres

Reply to this Message

William \(Bill\) Vaughn - 28 Feb 2005 02:34 GMT

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.

Signature

> Hello,
>
[quoted text clipped - 57 lines]
>>>>>
>>>>> Rapha?l D?salbres

Reply to this Message

Problem with SQL Server Script...

Free Magazines