Copy Files and SubDirectories and keep folder structure

Copies a set of files recursively and recreates subfolders and files in another location.

# Copy XML files from subfolders to another drive and create subfolders.

$SourcePath = 'G:\Prod\'
$TargetPath = 'E:\SQLScripts\'
$FileFilter = '*.xml'

Get-ChildItem $SourcePath -filter $FileFilter -recurse |
    ForEach-Object { 
        $targetFile = $TargetPath + $_.FullName.SubString($SourcePath.Length); 
        $DestFolder = Split-Path $targetFile
        if (-not (Test-Path $DestFolder)) {
            New-Item -ItemType Directory -Path $DestFolder #-WhatIf
        }

        Copy-Item $_.FullName -destination $targetFile #-WhatIf
} 

Dates Explained

Here are some of the ways dates can be output.

declare @date datetime
set @date = getdate()
select 
	@date	as SampleDate,
	datepart(year,@date)	as 'year',
	datepart(month,@date) 	as 'month',
	datepart(day,@date) 	as 'day',
	datepart(week,@date) 	as 'week',
	datepart(quarter,@date) as 'quarter',
	datepart(dy,@date) 	as 'day of year',
	datepart(weekday,@date) as 'weekday',
	datepart(hour,@date) 	as 'hour',
	datepart(minute,@date) 	as 'minute' ,

	datename(month,@date) as 'month name',
	datename(weekday,@date) as 'weekday name',

	'fiscal year' =   
	case            
		when datepart(month,@date) >= 7
		then datepart(year,@date) + 1
		else datepart(year,@date)
	end,
	CONVERT(varchar(12), getdate() ,112) 	as 'today date as integer',
	CONVERT(varchar(12),DATEADD(d, -7 ,getdate()),112) 	as 'lastweek date as integer',
	CONVERT(varchar(12), getdate() ,108) 			as 'time',
	REPLACE(CONVERT(varchar(12), getdate() ,108),':','') 	as 'time as integer',
	dateadd(d,-1, cast(month(dateadd(mm,1,getdate())) as varchar) + 
		'/01/' + cast(year(getdate()) as varchar)) as [Last day of month],
	DATEADD(D,1, 
		cast(month( getdate() ) as varchar) + '-' +
		cast(day( getdate()) as varchar) + '-' +
		cast(year(getdate() ) as varchar) ) as [Midnight Tonight]

declare @tim varchar(8)
set @tim = REPLACE(CONVERT(varchar(12), getdate() ,108),':','')
select @tim,
case when len(@tim) >= 5 then
	substring(@tim,len(@tim)-5,2 ) + ':' +
	substring(@tim,len(@tim)-3,2 ) + ':' +
	substring(@tim,len(@tim)-1,2 )
else
	@tim
end 

Where are tables located in database files?

This uses indexes to locate the files.


SELECT  [o].[crdate],
        DATEDIFF(MINUTE, [o].[crdate], GETDATE()) AS [MinAgo],
        [o].[name] AS [Tablename],
        [i].[indid],
        [i].[name] AS [IndexName],
        [i].[rowcnt],
        [i].[groupid],
        [f].[name] AS [FileGroupName],
        [d].[file_id],
        [d].[physical_name],
        [s].[name] AS [DataSpace]
FROM    [sys].[sysobjects] [o]
JOIN    [sys].[objects] [so]
ON      [so].[object_id] = [o].[id]
JOIN    [sys].[sysindexes] [i]
ON      [i].[id] = [o].[id]
JOIN    [sys].[filegroups] [f]
ON      [f].[data_space_id] = [i].[groupid]
JOIN    [sys].[database_files] [d]
ON      [f].[data_space_id] = [d].[data_space_id]
JOIN    [sys].[data_spaces] [s]
ON      [f].[data_space_id] = [s].[data_space_id]
WHERE   [is_ms_shipped] = 0
        AND [i].[name] IS NOT NULL
ORDER BY [Tablename],
        [IndexName],
        [d].[file_id]


SQL: Where are the indexes located?

Where are the indexes located?

SELECT  'table_name' = OBJECT_NAME(i.id),
        i.indid,
        'index_name' = i.name,
        i.groupid,
        'filegroup' = f.name,
        'file_name' = d.physical_name,
        'dataspace' = s.name
FROM    sys.sysindexes i,
        sys.filegroups f,
        sys.database_files d,
        sys.data_spaces s
WHERE   OBJECTPROPERTY(i.id, 'IsUserTable') = 1
        AND f.data_space_id = i.groupid
        AND f.data_space_id = d.data_space_id
        AND f.data_space_id = s.data_space_id
        AND i.name LIKE 'ndx%'
ORDER BY f.name,
        OBJECT_NAME(i.id),
        groupid

g

SQL: Last Day Of Month

For any date go back a month, then add a month to it.

SELECT  [dtMonthLastDay] = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)  

Last day for any month etc. by Pinal Dave

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

[master_admin].[dbo].[fn_RunDuration]

CREATE FUNCTION [dbo].[fn_RunDuration] (@nRunDuration INT)
RETURNS VARCHAR(128)
AS 
BEGIN 
    DECLARE @vcMsg VARCHAR(128),
        @nH INT,
        @nM INT,
        @nS INT,
        @vcH VARCHAR(48),
        @vcM VARCHAR(48),
        @vcS VARCHAR(48)
    SELECT  @nH = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 6), 2),
            @nM = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 4), 2),
            @nS = RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 2),
            @vcH = CASE WHEN @nH = 0 THEN ''
                        WHEN @nH = 1 THEN CAST(@nH AS VARCHAR) + ' hour, '
                        ELSE CAST(@nH AS VARCHAR) + ' hours, '
                   END,
            @vcM = CASE WHEN @nM = 0 THEN ''
                        WHEN @nM = 1 THEN CAST(@nM AS VARCHAR) + ' minute, '
                        ELSE CAST(@nM AS VARCHAR) + ' minutes, '
                   END,
            @vcS = CASE WHEN @nS = 1 THEN CAST(@nS AS VARCHAR) + ' second'
                        ELSE CAST(@nS AS VARCHAR) + ' seconds'
                   END,
            @vcMsg = @vcH + @vcM + @vcS

    RETURN @vcMsg
END

[master_admin].[dbo].[fn_INTtoDateTime]

CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
     @vcDate VARCHAR(8),
     @vcTime VARCHAR(6)
    )
RETURNS DATETIME
AS 
BEGIN
    SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
    SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)

    IF @vcDate = '00000000' 
        SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
                                                   '-', ''), ':', ''),
                                   SPACE(1), ''), 8)
	
    RETURN (  
	CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' + 
		SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2) 
	AS DATETIME)
	)
END

SQL Agent: What ran last night after time …

------------------------------------------------------------------------------------------------------------------------
--
-- What ran last night after time ...
--
SELECT  [master_admin].[dbo].[fn_INTtoDateTime]([run_date], [run_time]) AS [RunDate],
        DATENAME(WEEKDAY, CONVERT(VARCHAR(10), [run_date], 112)),
        [j].[name],
        [master_admin].[dbo].[fn_RunDuration]([run_duration]),
        [h].[step_id],
        [s].[step_name],
        [command] AS [StepCommand],
        [instance_id]
FROM    [msdb].[dbo].[sysjobhistory] [h] WITH (NOLOCK)
JOIN    [msdb].[dbo].[sysjobs] [j] WITH (NOLOCK)
ON      [j].[job_id] = [h].[job_id]
JOIN    [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
ON      [s].[job_id] = [j].[job_id]
        AND [s].[step_id] = [h].[step_id]
WHERE   [run_date] >= CONVERT(VARCHAR(8), DATEADD(DAY, -0, GETDATE()), 112)

AND [h].[run_time] >= 021439
ORDER BY [RunDate]

Required objects
[fn_INTtoDateTime]
[fn_RunDuration]

 

What are all those old tempdb temp tables?

SQL Server caches the definitions of temp tables and table variables, to reduce the load on the system catalog in tempdb on busy systems which creates a lot of temp tables. Typically they have a name that starts with # and is followed by eight hex digits. -Erland Sommarskog

SQL Server Temporary Table Caching

This tip will describe the condition to allow caching of a SQL Server temporary table and a demonstration to benchmark the performance between a cached vs. non-cached temporary table.

Source: www.mssqltips.com/sqlservertip/4406/sql-server-temporary-table-caching/