Problem to restore an SQL database....

[Raphaël Désalbres]

Hello,

I'm having the following problem.

I have SQL Server Accounting database called 'db_Accounting' , and a form.
This database is only accessed by one user at a time.
I have made a menu to backup the database. Works fine.

Dim fd As New SaveFileDialog
fd.Filter = "Database backup (BAK) | *.bak"
If fd.ShowDialog = DialogResult.OK Then
cmd.CommandText = "BACKUP DATABASE AccountingProgram TO DISK='" &
fd.FileName & "'"
cmd.ExecuteNonQuery()
End If

But then I need another menu to restore the database, but it doesn't work,
since the database is already in use.

Even if I try to close the connection it doesn't work.

What can I do about it? Do I have to kill an existing process?

Thanks,

Raphaël Désalbres

 

[Elton Wang]

Hi Raphaël,

Although I 'm not sure, you can try to dispose the
connection after close it and then force Garbage
Collection:

Connection.Dispose();
GC.Collect();

HTH

Elton Wang
(e-mail address removed)

-----Original Message-----
Hello,

I'm having the following problem.

I have SQL Server Accounting database

called 'db_Accounting' , and a form.

 

[Bernie Yaeger]

Hi Raphael,

You may want to take a look at using sqldmo and 2 functions they provide -
detach and attach database.

HTH,

Bernie Yaeger

 

[Raphaël Désalbres]

OK, thanks everybody...

 

[Jim Hughes]

Be sure to change the connection string default database/initial catalog to
be something besides the database you are doing the backup/restore on.
Master is pretty common.

Also, show the syntax of the RESTORE command that you are using, If you are
replacing an existing database you will need to use the REPLACE option.

You may also need to use the MOVE option if you are changing the destination
location from the one that was used during the backup.

Also remember that the TO DISK option is relative to the SQL Server computer
itself and not the remote workstations hard drive, so using the
SaveFileDialog will only work if your app is run at the server.