db:mssql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
db:mssql [2021/10/04 14:35] – [MSSQL. SQL administration] admindb:mssql [2021/11/03 09:53] (current) – admin
Line 61: Line 61:
 <code SQL> <code SQL>
 -- # Backup Restore/Mssql -- # Backup Restore/Mssql
 +
 +-- get backup set
 +SELECT TOP 100
 +  name,
 +  type, *
 +FROM
 +  msdb..backupset
 +WHERE 1=1 
 + -- and database_name like '%adm%'
 +ORDER BY
 +backup_set_id DESC
  
 -- delete database -- delete database
Line 126: Line 137:
 </code> </code>
 ++++ ++++
 +
 +
 +++++ --- SCRIPT - for archive all files except ZIP|
 +<code BASH>
 +Function Zip
 +{
 + Param
 + (
 + [string[]]$toBeZipped
 + )
 +    if ($null -eq $toBeZipped) {return}
 + $CurDir = Get-Location
 + Set-Location "C:\program files\7-zip\"
 + foreach ( $f in $toBeZipped)
 +        {
 +        echo "$(Get-Date -Format o) zip file $f "
 +    .\7z.exe A -tzip "$f.zip" $f | Out-Null
 +        }
 + Set-Location $CurDir
 +}
 +
 +
 +Function ZipFiles
 +{
 + Param
 + (
 + [string]$zipFile
 + ,
 + [string[]]$toBeZipped
 + )
 + $CurDir = Get-Location
 + Set-Location "C:\program files\7-zip\"
 + .\7z.exe A -tzip $zipFile $toBeZipped | Out-Null
 + Set-Location $CurDir
 +}
 +
 +
 +
 +# Code 
 +# get-childitem $path -recurse -exclude *.cs,*.tt,*.xaml,*.csproj,*.sln,*.xml,*.cmd,*.txt
 +$bckpdir=$args[0]
 +if ($null -eq $bckpdir){ 
 + echo "$(Get-Date -Format o) no directory in param in start"
 + exit 1
 +}
 +
 +$CheckFile = Test-Path -Path $bckpdir
 +
 +
 +if (!($CheckFile))  {
 + echo "$(Get-Date -Format o) $bckpdir  directory not exist "
 + exit 1 
 +} 
 +echo "$(Get-Date -Format o) --- start compress $bckpdir"
 +
 +
 +
 +$files = Get-ChildItem $bckpdir\*.* -exclude *.zip | Where-Object {$_.CreationTime -lt ((Get-date).adddays(+1))}
 +Zip $files
 +foreach ( $f in $files)
 +{
 + If(Test-Path "$f.zip")
 +   {
 +     Remove-Item $f
 +     echo "compress ok Delete $f"
 +   }
 +}
 +
 +echo  "$(Get-Date -Format o) === stop"
 +</code>
 +++++
 +
 +
 +++++ --- SCRIPT - for archive ZIP all files except last one for sort in time|
 +<code BASH>
 +Function Zip
 +{
 +  Param
 +  (
 +    [string[]]$toBeZipped
 +  )
 +    if ($null -eq $toBeZipped) {return}
 +  $CurDir = Get-Location
 +  Set-Location "C:\program files\7-zip\"
 +  foreach ( $f in $toBeZipped)
 +        {
 +        echo "$(Get-Date -Format o) zip file $f "
 +     .\7z.exe A -tzip "$f.zip" $f | Out-Null
 +        }
 +  Set-Location $CurDir
 +}
 +
 +
 +Function ZipFiles
 +{
 +  Param
 +  (
 +    [string]$zipFile
 +    ,
 +    [string[]]$toBeZipped
 +  )
 +  $CurDir = Get-Location
 +  Set-Location "C:\program files\7-zip\"
 +  .\7z.exe A -tzip $zipFile $toBeZipped | Out-Null
 +  Set-Location $CurDir
 +}
 +
 +
 +
 +# Code 
 +# get-childitem $path -recurse -exclude *.cs,*.tt,*.xaml,*.csproj,*.sln,*.xml,*.cmd,*.txt
 +$bckpdir=$args[0]
 +if ($null -eq $bckpdir){ 
 + echo "$(Get-Date -Format o) no directory in param in start"
 + exit 1
 +}
 +
 +$CheckFile = Test-Path -Path $bckpdir
 +
 +
 +if (!($CheckFile))  {
 + echo "$(Get-Date -Format o) $bckpdir  directory not exist "
 + exit 1 
 +} 
 +echo "$(Get-Date -Format o) --- start compress $bckpdir"
 +
 +
 +
 +$files = Get-ChildItem $bckpdir\*.* -exclude *.zip | sort -Property CreationTime
 +
 +if ($files -is [array])
 +{
 +$files = $files[0..($files.Count - 2)]
 +Zip $files
 +foreach ( $f in $files)
 +{
 + If(Test-Path "$f.zip")
 +   {
 +     Remove-Item $f
 +     echo "compress ok Delete $f"
 +   }
 +}
 +}
 +else
 +{
 +   echo  "$(Get-Date -Format o) return is not array - only one file $files"
 +}
 +echo  "$(Get-Date -Format o) === stop"
 +</code>
 +++++
 +
 +
 +++++ --- SCRIPT - clear age files in directory |
 +<code BASH>
 +$bckpdir=$args[0]
 +if ($null -eq $bckpdir){ 
 + echo "$(Get-Date -Format o) no directory in param in start"
 + exit 1
 +}
 +
 +$numdays=$args[1]
 +if ($null -eq $numdays -or -not $numdays -match '^[0-9]+$' -or $numdays -lt 3 ){ 
 + echo "$(Get-Date -Format o) no days in integer or less than 3 in param "
 + exit 1
 +}
 +
 +
 +$CheckFile = Test-Path -Path $bckpdir
 +
 +
 +if (!($CheckFile))  {
 + echo "$(Get-Date -Format o) $bckpdir  directory not exist "
 + exit 1 
 +} 
 +echo "$(Get-Date -Format o) --- start clear $bckpdir keep $numdays days"
 +
 +$files = Get-ChildItem –Path $bckpdir | Where-Object {($_.CreationTime -lt (Get-Date).AddDays(-$numdays))} 
 +if ($null -eq $files){ 
 + echo "$(Get-Date -Format o) === stop no files to delete"
 + exit 0
 +}
 +echo $($files| Format-Table  -HideTableHeaders)
 +$files | Remove-Item
 +</code>
 +++++
 +
 +
 +
 ===== MSSQL.bulk copy to file ===== ===== MSSQL.bulk copy to file =====
 <code SQL> <code SQL>
  • db/mssql.1633358156.txt.gz
  • Last modified: 2021/10/04 14:35
  • by admin