Powershell backup script not making weekly backups

Question

So i have the following code that should backup every database daily, weekly and monthly. The daily backup works fine but it doesnt seem to create a weekly or a monthly backup.

The database connection part of the script looks like:

$serverName = "."
$backupDirectory = "\\SERVER\BACKUP"
$daysToStoreDailyBackups = 7
$daysToStoreWeeklyBackups = 28
$monthsToStoreMonthlyBackups = 3

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$serverName
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "sa"
$mySrvConn.Password = "myPAssword"

$server = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)

$dbs = $server.Databases
$startDate = (Get-Date)
"$startDate"

The daily and weekly part of the script looks like:

foreach ($database in $dbs | where {$_.IsSystemObject -eq $False})
{
    $dbName = $database.Name      

    if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
    {
        $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
        $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_daily.bak"

        $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
        $smoBackup.Action = "Database"
        $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
        $smoBackup.BackupSetName = $dbName + " Backup"
        $smoBackup.Database = $dbName
        $smoBackup.MediaDescription = "Disk"
        $smoBackup.Devices.AddDevice($targetPath, "File")
        $smoBackup.SqlBackup($server) 
        "backed up $dbName ($serverName) to $targetPath"    
    }
    else
    {
        "$dbName backup skipped"
    }
}

if([Int] (Get-Date).DayOfWeek -eq 0)
{
    Get-ChildItem "$backupDirectory\*_weekly.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreWeeklyBackups)} |% {Remove-Item $_ -force }
    "removed all previous daily backups older than $daysToStoreWeeklyBackups days"

    foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
    {
        if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
        {
            $dbName = $database.Name      

            $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
            $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_weekly.bak"

            $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
            $smoBackup.Action = "Database"
            $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
            $smoBackup.BackupSetName = $dbName + " Backup"
            $smoBackup.Database = $dbName
            $smoBackup.MediaDescription = "Disk"
            $smoBackup.Devices.AddDevice($targetPath, "File")
            $smoBackup.SqlBackup($server) 
            "backed up $dbName ($serverName) to $targetPath"
        } 
        else
        {
            "$dbName backup skipped"
        }                   
    }
}

I assumed it would create the weekly backup on Sunday, but that didn't happen.

Bets that the last daily backup output is "ReportServerTempDB backup skipped" ? Follow through what happens on Sunday if that's the case .. — TessellatingHeckler
– TessellatingHeckler, Commented Jun 12, 2017 at 23:58
Thanks for the response, yes you are correct, i changed the dayOfWeek = 2 and it seems to just skip the weekly backup. Removing that if else block inside the weekly and monthly worked. but ofcourse it backups up the ReportServer DB too which i don't really want. — Owain Esau
– Owain Esau, Commented Jun 13, 2017 at 0:30

TessellatingHeckler · Accepted Answer · 2017-06-13 00:49:55Z

2

In the daily block you have

$dbName = $database.Name      

if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
{

but in the weekly block, you have

if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
{
    $dbName = $database.Name

dbName is checked before it is set. If it fails the check, it's never set for any database.

If your code comes out of the daily block value left as "ReportServerTempDB", it will never pass the if test in the weekly block, and dbName will not change, so nothing will be backed up.

a) Move the $dbName = $database.Name assignment above the if

b) Or use $database.Name directly

c) Or don't have big chunks of copy-paste code which have to be the same, but can somehow end up different. Instead move that duplicate chunk to one function and call it from both places.

answered Jun 13, 2017 at 0:49

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell backup script not making weekly backups

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related