|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem with SQL Server Script...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 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 Show quoteHide quote "Raphaël Désalbres" <raph***@desalbres.com> wrote in message news:%23AJd256GFHA.2356@TK2MSFTNGP12.phx.gbl... > 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 > > > 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... Show quoteHide quote "Michael Levy" <levy.mich***@gamail.com> escreveu na mensagem news:O0%23G4ICHFHA.3092@tk2msftngp13.phx.gbl... > 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 > > > "Raphaël Désalbres" <raph***@desalbres.com> wrote in message > news:%23AJd256GFHA.2356@TK2MSFTNGP12.phx.gbl... >> 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 >> >> >> > > 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? -- Show quoteHide quote____________________________________ 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" <raph***@desalbres.com> wrote in message news:uQ4xHSCHFHA.3968@TK2MSFTNGP14.phx.gbl... >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" <levy.mich***@gamail.com> escreveu na mensagem > news:O0%23G4ICHFHA.3092@tk2msftngp13.phx.gbl... >> 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 >> >> >> "Raphaël Désalbres" <raph***@desalbres.com> wrote in message >> news:%23AJd256GFHA.2356@TK2MSFTNGP12.phx.gbl... >>> 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 >>> >>> >>> >> >> > > 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...... Show quoteHide quote "William (Bill) Vaughn" <billvaRemoveT***@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 > 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" <raph***@desalbres.com> wrote in message > news:uQ4xHSCHFHA.3968@TK2MSFTNGP14.phx.gbl... >>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" <levy.mich***@gamail.com> escreveu na mensagem >> news:O0%23G4ICHFHA.3092@tk2msftngp13.phx.gbl... >>> 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 >>> >>> >>> "Raphaël Désalbres" <raph***@desalbres.com> wrote in message >>> news:%23AJd256GFHA.2356@TK2MSFTNGP12.phx.gbl... >>>> 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 >>>> >>>> >>>> >>> >>> >> >> > > 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. -- Show quoteHide quote____________________________________ 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" <raph***@desalbres.com> wrote in message news:%23ilRN5OHFHA.3332@TK2MSFTNGP15.phx.gbl... > 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" <billvaRemoveT***@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 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" <raph***@desalbres.com> wrote in message >> news:uQ4xHSCHFHA.3968@TK2MSFTNGP14.phx.gbl... >>>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" <levy.mich***@gamail.com> escreveu na mensagem >>> news:O0%23G4ICHFHA.3092@tk2msftngp13.phx.gbl... >>>> 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 >>>> >>>> >>>> "Raphaël Désalbres" <raph***@desalbres.com> wrote in message >>>> news:%23AJd256GFHA.2356@TK2MSFTNGP12.phx.gbl... >>>>> 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 >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  
Other interesting topics
DataSet Performance
Close() and Dispose() new bie to stored procedure...please suggest me Cannot do update on a record. Please help. Insert SQL Error Login based connection string setting! Batch queries in stored procedures? Subject:Writing a db independent data access layer with DAAB:How? How to add new record in a form with BindingManagerBase used? dBase |
|||||||||||||||||||||||
