db:mssql [Cygnus]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Mssql ======
===== MSSQL. Info =====

  * https://habr.com/ru/post/316064/ - информация по mssql 
  * mssql video traning http://www.sqlservervideos.com/video/sql2528-log-files/
  * https://sqlserver-kit.org/ru - каталог инструменты,
  * https://my.visualstudio.com/Downloads/Featured?mkt=ru-ru - скачивание дистрибутивов
  * https://docs.microsoft.com/ru-ru/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 - SSMS - studio
  * https://sqlserverbuilds.blogspot.com/ - site with version / update 
  * [[https://www.microsoft.com/en-us/download/details.aspx?id=44278|MSSQL 2008 SP4]]
  * mssql update pack https://support.microsoft.com/en-us/topic/kb968382-how-to-obtain-the-latest-service-pack-for-sql-server-2008-8b8113f9-dc3e-4f79-fecc-e1b8d96dd6fe
  * sp_command help https://irfanworld.wordpress.com/tag/sp_helpdb/

===== MSSQL. Docker =====
Docker mssql [[https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash|Mssql docker]] \\
<code bash> # пробросить mnt/volume для WSL и жестких дисков + нестандартный порт
sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=passw0rd_123" \
 -p 1434:1433 --name sql1 -h sql1 -v /mnt:/mnt/volume \
 -v /opt/docker/mssql/isoj:/var/opt/mssql/data
 -d mcr.microsoft.com/mssql/server:2019-latest
</code>

Пример работы с подключением из командной строки и выполнения восстановления \\
<code bash>
sudo docker exec -it sql3 "bash"
/opt/mssql-tools/bin/sqlcmd -S 172.31.28.155,1434 -U SA -P "passw0rd_123"

-- включить mssql agent
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Agent XPs', 1;  
GO  
RECONFIGURE  
GO  
</code>

===== MSSQL. SQL administration =====
++++ --- MSSQL. Sql to Get info data of base , space used, files|
<code SQL>
-- 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;
</code>
++++

++++ --- MSSQL. Backup Restore|
<code SQL>
-- # 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
</code>
++++


++++ --- MSSQL. turn on agent|
<code SQL>
-- Get data for mssql
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
</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 =====
<code SQL>
bcp [ISOJ].[dbo].[lNLogData] out i:\lNLogData.txt  -T -c -b 10000
</code>


===== MSSQL. Shrink Files, Reclaim Space =====

https://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log
<code SQL>
USE ISOJ
GO
 
DECLARE @FileName sysname = N'ISOJ';
DECLARE @TargetSize INT = (SELECT 1 + size*8./1024 FROM sys.database_files WHERE name = @FileName);
DECLARE @Factor FLOAT = .995;
DECLARE @Inc INT = 0
 
WHILE @Inc < 10
BEGIN
    SET @Inc += 1
    SET @TargetSize *= @Factor;
    DBCC SHRINKFILE(@FileName, @TargetSize);
    DECLARE @msg VARCHAR(200) = 'Shrink file completed. Target Size: '+ 
         cast(@TargetSize as varchar )+ ' MB. Timestamp: ';
    RAISERROR(@msg, 1, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:01';
END;


-- reclail logs
USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO
</code>


===== MSSQL rebuild all indexes in database =====
<code SQL >-- Short way 
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'</code>
++++ Rebuild indexes|
<code SQL>
--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
</code>
++++
===== MSSQL. trace =====
If trace skip rows , need to do trace on server side \\
https://www.sqlservercentral.com/blogs/setting-up-a-server-side-trace

===== MSSQL. syncronize tables =====

++++Check syncrhonize|
<code SQL>

-- 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)
);
</code>
++++

++++ Syncronize tables|
<code SQL>
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
</code>
++++
===== TSQL example =====
++++ TSQL example|
<code SQL>
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
</code>
++++