Powershell Folder Size Specific Folder Name

Powershell script to loop through a folder looking for specific folder names and reporting back the folder size. Our DIFF backups were stored in a folder under the server name and I needed a way to see the size of all DIFF folders.

$sourceDirectory = "\\path to your folder\folder"
$folderName = "Diff"

$colItems = (Get-ChildItem $sourceDirectory -recurse | Where-Object {$_.PSIsContainer -eq $true -and $_.Name -match $folderName} )
foreach ($i in $colItems)
{
    $subFolderItems = (Get-ChildItem $i.FullName | Measure-Object -property length -sum)
    $i.FullName + " -- " + "{0:N2}" -f ($subFolderItems.sum / 1MB) + " MB" 
}

Msg 2570 DBCC CHECKDB

Here is how I worked through a handful of Msg 2570 errors I received from DBCC CHECKDB. Fortunatley the errors were on a DEV system were I was afforded the luxury of updating the data with no problems. If this is a production machine then you need to tread more lightly and work with the application owners to make sure you restore the data as best as possible.

Msg 2570 errors will not be fixed by running the REBUILD_REPAIR option. You have to address the issues with the data.

Sample Error:

Msg 2570, Level 16, State 3, Line 1
Page (1:2718613), slot 22 in object ID 1079167090, index ID 0, partition ID 72057594249084928, alloc unit ID 72057594292207616 (type "In-row data"). Column "column1" value is out of range for data type "decimal".  Update column to a legal value.
CHECKDB found 0 allocation errors and 6 consistency errors in table 'table_name' (object ID 1079167090).

Using the Page and Slot information from the error above you use the DBCC PAGE procedure to find the data page. The table name and database name will be listed in the DBCC error output.

DBCC PAGE ('Database_Name',1,2718613,3) WITH TABLERESULTS

Several rows we be output here. Use your slot number to target the bad data. Sample output

dbcc_BadData

Once you target the row that contains the bad data a simple UPDATE statement can be executed to fix the data. Again I was in a DEV system so I could update the data without much thought. Proceed carefully in production. Once you have fixed all the Msg 2570 errors take a database backup and re-run the DBCC CHECKDB.

Powershell Dump all tables to CSV SQL Server

I searched around for a while but could not find a script to do this. So I had to piecemilled the following script from a couple different examples. The script will loop through all the tables on the specified SQL Server database and export the data to CSV format. There will be a CSV file for every table.

#
# Date: 5/8/2014
# Purpose: Dump all tables from specified SQL Server database to CSV
# Notes: Change the Server, Database and extract file location as needed
#

$server = "SERVERNAME\INSTANCE"
$database = "DATABASE_NAME"
$tablequery = "SELECT name from sys.tables"

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()



# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])]"
    
    #Specify the output location of your dump file
    $extractFile = "C:\mssql\export\$($Row[0]).csv"
    
    $command.CommandText = $queryData
    $command.Connection = $connection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation
}