There is more than one way to find database backup files

I was told during the initial implementation of Ola Hallengren's Maintenance Solution at my workplace that it was too hard to find database backup files in the default folder structure his solution creates.  The concern was that it took too long to find the oldest file or the biggest file when trying to resolve a space issue on a database server.



Ola's solution creates a folder tree with these elements. Drive:\root\ServerName\DatabaseName\TypeOfBackup



You specify the backup drive and root folder using the Directory parameter of the DatabaseBackup stored procedure.


When I originally solved this problem, Windows XP was in use.  I wish I had spent more time figuring this out because I had to modify Ola's database backup procedure to dump all the backups into one folder in order for the DBA teams to sign off on the initial deployment.


It only takes a couple of minutes to search using Windows Explorer in Windows XP but finding files in a folder tree has gotten much easier on Windows 7 and higher.  



Given Windows XP's imminent retirement, I will only cover Windows 7 or higher.










GUI

Start Windows Explorer and navigate to the backup root folder on the server.


Type *.bak in the Search field in the upper right hand corner of Windows Explorer on Windows 7 or 8 and press Enter.


By default, Windows Explorer in Windows 7 and higher searches sub-folders.

The Search result returns all the database backups
in the entire folder tree.

The two requirements given were finding files by size or date.



Both are easily solved by clicking on the appropriate header. 

In the search result, headers are clickable and allow sorting by any column. 

So, click on Date Modified to find files by date or click on Size to find files by size.

Finding files to delete by size or date is done in less than a minute.

You can even save searches now.

http://www.howtogeek.com/howto/5316/how-to-save-searches-in-windows-7/

Microsoft's GUI interfaces are sufficient if you only have a few servers to check. But GUIs don't scale when you need to do the same task on a few hundred servers. So, you'll need to do a bit of scripting.  

PowerShell

Google answered this question pretty quickly.

Find the ten largest files in a directory.

http://stackoverflow.com/questions/798040/find-the-10-largest-files-within-a-directory-structure



get-childitem -path  C:\Backup -recurse | ?{ -not $_.PSIsContainer } | sort-object Length -desc | select-object fullname -f 10

gci C:\Backup -r |  ?{ -not $_.PSIsContainer } | sort Length -desc | select fullname -f 10



Finding the oldest ten files in a folder tree by date

get-childitem C:\Backup  -recurse | ?{ -not $_.PSIsContainer } | sort-object LastWriteTime | select-object fullname -f 10

gci C:\Backup  -r | ?{ -not $_.PSIsContainer } | sort LastWriteTime | select fullname -f 10

gci \\ServerName\FolderName\dump_data  -r |  ?{ -not $_.PSIsContainer } | sort Length -desc | select fullname -f 10



Including ?{ -not $_.PSIsContainer } in the pipeline ensures only files not folders are included in the results. 



If you want to run these commands on multiple servers, wrap them in a foreach loop that reads a list of text files from a server. Check out my PowerShell posts for examples. 



Old School

If you are not comfortable with PowerShell, the venerable dir command from the Windows command line is still available.



Oldest files first in a folder tree

dir /S /OD | more


Files ordered by size largest first in folder tree

dir /S /O-S | more



So, it doesn't take very long to find the largest or oldest database backup using any of these methods.  Which means I didn't need to modify Ola's scripts for the next release. ;-)

SQL Saturday #85

I'll be speaking at SQL Saturday #85 this weekend in Orlando.

Here's another example we'll review during my session "PowerShell by Example".

This example uses the SQLPSX 1.6 function get-sqlserver to return server properties.




This example was inspired by @PaulRandal's Ponderings on the instance-wide fillfactor setting in his SQLskills Insider Tips newsletter of 9/3/2011.  

To become a SQLskills insider, go here.




Looking forward to seeing everyone at SQL Saturday #85!

   1:  # In response to Paul Randal's Ponderings of Sep 3, 2011 regarding fill factor set at the instance level...

   2:  # His Call To Action is to verify it is set to 100. This script provides the basic means to check it on multiple instances.  

   3:  # The results are sent to a csv file

   4:   

   5:  $FilePath = "C:\Output"

   6:  $OutFile = Join-Path -path $FilePath -childPath ("ServerWide_FillFactor" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

   7:   

   8:  # Provide a list of servers one way or another...

   9:  $Servers = 'RED50\SQLEXPRESS'

  10:  #$Servers = get-content 'C:\Input\Servers.txt'

  11:   

  12:  @(

  13:  foreach ($svr in $Servers)

  14:  {

  15:      $s = get-sqlserver $svr

  16:      $s.Configuration | select parent,{$_.Fillfactor.RunValue}

  17:      

  18:  }

  19:  ) | export-csv -noType $OutFile

Execute Guardium SQL Server Permissions Script on Multiple Servers

# SQLSaturday#85 in Orlando is September 24, 2011

# I'll be presenting "PowerShell by Example"

# Here's a typical example we will review.

#

# Want more?  Come to my session at 11 AM.

#

# Need more info on SQL Saturday Orlando, go here.

#

# Set permissions on multiple servers for GUARDIUM Vulnerability Assessment 
# Pre-requisites:
# PowerShell V1
# SQLPSX 1.6.1
# add-pssnapin sqlserverprovidersnapin100
# add-pssnapin sqlservercmdletsnapin100


1:  $serverName = "SQL999"

   2:  $databaseName = "InventoryDB"

   3:   

   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"

   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")

   6:   

   7:  # here string contains SQL query

   8:  $qry = @"

   9:  SELECT  DISTINCT

  10:          ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM

  11:                                         WHEN 'DEFAULT' THEN ''

  12:                                         ELSE '\'

  13:                                       END + CASE a.INST_NM

  14:                                               WHEN 'DEFAULT' THEN ''

  15:                                               ELSE ISNULL(a.INST_NM, '')

  16:                                             END AS InstanceName

  17:  FROM    InventoryDatabase

  23:   "@

  24:   

  25:  $Servers = Get-SqlData $serverName $databaseName $qry

  26:   

  27:  Start-Transcript $OutFile

  28:   

  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"

  30:   

  31:  foreach ($svr in $Servers)

  32:  {

  33:      $serverName = $svr.InstanceName

  34:      Write-Host $serverName

  35:      trap {"Oops! It failed. $_"; continue } Invoke-Sqlcmd -InputFile $ScriptToExecute  -verbose -ServerInstance $serverName

  36:  }

  37:   

  38:  Stop-Transcript

How cluttered is MSDB?

# My quick and dirty PowerShell script to check for the oldest entry in MSDB.

# I'm using a SQLPSX 1.6 function Get-SqlData to execute queries. 

# Runs against multiple servers

   1:  $start = get-date

   2:  write-host "Start: "  $start

   3:   

   4:  $serverName = "SERVER\INSTANCE"

   5:  $databaseName = "dbINVENTORY"

   6:   

   7:  $FilePath = "C:\Output"

   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

   9:   

  10:  # here string contains SQL query

  11:  $qry = @"

  12:  SELECT DISTINCT

  13:          ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM

  14:                                         WHEN 'DEFAULT' THEN ''

  15:                                         ELSE '\'

  16:                                       END + CASE INST_NM

  17:                                               WHEN 'DEFAULT' THEN ''

  18:                                               ELSE ISNULL(INST_NM, '')

  19:                                             END AS InstanceName

  20:  FROM    dbInventory A

  24:  ORDER BY InstanceName

  25:  "@

  26:   

  27:  $Servers = Get-SqlData $serverName $databaseName $qry

  28:   

  29:  $qry = @"

  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset 

  31:  "@

  32:  $databaseName = "msdb"

  33:   

  34:  # Version inventory

  35:  @(

  36:  foreach ($svr in $Servers)

  37:  {

  38:      $serverName = $svr.InstanceName

  39:      trap {"Oops! Query failed. $_"; continue } Get-SqlData $serverName $databaseName $qry

  40:      

  41:  }

  42:  )  | export-csv -noType $OutFile

  43:   

  44:  $end = get-date    

  45:  write-host "End: "  $end

SQL Rally abstracts submitted

I've submitted two abstracts for the SQLRally.

If you're interested in learning PowerShell by example or want to hear how I automated our login provisioning and compliance reporting. Vote for my sessions.

It's a win-win situation in the PowerShell track for attendees no matter who you vote for.

Another great bunch of abstracts in the DBA track.

Get out and vote.

SQL Saturday #62

I'll be speaking at SQL Saturday #62 in Tampa, FL on January 15, 2011.





I'm presenting a session called "Why Learn PowerShell" because there was a request for a beginning PowerShell session.  Tons of speaker submissions for this SQL Saturday so the organizers gave most speakers one slot to give the maximum amount of people the chance to speak.  They are still trying to secure additional space to expand the schedule.

   

If you have any interest in learning PowerShell, I encourage you attend this session.  

I'll explain why it's worth learning and provide REAL WORLD examples that I use regularly.

To further convince you that SQL Saturday is worth your time consider the following:

• The variety of topics is pretty amazing for a FREE event.


• Local, regional, and national speakers will present.


• Several 2010 PASS Summit presenters will speak.


• The Microsoft Scripting Guy and The Scripting Wife will be attending


• A local Microsoft Certified Master will be presenting on the Parallel Data Warehouse.


• The best lunch of any SQL Saturday will be served.

I've also registered for the Day of Data session "Virtualization and Storage for the DBA" by Denny Cherry being held the day before at the Italian Club in Ybor City.

I encourage all IT Pros to attend this event.