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
 
-- 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

--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