Copy Files and SubDirectories and keep folder structure

Leave a reply

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

Leave a reply

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?

Leave a reply

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?

Leave a reply

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

Leave a reply

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]

Leave a reply 
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]

Leave a reply 
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 …

Leave a reply 
------------------------------------------------------------------------------------------------------------------------
--
-- 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?

Leave a reply

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/