It's been a good run doing a post every day. I'm scaling it back a bit. I liked getting these 36 posts out one a week day for some time now. I'm going to scale back. I"m not sure yet if I'll go down to one or two a week. I have the book giveaway post on the 30th and the winner announcement on the 1st. It's been a fun month, that's for sure. I want to hope for more quality and less rushing to push something out. I'm really curious what I can do with proper time.

Simple PowerShell Backup Script

There are better ways to do this. I welcome your responses and advice. I'm just now dabbling in powershell. I know Sean McCown has a large library of powershell videos. I think that will be my new years resolution. Below is my first day working with powershell.

This is my powershell script that I've added to the windows task manager. Before we shred the idea here... We needed backups taken for an Express box. I'm sure scripts exist out there and no one should reinvent the wheel, but I wanted to take a stab at it to learn a bit more.

sqlcmd -S .\servername -i E:\Tasks\Backupalldatabases.sql

$Path = "E:\Backups\master"
$Daysback = "-3"

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item

$Path = "E:\Backups\msdb"
$Daysback = "-3"

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item

$Path = "E:\Backups\model"
$Daysback = "-3"

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item

$Path = "E:\Backups\Main"
$Daysback = "-3"

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item

$Path = "E:\Backups\Restore"
$Daysback = "-3"

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item

$Path = "E:\Backups\Test"
$Daysback = "-3"

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item

This calls a SQL script that is a bit more basic.

exec sp_MSforeachdb @command1 = '
use ?
if ''?'' not like ''tempdb''
begin
declare @backup varchar (1000)
declare @date varchar (100)
set @date = (select cast(cast(Getdate() as char(11)) as date))
set @backup =
(''BACKUP DATABASE ['' +
db_name() + ''] TO  DISK = N''''E:\Backups\'' +
DB_NAME() + ''\'' +
db_name() +
'' '' +
@date +
''.bak'''' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'''''' +
db_name() + ''-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'')
exec (@backup)
end'

I have found the way to have something delete anything in all sub folders with a date older than 3 days which would would work easier than hand scripting all this out.... but I haven't put it in play yet.