March 4, 2003 at 9:03 am
Hello,
I created a tool using sqlDMO for hot standby .
Functions of the tool : Backup & Restore.
Every day tool will take a full backup and after user defined interval it will take Differential Backup. After every Backup it will restore it in standby server in standby mode .
Problem :
1) stand by mode needs a file called undo.dat some time it is not created after Restore operation .
2) I changed restore operation in with noRecovary mode . it gives error while restoring Differential backup like chain is not complete or not in same chain.
3) With Recovery also not supporting Differential Backup.
March 4, 2003 at 9:24 am
Can you post the code?
Andy
March 4, 2003 at 9:47 am
[]
Can you post the code?
''~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'' Author PADMAKUMAR
'' Restore DATABASE To standby Server
''
''~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private WithEvents oRestore As SQLDMO.Restore
Dim mvarPhysicalLocation As String
Dim mvarAbort As Boolean
Dim mvarFile As String
Dim mvarGroupID As Integer
Private mvarconnection As ADODB.Connection
Private mvarDeviceName As String
Private mvarDeviceType As Byte
Private mvarDBName As String
Private mvarsqlserver As New SQLDMO.SqlServer
Private mvarAutoBackUp As Boolean
Private mvarAction As Byte
Private mvarfullBackup As Boolean
Private mvarTotalAvilableFreeSpace As Long
Private mvarFileNumber As Long
Private mvarRestoreAs As String
Private mvarResotoreDataAs As String
Private mvarResotoreLogAs As String
Private mvarLastFullBackUpID As Double
Private mvarPosition As Integer
Private mvarFilename As String
Private mvarUndoFile As String
Private Const BACKUPTYPE_DEVICE = 0 ''// BackUp through Device
Private Const BackupType_File = 1 ''// BackUp through File
Private Const BACKUPTYPE_Tape = 2 ''//BackUp To Tape
''// Const For FileList
Private Const FileList_LogicalName = 1
Private Const FileList_PhysicalName = 2
Private Const FileList_Type = 3
''// Const For BackupHeader
Private Const BackupHeader_BackupName = 1
Private Const BackupHeader_BackupDescription = 2
Private Const BackupHeader_BackupType = 3
Private Const BackupHeader_ExpirationDate = 4
Private Const BackupHeader_Compressed = 5
Private Const BackupHeader_Position = 6
Private Const BackupHeader_DeviceType = 7
Private Const BackupHeader_UserName = 8
Private Const BackupHeader_ServerName = 9
Private Const BackupHeader_DatabaseName = 10
Private Const BackupHeader_DatabaseVersion = 11
Private Const BackupHeader_DatabaseCreationDatesmalldatetime = 12
Private Const BackupHeader_BackupSize = 13
Private Const BackupHeader_FirstLsn = 14
Private Const BackupHeader_LastLsn = 15
Private Const BackupHeader_CheckpointLsn = 16
Private Const BackupHeader_DatabaseBackupLsn = 17
Private Const BackupHeader_BackupStartDate = 18
Private Const BackupHeader_BackupFinishDate = 19
Private Const BackupHeader_SortOrder = 20
Private Const BackupHeader_CodePage = 21
Private Const BackupHeader_CompatibilityLevel = 22
Private Const BackupHeader_SoftwareVendorId = 23
Private Const BackupHeader_SoftwareVersionMajorinteger = 24
Private Const BackupHeader_SoftwareVersionMinorinteger = 25
Private Const BackupHeader_SoftwareVersionBuildinteger = 26
Private Const BackupHeader_MachineName = 27
''// Const for Backup Type
Private Const BackupSetType_Database = 1
Private Const BackupSetType_TransactionLog = 2
Private Const BackupSetType_File = 4
Private Const BackupSetType_Differential_Database = 5
Private Const FreeSpace = 1024
Public Event RestoreEvent(Msg As String, Number As Long)
Public Event RestoreError(Msg As String, Number As Long)
Private Sub Class_Initialize()
Set oRestore = New SQLDMO.Restore
End Sub
Public Property Get PhysicalLocation() As String
PhysicalLocation = mvarPhysicalLocation
End Property
Public Property Let PhysicalLocation(ByVal vData As String)
mvarPhysicalLocation = vData
End Property
Private Property Get Abort() As Boolean
Abort = mvarAbort
End Property
Private Property Let Abort(ByVal vData As Boolean)
mvarAbort = vData
End Property
Public Property Get cn() As ADODB.Connection
cn = mvarconnection
End Property
Public Property Set cn(ByVal vData As ADODB.Connection)
Set mvarconnection = vData
End Property
Public Property Get DeviceName() As String
DeviceName = mvarDeviceName
End Property
Public Property Let DeviceName(ByVal vData As String)
mvarDeviceName = vData
End Property
Public Property Get DeviceType() As Byte
DeviceType = mvarDeviceType
End Property
Public Property Let DeviceType(ByVal vData As Byte)
mvarDeviceType = vData
End Property
Public Function GetBackUpSet() As ADODB.Recordset
Dim Adocm As New ADODB.Command
On Error GoTo errhand
''// Select backupsets of a group
strsql = "SELECT * FROM MSDB..backupsets WHERE GroupID=? "
With Adocm
Set .ActiveConnection = mvarconnection
.CommandText = strsql
.Parameters.Append .CreateParameter("GroupID", adInteger, adParamInput, , mvarGroupID)
.CommandType = adCmdText
Set GetBackUpSet = .Execute
End With
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Public Property Get DbName() As String
DbName = mvarDBName
End Property
Public Property Let DbName(ByVal vData As String)
mvarDBName = vData
End Property
Public Function GetAutoBackUpSet() As ADODB.Recordset
Dim strsql As String
Dim Adocn As New ADODB.Command
On Error GoTo errhand
With Adocn
Set .ActiveConnection = mvarconnection
.CommandText = "SELECT * FROM MSDB..backupsets WHERE AutoBackUp=1"
.CommandType = adCmdText
Set GetAutoBackUpSet = .Execute
End With
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Public Property Get Action() As Byte
Action = mvarAction
End Property
Public Property Let Action(ByVal vData As Byte)
mvarAction = vData
End Property
Public Property Get FileNumber() As Long
FileNumber = mvarFileNumber
End Property
Public Property Let FileNumber(ByVal vData As Long)
mvarFileNumber = vData
End Property
Public Sub DoRestore()
Dim RsRestoreset As New ADODB.Recordset
Dim str As String
Dim HeaderQResults As QueryResults
Dim FlistQResults As QueryResults
Dim strDataFile As String
Dim strLogFile As String
Dim IFrom As Integer
Dim blnIsdbExists As Boolean
Dim mvarBackupdate As String
Dim mvarLastFullBackUpID As String
Dim LastFullBackPosition As Integer
Dim I As Integer
On Error GoTo errhand
''// Get All Auot Backup Set For restore
Set RsRestoreset = GetAutoBackUpSet
If RsRestoreset.RecordCount > 0 Then
Do Until RsRestoreset.EOF
With RsRestoreset
mvarDeviceName = !DeviceName
mvarDeviceType = !DeviceType
mvarPhysicalLocation = !PhysicalLocation
mvarRestoreAs = !RestoreAs
mvarFilename = RsRestoreset!Filename
mvarResotoreDataAs = "" & !ResotoreDataAs
mvarResotoreLogAs = "" & !ResotoreLogAs
mvarUndoFile = "" & !UndoFile
End With
With oRestore
.Action = SQLDMORestore_Database
''// Type of Backup to Restore
If mvarDeviceType = BACKUPTYPE_DEVICE Then
.Devices = mvarDeviceName
ElseIf mvarDeviceType = BackupType_File Then
.Files = mvarPhysicalLocation & "\" & mvarFilename
End If
''// Database Name to which OR AS you want to
''// Restore Backup
.Database = mvarRestoreAs
''// Backup Info
Set HeaderQResults = GetFileHeader(mvarPhysicalLocation & "\" & mvarFilename)
Set FlistQResults = GetFileList(mvarPhysicalLocation & "\" & mvarFilename)
''// Get Restore Position
If HeaderQResults.Rows > 0 Then
LastFullBackPosition = GetRestorePosition(HeaderQResults, _
mvarPosition)
Else
Exit Sub
End If
For IFrom = mvarPosition To HeaderQResults.Rows
.FileNumber = IFrom
strDataFile = ""
strLogFile = ""
GetFileandPath strDataFile, strLogFile
mvarTotalAvilableFreeSpace = GetFreeDiskspaceInMB(Mid(strDataFile, 1, 2))
If mvarTotalAvilableFreeSpace > CLng(GetBackupSizeInMB(HeaderQResults, _
mvarPosition)) Then
RaiseEvent RestoreEvent("There is not Enough Free Disk space", 0)
Exit Sub
End If
If IsRestoreingOnsameDataBase(HeaderQResults, _
mvarPosition, mvarRestoreAs) Then
RaiseEvent RestoreEvent("Cannot Restore to Same Database from Which Backup is Done", 0)
Exit Sub
End If
blnIsdbExists = IsExistsDataBase(mvarRestoreAs)
If HeaderQResults.GetColumnString(IFrom, _
BackupHeader_BackupType) = BackupSetType_Database Then
mvarfullBackup = True
End If
If blnIsdbExists = False Or mvarfullBackup Then
.RelocateFiles = GetRelocateFile(FlistQResults, _
strDataFile, strLogFile)
Else
.RelocateFiles = ""
End If
If mvarfullBackup Or blnIsdbExists Then
.ReplaceDatabase = True
Else
.ReplaceDatabase = False
End If
.StandbyFiles = mvarUndoFile & "\undo.dat"
Debug.Print .GenerateSQL
mvarBackupdate = HeaderQResults.GetColumnString(IFrom, _
BackupHeader_BackupFinishDate)
mvarLastFullBackUpID = HeaderQResults.GetColumnString(LastFullBackPosition, _
BackupHeader_DatabaseBackupLsn)
If VerifyBackup(oRestore) Then
.SQLRestore mvarsqlserver
I = I + 1
Else
GoTo GoTONext
End If
UpdateRestoreDetails RsRestoreset!BackUpSetID, _
mvarBackupdate, mvarLastFullBackUpID, IFrom
GoTONext:
Next
End With
RsRestoreset.MoveNext
Loop
End If
Exit Sub
errhand:
If Err.Number = -2147221499 Then
UpdateRestoreDetails RsRestoreset!BackUpSetID, _
mvarBackupdate, mvarLastFullBackUpID, mvarPosition
Else
MsgBox Err.Description, vbInformation + vbCritical, "Restore"
End If
End Sub
Public Function GetFileList(Filename As String) As QueryResults
On Error GoTo errhand
With oRestore
.Files = Filename
.FileNumber = 1
Set GetFileList = .ReadFileList(mvarsqlserver)
End With
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Public Function GetFileHeader(Filename As String) As QueryResults
On Error GoTo errhand
With oRestore
.Files = Filename
.FileNumber = 1
Set GetFileHeader = .ReadBackupHeader(mvarsqlserver)
End With
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Private Function GetSqlDataRoot(SqlServer As SQLDMO.SqlServer) As String
GetSqlDataRoot = SqlServer.Registry.SQLDataRoot
End Function
Public Property Get RestoreAs() As String
RestoreAs = mvarRestoreAs
End Property
Public Property Let RestoreAs(ByVal vData As String)
mvarRestoreAs = vData
End Property
Public Property Get ResotoreDataAs() As String
ResotoreDataAs = mvarResotoreDataAs
End Property
Public Property Let ResotoreDataAs(ByVal vData As String)
mvarResotoreDataAs = vData
End Property
Public Property Get ResotoreLogAs() As String
ResotoreLogAs = mvarResotoreLogAs
End Property
Public Property Let ResotoreLogAs(ByVal vData As String)
mvarResotoreLogAs = vData
End Property
Public Function GetFileandPath(DataFile As String, LogFile As String) As String
On Error GoTo errhand
If Not IsNull(mvarResotoreDataAs) And Not mvarResotoreDataAs = "" _
And IsValidPathExists(mvarResotoreDataAs) Then
DataFile = mvarResotoreDataAs & "\" & mvarRestoreAs & "_Data.MDF"
Else
DataFile = GetSqlDataRoot(mvarsqlserver) & "\" & mvarRestoreAs & "_Data.MDF"
End If
If Not IsNull(mvarResotoreLogAs) And Not mvarResotoreLogAs = "" _
And IsValidPathExists(mvarResotoreLogAs) Then
LogFile = mvarResotoreDataAs & "\" & mvarRestoreAs & "_Log.LDF"
Else
LogFile = GetSqlDataRoot(mvarsqlserver) & "\" & mvarRestoreAs & "_Log.LDF"
End If
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Private Function GetLastrestoreInfo(DeviceName As String) As ADODB.Recordset
Dim Adocn As New ADODB.Command
On Error GoTo errhand
With Adocn
Set .ActiveConnection = mvarconnection
.CommandText = "SELECT * FROM MSDB..RestoreDetails WHERE " _
& " RecID=(Select Max(RecID) FROM MSDB..RestoreDetails RD , " _
& " MSDB..backupsets BS WHERE RD.BackUpSetID=BS.BackUpSetID" _
& " AND BS.DeviceName=?)"
.Parameters.Append .CreateParameter("DeviceName", adVarChar, adParamInput, 40, mvarDeviceName)
.CommandType = adCmdText
Set GetLastrestoreInfo = .Execute
End With
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Private Function IsExistsDataBase(RestoreDbName As String) As Boolean
Dim Db As Database
Dim p As Property
On Error GoTo errhand
IsExistsDataBase = False
For Each Db In mvarsqlserver.Databases
If Db.Name = RestoreDbName Then
IsExistsDataBase = True
End If
Next
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Private Function GetRelocateFile(FileListQuery As QueryResults, DataFile As _
String, LogFile As String) As String
On Error GoTo errhand
Dim strReFile As String
Dim I As Integer
Dim oDb As Database
Dim strLogicalNameofLog As String
Dim strLogicalNameofData As String
strReFile = ""
If IsExistsDataBase(mvarRestoreAs) Then
strReFile = GetDataFileandLogFromAnExistingDB(mvarRestoreAs, _
FileListQuery)
Else
For I = 1 To FileListQuery.Rows
If strReFile <> "" Then
strReFile = strReFile & ","
End If
strReFile = strReFile & "[" & FileListQuery.GetColumnString(I, 1) & "],"
If FileListQuery.GetColumnString(I, 3) = "D" Then
strReFile = strReFile & "[" & DataFile & "]"
ElseIf FileListQuery.GetColumnString(I, 3) = "L" Then
strReFile = strReFile & "[" & LogFile & "]"
End If
Next
End If
GetRelocateFile = strReFile
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Public Function GetRestorePosition(HeaderQ As QueryResults, _
Position As Integer) As Integer
On Error GoTo errhand
Dim rs As New ADODB.Recordset
Dim iRows As Integer
Dim RPosition As Integer
Dim RLastFullBackUpID As String
Dim LastFullBackpos As Integer
''// Get Restore info from DataBase
Set rs = GetLastrestoreInfo(mvarDeviceName)
''// Get Last Full Backup set From
With HeaderQ
If .Rows > 0 Then
For iRows = .Rows To 1 Step -1
If .GetColumnString(iRows, BackupHeader_BackupType) = BackupSetType_Database Then
LastFullBackpos = iRows
Exit For
End If
Next
End If
End With
If rs.RecordCount > 0 Then
RPosition = Val(0 & rs!Position)
RLastFullBackUpID = rs!LastFullBackUpID
End If
With HeaderQ
If CStr(RLastFullBackUpID) = CStr(.GetColumnString(LastFullBackpos, _
BackupHeader_DatabaseBackupLsn)) Then
If RPosition >= LastFullBackpos And LastFullBackpos < .Rows _
And IsExistsDataBase(mvarRestoreAs) Then
Position = RPosition + 1
Else
Position = LastFullBackpos
End If
Else
Position = LastFullBackpos
End If
End With
GetRestorePosition = LastFullBackpos
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Public Property Get LastFullBackUpID() As Double
LastFullBackUpID = mvarLastFullBackUpID
End Property
Public Property Let LastFullBackUpID(ByVal vData As Double)
mvarLastFullBackUpID = vData
End Property
Public Property Get Position() As Integer
Position = mvarPosition
End Property
Public Property Let Position(ByVal vData As Integer)
mvarPosition = vData
End Property
Private Function GetBackupSizeInMB(HeaderQ As QueryResults, _
Position As Integer) As Currency
Dim BackUpsize As Currency
With HeaderQ
BackUpsize = CCur(CDbl(.GetColumnString(Position, BackupHeader_BackupSize)))
GetBackupSizeInMB = BackUpsize * 10000 / 1024 / 1024
End With
End Function
Public Function IsRestoreingOnsameDataBase(HeaderQ As QueryResults, _
Position As Integer, RestoreAs As String) As Boolean
Dim strServerNameInBackupSet As String
Dim strStandByServer As String
Dim strDbNameInBackupSet As String
On Error GoTo errhand
strStandByServer = GetIni("STANDBY", "SERVER", App.Path & "\" & App.EXEName & ".INI")
With HeaderQ
strServerNameInBackupSet = .GetColumnString(Position, _
BackupHeader_ServerName)
strDbNameInBackupSet = .GetColumnString(Position, _
BackupHeader_DatabaseName)
End With
If strServerNameInBackupSet = strStandByServer And _
strDbNameInBackupSet = RestoreAs Then
IsRestoreingOnsameDataBase = True
End If
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Private Sub UpdateRestoreDetails(BakupSetID As Long, _
BackUpDate As String, LastFullBackUpID As String, Position As Integer)
Dim Adocn As New ADODB.Command
On Error GoTo errhand
With Adocn
Set .ActiveConnection = mvarconnection
.CommandText = "Insert Into MSDB..RestoreDetails (BackUpSetID," _
& " BackUpDate,LastFullBackUpID,Position,RestoreTime)" _
& " VALUES(?,?,?,?,GetDate()) "
.CommandType = adCmdText
.Parameters.Append .CreateParameter("BackUpSetID", adInteger, adParamInput, , BakupSetID)
.Parameters.Append .CreateParameter("BackUpDate", adVarChar, adParamInput, 50, BackUpDate)
.Parameters.Append .CreateParameter("LastFullBackUpID", adVarChar, adParamInput, 50, LastFullBackUpID)
.Parameters.Append .CreateParameter("Position", adSmallInt, adParamInput, , Position)
.Execute
End With
Exit Sub
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Sub
Public Function GetDataFileandLogFromAnExistingDB(RestoreDbName As String, _
FileListQuery As QueryResults) As String
Dim Db As Database
Dim p As Property
Dim strLogicalNameofLog As String
Dim strLogicalNameofData As String
On Error GoTo errhand
For I = 1 To FileListQuery.Rows
If FileListQuery.GetColumnString(I, 3) = "D" Then
strLogicalNameofData = FileListQuery.GetColumnString(I, 1)
ElseIf FileListQuery.GetColumnString(I, 3) = "L" Then
strLogicalNameofLog = FileListQuery.GetColumnString(I, 1)
End If
Next
For Each Db In mvarsqlserver.Databases
If Db.Name = RestoreDbName Then
strReFile = "[" & strLogicalNameofData & "],"
strReFile = strReFile & "[" & Trim(Db.FileGroups("PRIMARY").DBFiles(1).PhysicalName) & "]"
For I = 1 To Db.TransactionLog.LogFiles.Count
If strReFile <> "" Then
strReFile = strReFile & ","
End If
strReFile = strReFile & "[" & strLogicalNameofLog & "],"
strReFile = strReFile & Trim("[" & Db.TransactionLog.LogFiles(I).PhysicalName) & "]"
Next
End If
Next
GetDataFileandLogFromAnExistingDB = strReFile
Exit Function
errhand:
RaiseEvent RestoreError(Err.Description, Err.Number)
End Function
Public Property Get Filename() As String
Filename = mvarFilename
End Property
Public Property Let Filename(ByVal vData As String)
mvarFilename = vData
End Property
Private Function VerifyBackup(ObjRestore As SQLDMO.Restore) As Boolean
On Error GoTo errhand
VerifyBackup = False
With ObjRestore
.SQLVerify mvarsqlserver
End With
VerifyBackup = True
Exit Function
errhand:
Resume 0
End Function
Private Sub oRestore_Complete(ByVal Message As String)
RaiseEvent RestoreEvent(Message, 0)
End Sub
Private Sub oRestore_NextMedia(ByVal Message As String)
RaiseEvent RestoreEvent(Message, 0)
End Sub
Private Sub oRestore_PercentComplete(ByVal Message As String, ByVal Percent As Long)
RaiseEvent RestoreEvent(Message, Percent)
End Sub
Public Sub FillReadOnlyDB(cmb As ComboBox)
Dim Db As Database
For Each Db In mvarsqlserver.Databases
If Db.Properties("ReadOnly") = True Then
End If
Next
End Sub
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply