Timeout beim DB Restore verhindern

Hallo

[autoit]

#include <C:\Autoit_Git\UDF\MS-SQL.au3>

[/autoit][autoit][/autoit][autoit]

Dim $DB_User = "user"
Dim $DB_Pass = "pw"

[/autoit][autoit][/autoit][autoit][/autoit][autoit]

Global $g_eventerror = 0 ; to be checked to know if com error occurs. Must be reset after handling.

[/autoit][autoit][/autoit][autoit]

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Install a custom error handler

[/autoit][autoit][/autoit][autoit][/autoit][autoit]

If _Check_DB("Thinkpad-DR",1433,"DB-80224") = "ok" Then
_Restore_DB("Thinkpad-DR","DB-80224","c:\Datenbanken\DB-80224.bak","ok")
Else
_Restore_DB("Thinkpad-DR","DB-80224","c:\Datenbanken\DB-80224.bak","nok")
EndIf

[/autoit][autoit][/autoit][autoit]

Func _Check_DB($DB_Server,$DB_Server_Port,$DB_Name) ; Master DB Abfrage und temporäre Ini Datei füllen

[/autoit][autoit][/autoit][autoit]

Local $s_dbcheck = ""
If $DB_Server_Port <> 1433 Then
If RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo", $DB_Server) <> "DBMSSOCN," & $DB_Server & "," & $DB_Server_Port Then
RegWrite("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo", $DB_Server, "REG_SZ", "DBMSSOCN," & $DB_Server & "," & $DB_Server_Port)
EndIf
EndIf

[/autoit][autoit][/autoit][autoit]

$sql = _SQLCon($DB_Server, $DB_User, $DB_Pass, "master")

[/autoit][autoit][/autoit][autoit]

If $g_eventerror then
$g_eventerror = 0
Else
$var = _SQLQuery($sql, "select name from sys.databases where owner_sid <> 0x01")
With $var
While Not .EOF
If .Fields("name").value = $DB_Name Then $s_dbcheck = "ok"
.MoveNext
WEnd
EndWith
_SQLEnd($sql)
Endif
Return $s_dbcheck
EndFunc

[/autoit][autoit][/autoit][autoit][/autoit][autoit]

Func _Restore_DB($DB_Server,$DB_Name,$DB_Backup,$s_dbcheck)

[/autoit][autoit][/autoit][autoit]

$sql = _SQLCon($DB_Server, $DB_User, $DB_Pass, "master")

[/autoit][autoit][/autoit][autoit]

If $g_eventerror then
$g_eventerror = 0
Else
If $s_dbcheck = "ok" Then
$var = _SQLQuery($sql, "alter database [" & $DB_Name & "] set single_user with rollback immediate")
;~ _SQLEnd($sql)

[/autoit][autoit][/autoit][autoit]

$var = _SQLQuery($sql, "drop database [" & $DB_Name & "]")
;~ _SQLEnd($sql)
EndIf

[/autoit][autoit][/autoit][autoit]

$var = _SQLQuery($sql, "RESTORE DATABASE [" & $DB_Name & "] FROM DISK = '" & $DB_Backup & "' WITH REPLACE, RECOVERY;")
EndIf
_SQLEnd($sql)
EndFunc

[/autoit][autoit][/autoit][autoit][/autoit][autoit]

Func MyErrFunc() ; COM Fehler abfangen / Falls SQL Server für Query nicht verfügbar etc
Msgbox(0,"Verbindungsfehler: " & $DB_Server,"We intercepted a COM Error !" & @CRLF & @CRLF & _
"err.description is: " & @TAB & $oMyError.description & @CRLF & _
"err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
"err.number is: " & @TAB & hex($oMyError.number,8) & @CRLF & _
"err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
"err.source is: " & @TAB & $oMyError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oMyError.helpcontext _
)

[/autoit][autoit][/autoit][autoit]

Local $err = $oMyError.number
If $err = 0 Then $err = -1

[/autoit][autoit][/autoit][autoit]

$g_eventerror = $err ; to check for after this function returns
Endfunc

[/autoit][autoit]

;SQL UDF
#cs
# # # # # # # # # # # # # # #
# #
# Microsoft SQL Function #
# Written by Aiden #
# Version 0.1 Beta #
# #
# # # # # # # # # # # # # # #
#ce

[/autoit][autoit][/autoit][autoit]

#cs
Name der Funktion: _SQLCon
Beschreibung: Stellt eine Verbindung zu einem Microsoft SQL Server her.
Beispiel: $sql = _SQLCon(127.0.0.1, user, pass, MyDB)
#ce

[/autoit][autoit][/autoit][autoit]

Func _SQLCon($scIP, $scUser, $scPass, $scDB)
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $scIP & "; User ID=" & $scUser & "; Password=" & $scPass & "; database=" & $scDB & ";")
Return $sqlCon
EndFunc

[/autoit][autoit][/autoit][autoit]

#cs
Name der Funktion: _SQLQuery
Beschreibung: Setzt eine Abfrage an eine Datenbank des Microsoft SQL Servers ab.
Beispiel: _SQLQuery($sql, "INSERT INTO (id, name, age) VALUES (1, admin, 55)")
#ce

[/autoit][autoit][/autoit][autoit]

Func _SQLQuery($iSQLCon, $iQuery)
If IsObj($iSQLCon) Then
Return $iSQLCon.execute($iQuery)
EndIf
EndFunc

[/autoit][autoit][/autoit][autoit]

#cs
Name der Funktion: _SQLEnd
Beschreibung: Beendet die Verbindung zum Microsoft SQL Server
Beispiel: _SQLEnd()
#ce

[/autoit][autoit][/autoit][autoit]

Func _SQLEnd($sqlCon)
If IsObj($sqlCon) Then
$sqlCon.close
EndIf
EndFunc

[/autoit]

Was kann ich tun damit es nicht zum Timeout beim letzten SQL-Statement kommt, womit kann ich Ihn bis zu erfolgreichen Fertigstellung bespaßen?

Gruß Dietmar

jo klappt bedankt

ich rufe nun allerdings die zuvor angelegte sp auf, ist etwas wasserdichter.

Zitat

DECLARE @SQL AS VARCHAR(20), @spid AS INT
SELECT @spid = MIN(spid) FROM master..sysprocesses WHERE dbid = db_id('YourDbName') AND spid != @@spid

-- Kill processes that currently use the database and could block the restoration
while (@spid IS NOT NULL)
BEGIN
print 'Killing process ' + CAST(@spid AS VARCHAR) + ' ...'
SET @SQL = 'kill ' + CAST(@spid AS VARCHAR)
EXEC (@SQL)

SELECT @spid = MIN(spid) FROM master..sysprocesses WHERE dbid = db_id('YourDbName') AND spid != @@spid
END
GO

-- Restore the database and bring it back online, so it can be accessed
RESTORE DATABASE YourDbName FROM DISK = '\\path\to\backup.bak' WITH REPLACE, RECOVERY;
GO

Alles anzeigen

http://stackoverflow.com/questions/6807…-mssql2005-2008

Gruß Dietmar