Problem to restore an SQL database....

27 Feb 2005 6:36 PM

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

27 Feb 2005 7:59 PM

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
elton_w***@hotmail.com

>-----Original Message-----
>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,
Show quoteHide quote

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

27 Feb 2005 10:41 PM

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

Show quoteHide quote

"Raphaël Désalbres" <raph***@desalbres.com> wrote in message
news:enp7cOQHFHA.3928@TK2MSFTNGP09.phx.gbl...
> 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
>

27 Feb 2005 11:39 PM

Raphaël Désalbres

OK, thanks everybody...

Show quoteHide quote

"Raphaël Désalbres" <raph***@desalbres.com> escreveu na mensagem
news:enp7cOQHFHA.3928@TK2MSFTNGP09.phx.gbl...
> 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
>

27 Feb 2005 11:40 PM

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.

Show quoteHide quote

"Raphaël Désalbres" <raph***@desalbres.com> wrote in message
news:enp7cOQHFHA.3928@TK2MSFTNGP09.phx.gbl...
> 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
>