Mssql

-- Get data for mssql
USE ISOJ
GO 
 
-- main info about db
sp_helpdb ISOJ
GO
 
-- space used for DB
sp_spaceused 
GO
 
-- all databases
SELECT a.name, a.is_temporal_history_retention_enabled
FROM sys.databases  a;

-- # 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
USE [master];
--ALTER DATABASE [WAdmIsk] SET SINGLE_USER ;
--DROP DATABASE [WAdmIsk];
 
DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';
 
BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;
 
# backup FULL
/* */
USE WAdmIsk;
BACKUP DATABASE WAdmIsk TO DISK = 'F:\Backups\temp\WAdmIskFull.bak' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup of WAdmIsk';
BACKUP DATABASE [WAdmIsk] TO DISK='F:\Backups\temp\WAdmdiff20210901_1709.bak' WITH DIFFERENTIAL, MEDIANAME = 'SQLServerBackups', NAME = 'Diff-Backup of WAdmIsk';
GO
 
 
 
# Restore
USE [master]
RESTORE DATABASE [WAdmIsk] FROM  DISK = N'/mnt/backup/WAdmIsk_backup_2021_09_02_081951_4217315.bak' WITH  FILE = 1,  MOVE N'WAdmIsk' TO N'/var/opt/mssql/data/WAdmIsk.mdf',  MOVE N'WAdmIsk_log' TO N'/var/opt/mssql/data/WAdmIsk_1.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE DATABASE [WAdmIsk] FROM  DISK = N'/mnt/backup/WAdmIsk_backup_2021_09_02_120927_8075561.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [WAdmIsk] FROM  DISK = N'/mnt/backup/WAdmIsk_backup_2021_09_02_121428_1305931.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO
 
-- проверка прогресса backup
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete, dateadd(SECOND,estimated_completion_time/1000, getdate()) AS estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE');
 
 
-- проверить корректность
RESTORE VERIFYONLY FROM DISK = 'C:\tmp\Server2008\isoj_2020.bak'
GO
 
-- посмотреть информацию
RESTORE HEADERONLY   
FROM DISK = N'C:\tmp\Server2008\isoj_2020.bak' ;  
GO 
 
-- восстановить с переносом файлов - с возвратом сообщения раз в 5 %
USE [master]
RESTORE DATABASE [WAdmIsk] FROM  DISK = N'C:\tmp\Server2008\isoj_2020.bak' WITH  FILE = 1,
  MOVE N'isoj' TO N'd:\isoj\isoj.mdf',  MOVE N'isoj_log' TO N'd:\isoj\isoj._1.ldf',    NOUNLOAD,  STATS = 5

-- Get data for mssql
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

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"

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"

$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

--https://stackoverflow.com/questions/32505775/rebuild-all-indexes-in-a-database
 
--check fragmentation
SELECT dbschemas.[name] AS 'Schema',
dbtables.[name] AS 'Table',
dbindexes.[name] AS 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] LIKE '%%'
ORDER BY indexstats.avg_fragmentation_in_percent DESC
 
-- process fragmentation
USE ISOJ;
 
DECLARE @TableName VARCHAR(255)
DECLARE @SQL NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@SQL)
DECLARE @msg VARCHAR(200) = 'process index completed. '+@TableName;
    RAISERROR(@msg, 1, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:01';
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

-- Sector  
SELECT 'cappeal', 
YEAR(CASE WHEN c.date IS NULL THEN co.date ELSE c.date END) AS  YEAR,
COUNT(*) AS cnt,
SUM(CASE WHEN co.id IS NULL THEN 1 ELSE 0 END) AS cntForLoad
FROM ISOJ2018.dbo.cAppeal c
FULL OUTER JOIN isoj.dbo.cAppeal co ON co.id=c.id
-- where co.id is null
GROUP BY rollup(YEAR(CASE WHEN c.date IS NULL THEN co.date ELSE c.date END)
);

USE ISOJ;
  ---  synchronize tables
 
    DECLARE @DATABASE NVARCHAR(255)   
    DECLARE @TABLE NVARCHAR(255)  
    DECLARE @cmd NVARCHAR(1000)  
 
 
 -- '[' + table_catalog + '].[' + table_schema + '].[' +  table_name + ']' as tableName 
 DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
   SELECT top 2000 TABLE_NAME  AS tableName 
	   FROM [ISOJ].INFORMATION_SCHEMA.TABLES 
	   WHERE table_type = 'BASE TABLE'
 
    OPEN DatabaseCursor  
 
    FETCH NEXT FROM DatabaseCursor INTO @TABLE  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
	FETCH NEXT FROM DatabaseCursor INTO @TABLE 
 
  --set @TABLE = 'cAppealSource'
 
       SET @cmd = '
INSERT INTO [isoj].[dbo].['+@TABLE+']
select top 1000000 *
from ISOJ2018.[dbo].['+@TABLE+'] c
where c.id in (
	select c.id
	from ISOJ2018.[dbo].['+@TABLE+'] c
	left join isoj.[dbo].['+@TABLE+'] co on co.id=c.id
	where co.id is null
)
order by c.DateModified asc;
	   '   
 
          BEGIN TRY   
 
             PRINT @TABLE -- uncomment if you want to see commands
             EXEC (@cmd) 
          END TRY
          BEGIN CATCH
             PRINT '---'
             PRINT @cmd
             PRINT ERROR_MESSAGE() 
             PRINT '---'
          END CATCH
 
 END  
    CLOSE DatabaseCursor   
    DEALLOCATE DatabaseCursor
 
GO

USE ISOJ;
 
 
 DECLARE @DATABASE NVARCHAR(255)   
    DECLARE @TABLE NVARCHAR(255)  
    DECLARE @cmd NVARCHAR(1000)  
 
    DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
    SELECT name FROM master.sys.databases   
    WHERE name IN ('ISOJ')  -- databases
    AND state = 0 -- database is online
    AND is_in_standby = 0 -- database is not read only for log shipping
    ORDER BY 1  
 
    OPEN DatabaseCursor  
 
    FETCH NEXT FROM DatabaseCursor INTO @DATABASE  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
 
       SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +  
       table_name + '']'' as tableName FROM [' + @DATABASE + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   
 
       -- create table cursor  
       EXEC (@cmd)  
       OPEN TableCursor   
 
       FETCH NEXT FROM TableCursor INTO @TABLE   
       WHILE @@FETCH_STATUS = 0   
       BEGIN
          BEGIN TRY   
             SET @cmd = 'ALTER INDEX ALL ON ' + @TABLE + ' REBUILD' 
             PRINT @cmd -- uncomment if you want to see commands
             EXEC (@cmd) 
          END TRY
          BEGIN CATCH
             PRINT '---'
             PRINT @cmd
             PRINT ERROR_MESSAGE() 
             PRINT '---'
          END CATCH
 
          FETCH NEXT FROM TableCursor INTO @TABLE   
       END   
 
       CLOSE TableCursor   
       DEALLOCATE TableCursor  
 
       FETCH NEXT FROM DatabaseCursor INTO @DATABASE  
    END  
    CLOSE DatabaseCursor   
    DEALLOCATE DatabaseCursor
GO