SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

Backups

Get More Done with SQLPSX

Feb 25th, 2010
by Aaron Nelson.
Comments are off for this post

In my LiveMeeting session for the AppDev Virtual Chapter of PASS yesterday I talked about building on top of tools that others had already built for you to use.  A great one for any DBA to use is the SQL PowerShell Extensions known as SQLPSX.  Even if you’ve never used PowerShell before you should take a look at this.  Heck even Sys Admins in shops that don’t have a DBA should have a look at this.  I have found the commands I have worked with very easy to use; even easier than T-SQL in some cases.

SQLPSX

After you download the files and RTFM you can then you can follow along on this next part.  Oh wait, first a little warning straight from Buck Woody:

Script Disclaimer, for people who need to be told this sort of thing:  

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

get-module -listAvailable

import-module SQLServer

Invoke-SqlBackup -sqlserver "WINX64ULT7\Kilimanjaro" -dbname "AdventureWorks" `

-filepath "C:\Temp\AdventureWorks_db_$(((Get-Date).ToString("yyyyMMddHHmm"))).bak"

Now this little script here (above) will backup a db for you and even include the current YearMonthDayHourMinute in the file string.  This one below will backup all of the non-system databases on your instance.  If you’re like me you’re thinking this doesn’t do anything that you can’t already do today with a maintenance plan.  That’s true and maybe I should have titled this post “Get Something Done with SQLPSX” but I will build on how you can leverage this more tomorrow.  For now, why don’t you add an AND clause only backup all the databases that start with ‘A’ ;-) 

foreach ($dbn in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" `

-database master -serverinstance WIN7\Kilimanjaro )

{

$k="C:\Temp\" + $($dbn.name) + "_db_$(((Get-Date).ToString("yyyyMMddHHmm"))).bak"WIN7\Kilimanjaro" -dbname $($dbn.name)  -filepath $k

$dbn; Invoke-SQLBackup -sqlserver "

}

I’ve gone ahead and included the SQLPSX help items here.  Please see if there’s one that catches your eye and see if it might work for you.

* Get-AgentAlert

* Get-AgentAlertCategory

* Get-AgentJob

* Get-AgentJobHistory

* Get-AgentJobSchedule

* Get-AgentJobServer

* Get-AgentJobStep

* Get-AgentOperator

* Get-AgentOperatorCategory

* Get-AgentProxyAccount

* Get-AgentSchedule

* Get-AgentTargetServer

* Get-AgentTargetServerGroup

* Set-AgentJobHistoryFilter

* Get-ReplArticle

* Get-ReplEnumLogReaderAgent

* Get-ReplEnumPublications

* Get-ReplEnumPublications2

* Get-ReplEnumSnapshotAgent

* Get-ReplEnumSubscriptions

* Get-ReplEnumSubscriptions2

* Get-ReplLightPublication

* Get-ReplMonitor

* Get-ReplPublication

* Get-ReplPublicationMonitor

* Get-ReplPublisherMonitor

* Get-ReplScript

* Get-ReplServer

* Get-ReplSubscriberSubscription

* Get-ReplSubscription

* Get-ReplTransPendingCommandInfo

* New-ReplMergePublication

* New-ReplScriptOptions

* New-ReplTransPublication

* Get-GroupUser

* Get-ShowMbrs

* New-ShowMbrs

* Set-ShowMbrs

* Out-SqlScript

* Test-SqlScript

* Add-SqlDatabase

* Add-SqlDatabaseRole

* Add-SqlDatabaseRoleMember

* Add-SqlDataFile

* Add-SqlFileGroup

* Add-SqlLogFile

* Add-SqlLogin

* Add-SqlServerRoleMember

* Add-SqlUser

* Get-Sql

* Get-SqlCheck

* Get-SqlColumn

* Get-SqlConnection

* Get-SqlData

* Get-SqlDatabase

* Get-SqlDatabasePermission

* Get-SqlDatabaseRole

* Get-SqlDataFile

* Get-SqlDefaultDir

* Get-SqlEdition

* Get-SqlErrorLog

* Get-SqlForeignKey

* Get-SqlIndex

* Get-SqlIndexFragmentation

* Get-SqlInformation_Schema.Columns

* Get-SqlInformation_Schema.Routines

* Get-SqlInformation_Schema.Tables

* Get-SqlInformation_Schema.Views

* Get-SqlLinkedServerLogin

* Get-SqlLogFile

* Get-SqlLogin

* Get-SqlObjectPermission

* Get-SqlPort

* Get-SqlProcess

* Get-SqlSchema

* Get-SqlScripter

* Get-SqlServer

* Get-SqlServerPermission

* Get-SqlServerRole

* Get-SqlShowMbrs

* Get-SqlStatistic

* Get-SqlStoredProcedure

* Get-SqlSynonym

* Get-SqlSysDatabases

* Get-SqlTable

* Get-SqlTransaction

* Get-SqlTrigger

* Get-SqlUser

* Get-SqlUserDefinedDataType

* Get-SqlUserDefinedFunction

* Get-SqlVersion

* Get-SqlView

* Invoke-SqlBackup

* Invoke-SqlDatabaseCheck

* Invoke-SqlIndexDefrag

* Invoke-SqlIndexRebuild

* Invoke-SqlRestore

* New-SqlScriptingOptions

* Remove-SqlDatabase

* Remove-SqlDatabaseRole

* Remove-SqlDatabaseRoleMember

* Remove-SqlLogin

* Remove-SqlServerRoleMember

* Remove-SqlUser

* Set-SqlData

* Set-SqlDatabasePermission

* Set-SqlObjectPermission

* Set-SqlServerPermission

* Update-SqlStatistic

* Copy-ISItemFileToSQL

* Copy-ISItemSQLToFile

* Copy-ISItemSQLToSQL

* Get-ISData

* Get-ISItem

* Get-ISPackage

* Get-ISRunningPackage

* Get-ISSqlConfigurationItem

* New-ISApplication

* New-ISItem

* Remove-ISItem

* Rename-ISItem

* Set-ISConnectionString

* Set-ISPackage

* Test-ISPath

How Big Were Last Nights Backups?

Feb 24th, 2010
by Aaron Nelson.
1 comment

Last week one of the Sys Admin’s asked me “How big were last night’s backups?”  I was like “Why are you asking me?  We drop all of the backup files onto one of your arrays so why don’t you tell me.”

Background:  We’re doing a Data Center move and they wanted to move a copy of all of the dbs over to the new DC to test the applications.  In some shops this might be easy because they just delete backups more than a day old.  For us, we are blessed with enough room to keep several days worth of backups for some of our critical systems.  This is further complicated by the fact that we have over 100 databases to move and they have different retention policies based on importance. 

I know that the databases take up 1.8 TB thanks to another PowerShell script I wrote but since we use backup compression our data is about a fifth of that size.  But what is the exact number?!  I’m glad you asked.

I threw together a script I had used previously and modified it so that would recurse down the directory structure of our db archive server and pickup on .bak files that were made in the last day.  Now, because nothing is simple where I work, there is a software product – which will remain nameless for now – that also backs up transaction log files with the .bak extension; but luckily also throw in _TLOG_ so I was able to filter them out pretty easily.

   1: $c=0

   2: $l=0

   3: foreach ($f in dir -include *.bak -exclude *TLOG* -recurse |

   4: where{ $_.LastWriteTime -gt [datetime]::Now.AddDays(-1) } )

   5: {$c += 1; $l += $f.length}

   6: "Total File Count: $c and total size of .bak files in Bytes: $l"

   7: >> c:\Temp\MyBackupFileSizes.txt

Now I was already in a directory where I wanted to get all the files in all of the sub-directories when I ran this so don’t forget to do that or this could run for a long time.  For you that might look like this (but probably won’t): C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Backup. 

I went and checked a bunch of these file sizes manually and they came out exactly correct :-)

If you’re new to PowerShell I hope you give this a try and come up with a way that it could be useful to you.