March 27, 2017 at 4:16 am
SQLBill - Wednesday, March 15, 2017 11:34 AMartisticcheese - Friday, March 10, 2017 12:13 PMSQLBill - Friday, March 10, 2017 12:09 PMartisticcheese - Friday, March 10, 2017 9:31 AMWell this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.While this won't help you now, I would suggest you change your backup methods. You take a full backup every four days and tlog backups hourly. Change that to a Full backup every four days, a differential every day, and tlogs every hour. If you had done that, you would only have to restore the full back, 3 differential backups, and then just those tlog backups made after the last differential.
Example: Full backup made Sunday evening. Need to restore Thursday morning. You would restore the full backup, the differentials for Monday, Tuesday, and Wednesday nights, and lastly, the tlog backups that were made following Wednesday's differential backup. Much easier.
-SQLBill
Yes I would usually do something like that but problem is that volume backup is being done by third party service which breaks restore chain and diff backups stop working
Then if the Diff chain would be broken, so would the tlog chain.
-SQLBill
Not necessarily. A diff backup chain is broken by a (n unavailable) full backup; a log backup chain is broken by a (n unavailable) log backup or by switching to Simple recovery mode.
If you're worried about volume backups, explain this to the third party and then disable the SQL Server VSS Writer service.
John
March 27, 2017 at 2:50 pm
John Mitchell-245523 - Monday, March 27, 2017 4:16 AMSQLBill - Wednesday, March 15, 2017 11:34 AMartisticcheese - Friday, March 10, 2017 12:13 PMSQLBill - Friday, March 10, 2017 12:09 PMartisticcheese - Friday, March 10, 2017 9:31 AMWell this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.While this won't help you now, I would suggest you change your backup methods. You take a full backup every four days and tlog backups hourly. Change that to a Full backup every four days, a differential every day, and tlogs every hour. If you had done that, you would only have to restore the full back, 3 differential backups, and then just those tlog backups made after the last differential.
Example: Full backup made Sunday evening. Need to restore Thursday morning. You would restore the full backup, the differentials for Monday, Tuesday, and Wednesday nights, and lastly, the tlog backups that were made following Wednesday's differential backup. Much easier.
-SQLBill
Yes I would usually do something like that but problem is that volume backup is being done by third party service which breaks restore chain and diff backups stop working
Then if the Diff chain would be broken, so would the tlog chain.
-SQLBill
Not necessarily. A diff backup chain is broken by a (n unavailable) full backup; a log backup chain is broken by a (n unavailable) log backup or by switching to Simple recovery mode.
If you're worried about volume backups, explain this to the third party and then disable the SQL Server VSS Writer service.
John
John,
Sorry for not being clear...the OP said the vendor can also do backups and if the OP went to Diff backups vice just TLog backups, the vendor might make a backup that would break the Differential chain....I was saying the same thing would happen with the TLogs backups. If the vendor makes a full backup and the OP doesn't have access to it, the TLog chain is "worthless" since the most recent TLog backups would go with the missing Full Backup. So if the you can lose a backup "chain" at the whim of the vendor...I still think Diff backups combined with TLog backups are worth considering. The restore is still quicker (less files) as long as the vendor doesn't mess things up.
-SQLBill
March 28, 2017 at 7:36 am
SQLBill - Monday, March 27, 2017 2:50 PMJohn Mitchell-245523 - Monday, March 27, 2017 4:16 AMSQLBill - Wednesday, March 15, 2017 11:34 AMartisticcheese - Friday, March 10, 2017 12:13 PMSQLBill - Friday, March 10, 2017 12:09 PMartisticcheese - Friday, March 10, 2017 9:31 AMWell this work but the problem is that UI does not allow you to restore more then one TRN file at a time and I make full backup every 4 days and hourly log backups so to restore for last 3 days it will take forever.While this won't help you now, I would suggest you change your backup methods. You take a full backup every four days and tlog backups hourly. Change that to a Full backup every four days, a differential every day, and tlogs every hour. If you had done that, you would only have to restore the full back, 3 differential backups, and then just those tlog backups made after the last differential.
Example: Full backup made Sunday evening. Need to restore Thursday morning. You would restore the full backup, the differentials for Monday, Tuesday, and Wednesday nights, and lastly, the tlog backups that were made following Wednesday's differential backup. Much easier.
-SQLBill
Yes I would usually do something like that but problem is that volume backup is being done by third party service which breaks restore chain and diff backups stop working
Then if the Diff chain would be broken, so would the tlog chain.
-SQLBill
Not necessarily. A diff backup chain is broken by a (n unavailable) full backup; a log backup chain is broken by a (n unavailable) log backup or by switching to Simple recovery mode.
If you're worried about volume backups, explain this to the third party and then disable the SQL Server VSS Writer service.
John
John,
Sorry for not being clear...the OP said the vendor can also do backups and if the OP went to Diff backups vice just TLog backups, the vendor might make a backup that would break the Differential chain....I was saying the same thing would happen with the TLogs backups. If the vendor makes a full backup and the OP doesn't have access to it, the TLog chain is "worthless" since the most recent TLog backups would go with the missing Full Backup. So if the you can lose a backup "chain" at the whim of the vendor...I still think Diff backups combined with TLog backups are worth considering. The restore is still quicker (less files) as long as the vendor doesn't mess things up.
-SQLBill
John is correct here. Tlog restores will work as long as you have any full backup, including a copy_only one, and an unbroken log backup chain since that full backup. ie VSS only messes up differential restores.
I have a similar problem on all our servers as I do not know when VSS will be run which means I can only use differential backups on an ah hoc basis and not as part of a regular routine. At least I am in the fortunate position of having the servers under low load at night so full backups can be done every day.
As far as restoring goes, I would also use sql script. The following outline powershell will generate the t-sql restore script for all the files it comes across. It assumes Ola Hallengren's directory structure and naming convention but could easily be altered.
#SQLRestore.ps1
$Root = 'C:\SQLRestore'
$OPFileName = 'SQLRestore.sql'
$OPFile = "Junk"
$Folders = Get-ChildItem -Path $Root -recurse | where {($_.psiscontainer)}
ForEach ($Folder in $Folders)
{
If ($Folder.Name -eq "FULL")
{
Echo $Folder.FullName
$DB = Split-path (Split-Path $Folder.FullName -Parent) -Leaf
$LogPath = Join-Path (Split-Path $Folder.FullName -Parent) "LOG"
$ThisFile = Join-Path (Split-path (Split-Path $Folder.FullName -Parent) -Parent) $OPFileName
If ( -not ($OPFile -eq $ThisFile))
{
$OPFile = $ThisFile
If ((Test-Path $OPFile))
{
Remove-Item $OPFile -Force | Out-Null
}
}
$Items = Get-ChildItem -Path $Folder.FullName | Where-Object{!($_.PSIsContainer) -and ($_.Name -match "_FULL_")} | Sort-Object Name -descending
foreach ($Item in $Items)
{
$FullBackup = $Item.FullName
$LogBackupBase = $Item.FullName.Replace("_FULL_", "_LOG_").Replace("\FULL\","\LOG\").Replace('.bak','.trn')
Break
}
Write-Output "--*** $DB ***" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "RESTORE DATABASE [$DB]" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "FROM DISK = '$FullBackup'" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "WITH NORECOVERY;" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "GO" | Out-File -Filepath "$OPFile" -Append -width 8000
If (Test-Path ($LogPath))
{
$Items = Get-ChildItem -Path $LogPath | Where-Object{!($_.PSIsContainer) -and ($_.Name -match "_LOG_") -and ($_.FullName -ge "$LogBackupBase")} | Sort-Object Name
foreach ($Item in $Items)
{
$LogFile = $Item.FullName
Write-Output "RESTORE LOG [$DB]" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "FROM DISK = '$LogFile'" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "WITH NORECOVERY;" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "GO" | Out-File -Filepath "$OPFile" -Append -width 8000
}
}
Write-Output "RESTORE DATABASE [$DB] WITH RECOVERY;" | Out-File -Filepath "$OPFile" -Append -width 8000
Write-Output "GO" | Out-File -Filepath "$OPFile" -Append -width 8000
}
}
March 29, 2017 at 3:35 pm
John gave you a piece of advice earlier in this thread - learn the syntax for the RESTORE DATABASE and RESTORE LOG commands in SQL. Please at least think about it. If you understand the backups, then take the time to learn how to use them to restore. After all, the ultimate reason to take a backup is to be able to use them to restore. Yes, we can talk about log file sizes, etc., but the real goal is to be able to restore.
That said, you really should practice with them so you know you're comfortable doing a restore. It also confirms that your backups are viable and can be used.
The inefficiency of the GUI is another reason to learn the SQL statements.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply