Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DBA On The Go

I've been working in Enterprise IT environments for roughly 10 years. My move to become a DBA was new, but expected. I've worked in secure DoD databases and secure Pharmaceutical databases. One thing to note, the security aspect isn't a lot different. My goal is to marry Databases and security in a functional manner and see how we can speed these things up with minimal work.

Powershell Backups and Post Frequency Change

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.


Comments

Leave a comment on the original post [www.dbaonthego.com, opens in a new window]

Loading comments...