Unblock Downloaded Applications Using PowerShell

    No Comments

    Anyone who has downloaded applications from the Internet has seen this warning:

    And many probably know you can go into properties and unblock that program:

    However, it’s also possible to find and unblock those files in PowerShell. The “Unblock-File” command will unblock the application even if the [Unblock] button has been removed by Group Policy.

    To find all the files in a folder that are blocked, you can use the

    "Zone.Identifier" stream:
    Get-Item * -Stream "Zone.Identifier" -ErrorAction SilentlyContinue

    And, you can pipe that into select to see only the filenames of the blocked executable files:

    Get-Item * -Stream "Zone.Identifier" -ErrorAction SilentlyContinue | select FileName

    Source: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/unblock-file?view=powershell-5.1

    Categories: PowerShell, Scripts

    List All Files to CSV and Copy to Your Local Machine

    No Comments

    I recently realized that the backup job I’ve been creating hasn’t been properly deleting the files. Using this script I’m able to find all files in a folder (and it’s subfolders) so I can determine which files should be deleted. Since this is quicker to do on the remote server than my local machine, the last part is to copy the CSV to C:\Temp on my laptop. The date calls are just there as indicators of how long each step takes.

    $today=$(Get-Date -Format u).Substring(0,10).replace("-","") date Get-ChildItem -Path "\\UNCServer\backup\directory\" -Recurse | Select-Object DirectoryName,BaseName, Extension, Length, CreationTime | Export-Csv -Path D:\Install\FileListing_$($today).csv date copy "D:\Install\FileListing_$($today).csv" "\\tsclient\C\Temp\" date
    Categories: PowerShell, Scripts

    Delete Old SQL Server Backups

    No Comments

    Part of any good backup plan is deleting  the old backups you don’t need anymore. This PowerShell script will find all the full and tLog backups older than 31 days and delete them. Because this is meant to be run as a step in a SQL Agent job, PowerShell will default to a SQL Server context. Starting the script with CD C: will switch that to the file system. Without this line you may get an error that the path cannot be found.

    CD C:
    $backupPath = "C:\Backup\full\User\"
    $tLogPath = "C:\Backup\tLog\"
    $limit = (Get-Date).Date.AddDays(-31)
    Get-ChildItem -Path $backupPath | Where-Object { $_.LastWriteTime -lt $limit } | Remove-Item -Force
    Get-ChildItem -Path $tLogPath | Where-Object { $_.LastWriteTime -lt $limit } | Remove-Item -Force
    Categories: PowerShell, Scripts, SQL Server

    Restore All DB Backups In a Folder

    No Comments

    It’s possible that you’d want to restore all the databases you have backed up to  a specific folder. This will generate the scripts to restore those and move the files to a new destination. Copy the output from this query into a new query window and modify as needed before running.

    Part of this code was taken from StackOverflow – I think this post in particular, but it’s hard to say for sure.

    declare @folder as varchar(1000) = 'C:\Backups\ '
    declare @moveToDrive as varchar(20) = 'M'
    declare @allFiles as table(id int identity(1,1),file_nm varchar(200), depth_no int, is_file bit, fullPath varchar(1200)
    ,restoreFilenames varchar(2000))
    DECLARE @fileListTable TABLE (
    [LogicalName] NVARCHAR(128),
    [PhysicalName] NVARCHAR(260),
    [Type] CHAR(1),
    [FileGroupName] NVARCHAR(128),
    [Size] NUMERIC(20,0),
    [MaxSize] NUMERIC(20,0),
    [FileID] BIGINT,
    [CreateLSN] NUMERIC(25,0),
    [DropLSN] NUMERIC(25,0),
    [ReadOnlyLSN] NUMERIC(25,0),
    [ReadWriteLSN] NUMERIC(25,0),
    [BackupSizeInBytes] BIGINT,
    [SourceBlockSize] INT,
    [FileGroupID] INT,
    [DifferentialBaseLSN] NUMERIC(25,0),
    [DifferentialBaseGUID] UNIQUEIDENTIFIER,
    [IsReadOnly] BIT,
    [IsPresent] BIT,
    [TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
    ,allFilesId int
    insert into @allFiles (file_nm, depth_no, is_file)
    EXEC Master.dbo.xp_DirTree @folder,1,1
    update @allFiles set fullPath = @folder + file_nm
    update @allFiles set restoreFilenames = 'RESTORE FILELISTONLY FROM DISK='''+fullPath+''''
    --select id, file_nm, fullPath,restoreFilenames
    --from @allFiles
    DECLARE @curFile_id as int, @restoreScript as varchar(2000)
    set @curFile_id = 1
    while @curFile_id <= (select MAX(id) from @allFiles)
    select @restoreScript=restoreFilenames from @allFiles where id=@curFile_id
    insert into @fileListTable ([LogicalName],[PhysicalName],[Type],[FileGroupName],[Size],[MaxSize],[FileID],[CreateLSN],[DropLSN],[UniqueID],[ReadOnlyLSN],[ReadWriteLSN],[BackupSizeInBytes],[SourceBlockSize],[FileGroupID],[LogGroupGUID],[DifferentialBaseLSN],[DifferentialBaseGUID],[IsReadOnly],[IsPresent],[TDEThumbprint])
    update @fileListTable set allFilesId=@curFile_id where allFilesId is null
    set @curFile_id += 1
    select allFilesId, LogicalName, @moveToDrive+SUBSTRING(PhysicalName,2,1000),
    ',MOVE '''+LogicalName+''' TO '''@moveToDrive+SUBSTRING(PhysicalName,2,1000)+''''
    from @fileListTable
    select id, '!'+SUBSTRING(file_nm, 1, PATINDEX('%_FULL%',file_nm)-1),'',
    'RESTORE DATABASE ['+SUBSTRING(file_nm, 1, PATINDEX('%_FULL%',file_nm)-1)+'] FROM DISK='''+fullPath+''' WITH STATS=3,NORECOVERY'
    from @allFiles
    ORDER BY allFilesId, LogicalName
    Categories: Scripts, SQL Server

    List all DBs, Tables, and Columns

    No Comments

    Sometimes it’s necessary or nice to export all the databases and tables on a server. This simple script will give you information about every table you have access. As a DBA, you can uncomment the “execute as…” and “revert” lines to determine what tables another user can see.

    -- execute as login = 'newUser'
    set nocount on
    Declare @Output as table (DBName varchar(200), TableName varchar(200), TAB_VIEW varchar(10), ColName varchar(200), ColType varchar(200), PK bit)
    declare @DBName as VARCHAR(100) = 'master'
    declare @SQL as NVARCHAR(max)
    declare @CurDB as int = 5
    while @CurDB <= (select MAX(database_id) from master.sys.databases)
    SELECT @DBName = name from master.sys.databases where database_id = @CurDB
    begin try
    print 'Checking ' + @DBName + '…'
    SET @SQL = 'select "' + @DBName + "' as DBName, tables.name as TableName, tables.TAB_VIEW, columns.name as ColName,
    case types.name
    when "varchar" then "varchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
    when "char" then "char(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
    when "nvarchar" then "nvarchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length/2),-1),0),"MAX") + ")"
    when "nchar" then "nchar(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length/2),-1),0),"MAX") + ")"
    when "binary" then "binary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
    when "varbinary" then "varbinary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
    when "numeric" then "numeric(" + convert(varchar(4),columns.precision) + "," + convert(varchar(4),columns.scale) + ")"
    when "decimal" then "decimal(" + convert(varchar(4),columns.precision) + "," + convert(varchar(4),columns.scale) + ")"
    when "float" then "float" + ISNULL(NULLIF("("+convert(varchar(4),columns.precision)+")","(53)"),"")
    –when "varbinary" then "varbinary(" + ISNULL(NULLIF(NULLIF(convert(varchar(4),columns.max_length) ,-1),0),"MAX") + ")"
    else types.name end as ColType–, columns.*
    ,isnull(PK.[PrimaryKey],0) as [PrimaryKey]
    from [' + @DBName + '].sys.columns
    inner join (SELECT NAME, object_id, is_ms_shipped, "TABLE" AS TAB_VIEW FROM [' + @DBName + '].sys.tables
    union SELECT NAME, object_id, is_ms_shipped, "VIEW" AS TAB_VIEW FROM [' + @DBName + '].sys.views ) as tables
    on tables.object_id = columns.object_id
    left join [' + @DBName + '].sys.types
    on types.user_type_id = columns.user_type_id
    outer apply (
    select case when si.object_id is not null then 1 else 0 end as [PrimaryKey]
    from [' + @DBName + '].sys.indexes si
    inner join [' + @DBName + '].sys.index_columns ic
    AND si.index_id = ic.index_id
    and si.is_primary_key = 1
    where si.object_id = tables.object_id
    and ic.column_id = columns.column_id
    ) as PK
    where tables.is_ms_shipped = 0'
    insert @Output
    exec sp_sqlexec @SQL
    print '... Success'
    end try
    begin catch
    print '... Error: ' + error_message()
    end catch
    set @CurDB += 1
    select * from @Output
    print 'COMPLETE!'
    -- revert


    Categories: Scripts, SQL Server