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

 

[Corran007]

post if you can the script file and the vb.net code that is calling it

 

[AccordingToDale]

the script:

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

the vb code:

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

I use the sub for the backup and the restore, just passing the scriptName.

Any thots would be appreciated.

Dale

 

[mrdenny]

Why are you calling osql to do the backup and restore. Just connect to the SQL Server, connecting to the Master database and execute the backup or restore command from there. Don't bother shelling out and running osql.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[AccordingToDale]

I was trying through a regular connection and sql command object but was getting issues - essentially exclusivity issues. Even when I close my vb connection object and dispose of it, the connection status changes to 'sleeping' until I actually close the app.

I was trying the osql route to see if i could get a work around, and really just wanted to try it out. :>)

Anyway, I need to take a closer look at my connection tomorrow - when my brain is a little more alert.

Dale

 

[AccordingToDale]

OK, after a reasonable night's sleep... :>)

The reason I was using the osql was two-fold:

1) the sql connection I use in the app has datareader, datawriter and backupoperator permissions only, so the given connection cannot restore backups. This is a security limitation given by the client.

2) although I could have just created another connection object, I went with the osql for the sake of experimentation. (I end up using this for backups as well for sake of consistent coding.) Either way, I still run into the same issue.

Someone suggested that a connection may remain open for up to 60 seconds, even though the object has been closed and properly cleaned up in the app. Is that true?

Dale

 

[AccordingToDale]

I ended up using kill and all works fine.

Thanx all.

Dale