Restore MSDE db from VB app

[AccordingToDale]

This may be the wrong place to post this, but...

I have a VB.NET app that connects to an MSDE db. The app has an option to backup the db, which works very well.

Essentially, I disconnect the app from db, use osql to create the backup via a script file, then re-connect the app to the db.

I am using the same approach to restore, but for some reason I cannot restore. The script file works fine. I can run it from either QA or as a batch file and no problems, but when run from the app it doesn't work, even though it is disconnected from the db.

Unfortunately, I am not able to capture an error in the app (from the app perspective, there is no error), so I have no idea why it isn't working, but if anyone has any thoughts...

Dale

 

[NoCoolHandle]

Ok... first MDSE = SQL server..

Permissions become a big factor in being able to perform a restore.

YOU MUST BE DBO to be able to restore the database (or a memeber of the SYSADMIN role.

Basic sytnax (once you are logged in correctly)

Restore Database XYZ from Disk='e:\backups\yourbackup.bak'
with init


HTH


ROB

 

[AccordingToDale]

It's the sa that's logged in for the restore.

 

[NoCoolHandle]

What does your restore statement look like and what is the error you are getting..


(just a guess but I think that you have a locking issue. YOu are connected to the database you are trying to restore...

If I am correct, issue the following command on the active connection
"USE MASTER"

or in the connectionstring change your database name to MASTER..

SQL won't do a restore if you are connected to the db.

)

Rob

 

[AccordingToDale]

The app is disconnected from the db.

When using osql it connects to master.

This is the script that is used via osql...

USE master 
GO 
RESTORE DATABASE  SeedlingShipping 
FROM DISK = 'D:\SQLdata\Data\SeedlingShipping.bak' 
WITH MOVE 'SeedlingShipping' TO 'D:\SQLdata\Data\SeedlingShipping.mdf', 
MOVE 'SeedlingShipping_log' TO 'D:\SQLdata\Data\SeedlingShipping_log.ldf', 
REPLACE

this is the vb code used for both the backup and restore...

Private Sub runOSQLscript(ByVal scriptName As String)
   'our osql string
   Dim osqlParams As String = ""
   'create a formatted string
   osqlParams = String.Format("/U{0} /P{1} /S{2} /d{3} /n /i{4}", "sa", password, "(local)", "master", "D:\SQLdata\Data\" & scriptName)

   Dim proc As New Process
   proc.StartInfo.FileName = "osql.exe"
   proc.StartInfo.CreateNoWindow = True
   proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden

   Try
      proc.StartInfo.Arguments = osqlParams
      proc.Start()
      ' wait until the script execution ends
      proc.WaitForExit()
   Catch ex As Exception
      'error processing instructions...

   Finally
      proc.Dispose()
   End Try

End Sub

Dale

 

[NoCoolHandle]

ok now you are making me think

Interesting that you choose to use the OSQL tool to do the restore..

You could easily do the same thing through an sqlcommand object..

Q1. What happens if you open the osql tool and just give it the command an tell it to execute?
Q2. Are you sure you don't have an open connection? Pooled maybe..

YOu could try executing sp_who into a datatable and it should tell you what connections are open..

e.g.

Private Function sp_who () as DataTable
Dim con as New SqlClient.SqlConnection("server=[red]yourserver[/red];database=Master;User ID=sa;Password=[red]yourpassword[/red]")
Dim cmd as New SqlClient.SqlCommand("sp_who",con)
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim dt As New DataTable
With cmd
.CommandType = CommandType.StoredProcedure
con.Open()
try
'The next line populates a datatable for return
da.Fill(dt)
' The following code might be helpfull in using the datatable...
'Dim col As DataColumn
'Dim intX As Integer
'For intX = 0 To dt.Columns.Count - 1
'    Response.Write(dt.Columns(intX).ColumnName.ToString & ", ")
'Next
'For intX = 0 To dt.Rows.Count - 1
'    Response.Write(dt.Rows(intX)(1).ToString & "<br>")
'Next

catch ex as exception
'msgbox ex.message
'response.write ex.message
Return Nothing
exit function
Finally
con.Close()
cmd.Dispose()
End Try
End With
If dt Is Nothing Then
    Return Nothing
Else
    Return dt
End If
End Function

The just execute it and see what is open..


ROB

 

[NoCoolHandle]

Oh yeah, are you seeing any errors?

(I am about to go home for the day, but will check when I get there (about an hour or 2 from now...)


Rob

 

[AccordingToDale]

Well, that was interesting...

I'm using the osql option b/c I wanted to try something different, essentially.

Anyway, I did something a little more on the simple side for my test - as my coding was the more complex route ;>)

I just ran sp_sho in QA at several different points during the app so i could see who was logged on and at what points that all changed.

So, the user i am using for this particualr connection status was 'sleeping' even after disconnecting, so that would certainly cause exclusivity issues.

But i was noticing that there are two lines in the sp_who results when i connect, even though the connect procedure only is called once. Why is that?

Anyway, one of these changes to the master db when the script runs, but the other does not. Thots?

I was thinking about the exclusivity issue, but suffering from brain fade when it came to the sp_who. Good call, Rob.

Dale

 

[NoCoolHandle]

I had the feeling...

I have noticed that any oledb type connection (sqlclient included) tends to hang out for at least 60 seconds..

I also noticed that connection pooling can kick you butt if you have to many connect requests too fast and you dont clean up your objects well.

1 time I had the dispose after returning the dataset and the dispose never got called because the class it existed in had been disposed before I could close and clean up my connections..

To force a restore, you could issue an sp_dboption 'dbo use only',1 (I think that is the command.., of course if you are logged on as sa that kind of fails to help

Or you could always turn off sql.. 'Net stop mssqlserver'
that always shuts down connections

Then net start mssqlserver to restart and restore..


Have a good one..

Rob

 

[gmmastros]

Before resorting to a Net Stop and a Net Start, I would suggest a less drastic option.

First, make sure you are connected to the master database

Use Master

Then, using VB, issue the sp_who command. Iterate through the recordset and kill the connections that are using the database you are trying to restore.

sp_who returns 8 columns, check the dbname column for the database you are trying to restore. If found, use the value in the spid column to kill the connection.

Kill 51 (for example)

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AccordingToDale]

I was actually just working on this.

The kill works fine and the backup restores just great.

Thanx all for the help.

BTW: Rob,

Interesting that you choose to use the OSQL tool to do the restore..

I was getting a little tired yesterday and didn't really answer this properly.

The reason I am using osql is that the connection object I use in the app is logged on with limited permissions (datareader, datawriter and backupoperator) so I can't do a restore with this connection.

I suppose I could create a different connection object and use that, but the osql seemed more fun! :>)

Dale