Create backups for databases that were not backed up during last 1 day


 

a) Install SQL Server 2008. You can get RC0 from here: Download SQL 2008 RC0


b) Launch SQL Server Management  Studio ( Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio)


c) Connect to SQL Server instance, Right click on the server node in Object Explorer and click on “Start Power Shell” menu


image


d) Get the list of all databases and their last backup date on this SQL instance


dir Databases | format-table Name, LastBackupDate


image


e) Backup all databases that were not backed up in last 1 day


$databasesNotBackedUp = dir Databases | where-object { $_.LastBackupDate le [DateTime]::Now.AddDays(-1)}

$server = (get-item .)

foreach(
$db in $databasesNotBackedUp)
{
        # construct a unique backup file name
        $backupfilepath = $server.BackupDirectory + “\” + $db.Name + [DateTime]::Now.ToString(‘s’).Replace(“:”,“-“) + “.bak”
       
        $backup = new-object (‘Microsoft.SqlServer.Management.Smo.Backup’)
        $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
        $backup.Database = $db.Name
        $backup.Devices.AddDevice($backupfilepath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

        Write-Host “Backing up database: “, $db.Name
        $backup.SqlBackup($server)
        Write-Host “Database:” , $db.Name , ” backed up to: “, $backupfilepath
}


 image


This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


Comments (0)