FFmpeg Scripts

    No Comments

    One of my duties at Church is recording the service and posting that online later in the week. This means I spend a fair amount of time each week working with video and audio recordings. One of my favorite tools that can handle both is FFmpeg. As a command-line utility, FFmpeg is a no-fuss utility that just works as you tell it. However, this also means you could spend a lot of time making sure you type exactly what you want.

    To assist with consistency, I’ve started using a series of Windows batch files that will make sure I’m using the same settings each time. This has become my favorite way to convert audio and video files.

    • Extract audio as wav.bat: This will extract the audio from any input file and convert it to .wav
    • Scale to 720p 29_97.bat: Most of the video files I work with are 1080p, 29.97 fps with high bitrates on audio and video. This script will change that to 720p with lower bitrates.
    • Extract AAC.bat: Like the .wav extract, this will extract the AAC. I use this to export the audio from videos I’ve compiled, so the script expects AAC format on the audio track of the video.
    • RunAllThroughBat.bat: On this one I pass in one of the other .bat files. So if I run this with “Extract AAC.bat”, it will find all the .mp4 videos in the current folder and extract the audio to .aac files.
    • Convert AVI to MP4.bat: Another that has a slightly unclear name. The source doesn’t have to be AVI, but that’s what I was using the script for at the time. I had recordings of several episodes from an old TV show. These recordings were made over a while and from different TV stations, so they lacked consistency. This converts them to MP4, normalizes the audio, and sets the movflag to fast start to make streaming better on Plex/Kodi. The script converts the file and moves it to my NAS where Plex picks it up.

    Extract audio as wav.bat

    set inFile=%1
    set outFile=%1.wav

    C:\ffmpeg\bin\ffmpeg.exe -i %inFile% -ac 1 -threads 4 %outFile%

    Scale to 720p 29_97.bat

    set inFile=%1
    set outFile=%1_720.mp4
    C:\ffmpeg\bin\ffmpeg.exe -i %inFile% -b:v 1500k -b:a 96k -vf "fps=29.97, scale=-1:720" -threads 4 %outFile%

    Extract AAC.bat

    set inFile=%1
    set outFile=%~n1.aac
    C:\ffmpeg\bin\ffmpeg.exe -i %inFile% -vn -codec:a copy "%outFile%"


    set fileType=mp4
    set runBat=%1
    for %%j in (*.%fileType%) do (
    %runBat% %%j

    Convert AVI to MP4.bat

    set inFile=%1
    set outFile=W:\Plex\TV\Show\SeasonX\%~n1.mp4
    C:\ffmpeg\bin\ffmpeg.exe -n -i %inFile% -c:v libx264 -codec:a libmp3lame -movflags faststart -filter:a loudnorm "%outFile%"
    Categories: FFmpeg, Scripts

    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