Backup and restore

Hello to all readers.
I can perform a Backup like this :
Public Sub Backup(ByVal dtCurrentId As DateTime)
Dim sSQLSvrDir As String = GetSqlDataDirectory()
Dim sDataDir As String = sSQLSvrDir & "\Data\"
Dim sServerName As String = Environment.MachineName & "\" & INSTANCE_NAME
Dim sCommand As String
'close the actual connexion
CloseDatabase()
'set the connection string to the the master database
sConnectionString = BuildConnectionString(sServerName, MASTERDB,
String.Empty, SecurityMode.smWindowsIntegrated)
'process the backup
sCommand = "USE master;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_addumpdevice 'disk', 'PIPTBackup', '" & g_sAppPath &
BACKUPSUBDIR & "\Backup PIP Traceability " & Format(dtCurrentId, "yyyy-MM-dd
HH-mm-ss") & ".dat';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "BACKUP DATABASE [PIP Traceability] TO PIPTBackup;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
're open the database connection
Call OpenDatabase()
End Sub
This work fine.
But the restore :
Public Sub Restore(ByVal dtCurrentId As DateTime)
Dim sSQLSvrDir As String = GetSqlDataDirectory()
Dim sDataDir As String = sSQLSvrDir & "\Data\"
Dim sServerName As String = Environment.MachineName & "\" & INSTANCE_NAME
Dim sCommand As String
'close the actual connexion
CloseDatabase()
'set the connection string to the the master database
sConnectionString = BuildConnectionString(sServerName, MASTERDB,
String.Empty, SecurityMode.smWindowsIntegrated)
'process the backup
sCommand = "USE master;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_addumpdevice 'disk', 'PIPTBackup', '" & g_sAppPath &
BACKUPSUBDIR & "\Backup PIP Traceability " & Format(dtCurrentId, "yyyy-MM-dd
HH-mm-ss") & ".dat';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "RESTORE DATABASE [PIP Traceability] FROM PIPTBackup; EXEC
sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
're-open the database connection
Call OpenDatabase()
End Sub
does not work. I get an error message telling that the database is used by
an other user !!! (during the Restore SQL)
Any suggestion will be very appreciated.
Thanks
hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
> does not work. I get an error message telling that the database is used by
> an other user !!! (during the Restore SQL)
> Any suggestion will be very appreciated.
>
it usually depends on Connection Pooling... your connection is closed, but
the connection pooler usualy takes up to 1 minute to completely release
it... so you have to wait a little...
Ado.Net shoul'd expose a "non pooled" property for connections...
but other cons... the connection will neveer be picked up from an existing
pool, so it will take a little more to create it
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
do you think that a 'dispose' could help ?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:%23ReD5jdkEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Thanks Andrea,
> do you think that a 'dispose' could help ?
really do not know... but I do not think so... :-(
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
OK, that's working. I have to wait for a while for the connection release
from the pool.
(In the dev env, I had also to unsucsribe the database from the server
browser :-) )
Is there a cool way to test that the connection has been released from the
pool ot do I have to catch the error when trying the restore ?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:u7d46oykEHA.3520@.tk2msftngp13.phx.gbl...
> Hi Andrea,
> OK, that's working. I have to wait for a while for the connection release
> from the pool.
> (In the dev env, I had also to unsucsribe the database from the server
> browser :-) )
> Is there a cool way to test that the connection has been released from the
> pool ot do I have to catch the error when trying the restore ?
I think you always have to catch the exception(s) =;-DDDD
you can perhaps set the "autoclose" database property to help you this way
(default setting on MSDE platform), but beware that this setting has access
penalties as it always close the database when no active connections are
present, and reopen it as new connections are live..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea & Ouaf,
It does expose such a thing. You can set "Pooling=false" in your connection
string and pooling will be disabled. That's the setting for SqlClient. For
OleDB client, it's "OleDBServices=-4".
I'd suggest avoiding AutoClose. I've seen diabolical problems caused by it,
many more than it was intended to solve, plus it won't really help here.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||"Greg Low [MVP]" <greglow@.lowell.com.au> ha scritto nel messaggio
news:e5ix4$5kEHA.3016@.tk2msftngp13.phx.gbl...
> I'd suggest avoiding AutoClose. I've seen diabolical problems caused by
it,
> many more than it was intended to solve, plus it won't really help here.
>
=;-D
thank's Greg
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply