Проверить SPN на узле:

setspn -L DOMAIN\<service account OR user>

Делегировать право на запись SPN своего объекта в AD:

dsacls "CN=<service account or user>,OU=Users,DC=domain,DC=com" /G "DOMAIN\<service account or user>:WS;servicePrincipalName"
#или если WP (Write Property)
dsacls "CN=<service account or user>,OU=Users,DC=domain,DC=com" /G "DOMAIN\<service account or user>:WP;servicePrincipalName"

Зарегистрировать SPN на узле в ручную:

setspn -S MSSQLSvc/NODE02.domain.com DOMAIN\<service account OR user>
setspn -S MSSQLSvc/NODE02.domain.com:1433 DOMAIN\<service account OR user>

CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

SELECT e.name AS endpoint_name,
       grantee.name AS grantee,
       pe.permission_name,
       pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals grantee ON grantee.principal_id = pe.grantee_principal_id
JOIN sys.endpoints e ON pe.class = 105 AND pe.major_id = e.endpoint_id
WHERE e.type = 4          -- DATABASE_MIRRORING
  AND pe.permission_name = 'CONNECT';

SELECT 
    ar.replica_server_name, 
    adc.database_name, 
    ag.name AS ag_name, 
    dhdrs.synchronization_state_desc, 
    dhdrs.is_commit_participant, 
    dhdrs.last_sent_lsn, 
    dhdrs.last_sent_time, 
    dhdrs.last_received_lsn, 
    dhdrs.last_hardened_lsn, 
    dhdrs.last_redone_time
FROM sys.dm_hadr_database_replica_states AS dhdrs
INNER JOIN sys.availability_databases_cluster AS adc 
    ON dhdrs.group_id = adc.group_id AND 
    dhdrs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = dhdrs.group_id
INNER JOIN sys.availability_replicas AS ar 
    ON dhdrs.group_id = ar.group_id AND 
    dhdrs.replica_id = ar.replica_id
WHERE database_name='DATABASE'

https://learn.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-database-replica-states-azure-sql-database?view=azuresqldb-current

Get-ClusterAccess

Добавить ноду в AG (на Primary):

ALTER AVAILABILITY GROUP [AG01] ADD REPLICA ON
N'NODE02' WITH (
    ENDPOINT_URL = N'TCP://NODE02.domain.com:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
);

Удалить ноду из AG:

ALTER AVAILABILITY GROUP [AG01] REMOVE REPLICA ON N'NODE02';

Присоединить ноду (на самой ноде) и разрешить создавать базы:

ALTER AVAILABILITY GROUP [AG01] JOIN;
ALTER AVAILABILITY GROUP [AG01] GRANT CREATE ANY DATABASE;

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql

-- Run on every node --
USE master;
GO
 
-- Enable clr
EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;
 
-- Add VsOnline.SQLCLR in trusted_assembly --
 
DECLARE @clrName nvarchar(4000) = 'VsOnline.SQLCLR'
DECLARE @asmBin varbinary(MAX) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300AF83515C0000000000000000E00022200B013000001C00000006000000000000C23A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000703A00004F00000000400000A803000000000000000000000000000000000000006000000C000000383900001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C81A000000200000001C000000020000000000000000000000000000200000602E72737263000000A80300000040000000040000001E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002200000000000000000000000000004000004200000000000000000000000000000000A43A00000000000048000000020005004C240000EC140000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300200360000000100001103281100000A256F1200000A0A026F1300000A0B027E1400000A281500000A2D0F0F00042D03062B0107281600000A2A7E1400000A2A0000133003002A0000000100001103281100000A256F1200000A0A0F0006281700000A6F1300000A0B0F00042D03062B0107281700000A2A56020F01281800000A0F02281900000A28010000062A56020F01281800000A0F02281900000A28020000062A1E02281A00000A2A367E0200000402036F1B00000A2A367E0200000402036F1C00000A2A367E0100000402036F1D00000A2A367E0100000402036F1E00000A2A5673120000068001000004731100000680020000042A220203281F00000A2A26020304282000000A2A001330020024000000020000110203282100000A0A066F2200000A2D02142A066F2300000A046F2400000A6F2500000A2A220F01282600000A2A3202282700000A282800000A2AC202732900000A2520000100006F2A00000A2520000100006F2B00000A251B6F2C00000A25176F2D00000A282E00000A2A001B3004004000000003000011041E5B732F00000A0A160B2B1603075A0C0608283000000A161A6F3100000A0717580B07041E5B1A5B32E2066F3200000A0DDE0A062C06066F3300000ADC092A01100000020009002B34000A000000003A02281A00000A02037D3400000A2A001B3005008B00000004000011042D0B7201000070733500000A7A027B3400000A027B3400000A6F3600000A283700000A0203027B3400000A6F3800000A6F3900000A6F3A00000A0A733B00000A0B070617733C00000A0C08733D00000A0D09046F3E00000ADE0A092C06096F3300000ADC076F3200000A283F00000A1304DE14082C06086F3300000ADC072C06076F3300000ADC11042A000128000002005200095B000A0000000002004B002974000A00000000020042003C7E000A000000001B3006008300000005000011042D0B7213000070733500000A7A04284000000A027B3400000A027B3400000A6F3600000A283700000A0203027B3400000A6F3800000A6F3900000A6F4100000A0A734200000A0B070616733C00000A0C08734300000A0D096F4400000A1304DE1E092C06096F3300000ADC082C06086F3300000ADC072C06076F3300000ADC11042A0001280000020058000A62000A00000000020051001B6C000A00000000020048002E76000A00000000DA042D0B7213000070733500000A7A052D0B7237000070733500000A7A046F4500000A2D02162A020304284600000A051A284700000A2A001B3005003900000006000011021E5B732F00000A0A284800000A72590000706F4900000A0B060716021E5B6F3100000A066F3200000A0CDE0A062C06066F3300000ADC082A0000000110000002000900242D000A0000000042534A4201000100000000000C00000076342E302E33303331390000000005006C000000B4070000237E000020080000FC07000023537472696E6773000000001C1000009C00000023555300B8100000100000002347554944000000C81000002404000023426C6F620000000000000002000001571F02080904000000FA013300160000010000003300000009000000040000001C000000320000000100000049000000010000001B00000006000000040000000100000003000000020000000000720401000000000006009C024E0606001D034E0606008A034E0606002902180613006E06000006005102E80406000003E8040600CC02E80406007103E80406003D03E80406005603E80406006802E80406003D022F0606001B022F060600AF02E80406008302160406002707A9040600DF01A9040600DF04A9040A00EB02A5050A0050047D060A00D4047D060A00DF00A5050600D000A9040E006104B5060600930440000600B004B9070600C304B90706008604B90706008005400006005A05400006003905A9040E008C07B5060E004E05B5060E00FA04B5060E00FE01B50606002B06B9070600C000B90706008001B90706009D01B90706009805A9040600990440000600A801A90406000A05A90406008C01B90706008D05400006007207A90406006705400006005304A90406002005A90406000D048007000000003700000000000100010081011000A2064A0045000100010001001000E6064A0045000100030001001000D4064A0045000100060001001000F8064A00450003000C0001001000CD05E20706000300100001001000E305E2070A0003001200A10000001900E207000003001400810010000100E2074500030017003100E3058B013100CD058F0104007205CD005180400793015020000000009600C001960101009420000000009600750096010400CA20000000009600C0019F010700E02000000000960075009F010A00F620000000008618C00501000D00FE200000000096002401AA010D000C210000000096004C01AA010F001A210000000096003701B101110028210000000096001101B1011300F620000000008618C005010015003621000000009118C605B70115004C210000000096005A046A00150055210000000096006A01700017006021000000009600460570001A00F620000000008618C00501001D00902100000000C4005A00BB011D009921000000008618C00501001F00A621000000008618C00501001F00D82100000000C4005A00C3011F00000000000000C6052E0763002100000000000000C605360763002300000000000000C605B207CA0125003422000000008418C0059B002800442200000000E6012E0763002900042300000000E601360763002B00BC2300000000E601B207CA012D00F423000000009100AB07B8003000000000000000C4055A00E100310000000100B401000002008F0000000300E80100000100DA01000002008F0000000300E80100000100B401000002008F0000000300E80100000100DA01000002008F0000000300E801000001009E00000002006101000001009E0000000200FF00000001009E0000000200FF00000001009E00000002006101000001007A07000002003105000001007A07000002003105000003006607000001007A07000002003105000003005405000001009E0000000200D703000001009E0000000200D703000001009E00000002006101000001009E0000000200FF00000001009E0000000200FF0000000300EE00000001007905000001009E00000002006101000001009E0000000200FF00000001009E0000000200FF0000000300EE0000000100F903000001009E0000000200D70309000E000900C00501001100C00505001900C00501002100C0050A003100C00510003900C00510004100C00510004900C00510005100C00510005900C00510006100C00510006900C00515007100C00510007900C00510008100C0051000A100C00501000101A5003500010147073C000101590741009100B20348009100D6074C009100BC00540091001E075400A900A8035B00B100A8035F008900C00501000C002E0763000C003607630014003607630014002E07630009015F046A0009016F017000090161047C00110112075F00C900070783001901A00489002101A8035B00C1009207900029010E0295000C00C0059B003101C0050100D900ED030500D900C9030500D9000104A100D9007701A8001400C0059B00D100C005050049019206B80051011502BE00D1009E07900059010602010024007205CD006101C0051000D900E103DD002400AB07B800D900BB03DD0024005A00E100D9000806E900D100C0050100E900C005F200F100C005FE00710115021000790141040501790130041701D900F805E900D100C0051D01F900C005FE008101D5005B0089016704DD0024003607630089019B06230199012E003501990192063B010E0010004A0120000B00D2012E000B00D2012E001300D7012E001B00E0012E002300FF012E002B0008022E0033001D022E003B001D022E0043001D022E004B0008022E00530023022E005B001D022E0063001D022E006B003B022E00730065022E007B00720240000B00D20143000B00D20160008300D20180008300D201C0008300C002E0008300C00200018300C00220018300C002800183007103A00183007103C001830071032E007700AF00D1000B012C011A0021002700C6000480000001000000000000000000000000004A00000004000000000000000000000041016C000000000004000000000000000000000041016000000000000400000000000000000000004101A90400000000000002001000A607000000001200A60700000050617373776F726450726F746563746F72426173656031004950617373776F726450726F746563746F726031006765745F55544638003C4D6F64756C653E0053797374656D2E494F0056734F6E6C696E652E53514C434C520047657449560053797374656D2E44617461006D73636F726C696200436F6E766572744C6F63616C4461746554696D65546F5574630074696D655A6F6E65496E666F4964007573657249640046696E6453797374656D54696D655A6F6E6542794964004164640052696A6E6461656C4D616E6167656400477569640052656164546F456E6400446174614163636573734B696E640070726F766964656450617373776F72640070726F74656374656450617373776F7264004F6C6450726F7465637450617373776F7264004E657750726F7465637450617373776F7264004F6C64556E70726F7465637450617373776F7264004E6577556E70726F7465637450617373776F72640070617373776F72640052656745785265706C616365007365745F4D6F64650050616464696E674D6F64650043727970746F53747265616D4D6F6465004369706865724D6F64650049446973706F7361626C65007574634461746554696D6500436F6E76657274557463546F4C6F63616C4461746554696D65006C6F63616C4461746554696D65007573654461796C69676874536176696E6754696D65004361707475726500446973706F73650043726561746500577269746500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500457874656E73696F6E41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565004D696E56616C7565006765745F426C6F636B53697A65007365745F426C6F636B53697A6500626C6F636B53697A65006765745F4B657953697A65007365745F4B657953697A65006B657953697A65007365745F50616464696E6700456E636F64696E670053797374656D2E52756E74696D652E56657273696F6E696E670046726F6D426173653634537472696E6700546F426173653634537472696E670053716C537472696E6700526567457849734D61746368006765745F4C656E6774680056734F6E6C696E652E53514C434C522E646C6C0043727970746F53747265616D004D656D6F727953747265616D006765745F4974656D0053797374656D0053796D6D6574726963416C676F726974686D004943727970746F5472616E73666F726D0053716C426F6F6C65616E0054696D655370616E0053797374656D2E5265666C656374696F6E0047726F7570436F6C6C656374696F6E00417267756D656E744E756C6C457863657074696F6E00537472696E67436F6D70617269736F6E007061747465726E0054696D655A6F6E65496E666F00526567457847657447726F75700067726F75700053747265616D526561646572005465787452656164657200436970686572006369706865720053747265616D577269746572005465787457726974657200426974436F6E766572746572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F72004775696450617373776F726450726F746563746F7200496E7450617373776F726450726F746563746F7200437265617465446563727970746F7200437265617465456E63727970746F720053797374656D2E446961676E6F7374696373004165730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300476574427974657300457175616C73004461746554696D65457874656E73696F6E730053797374656D2E546578742E526567756C617245787072657373696F6E730050617373776F726446756E6374696F6E73004461746554696D6546756E6374696F6E7300526567657846756E6374696F6E73006765745F47726F757073006765745F53756363657373005375627472616374004F626A6563740050726F7465637400556E70726F7465637400536563726574006765745F426173655574634F6666736574004765745574634F6666736574007265706C6163656D656E7400436F6E7665727400696E7075740053797374656D2E5465787400526567657800546F42797465417272617900546F417272617900544B6579004765744B6579005665726966790053797374656D2E53656375726974792E43727970746F677261706879006F705F457175616C6974790056734F6E6C696E652E53514C434C522E536563757269747900000011700061007300730077006F00720064000023700072006F00740065006300740065006400500061007300730077006F00720064000021700072006F0076006900640065006400500061007300730077006F00720064000041380033003900430030004400390035003900440034004600340037003900430038003100450045003000420034003900350043004300330042003900360039000000BF15293F4D049E41B18E74541BBC05C100032000010420010108052001011115042001010E04200101020615122401116105151224010806151220011300060702114D114D0600011280810E042000114D062001114D11490306114907000202114911490620011149114D0320000E032000020620020E13000E050002020E0E0600030E0E0E0E040701126506000212650E0E05200012808D0620011280890E0420001D0505000012809505200101126D0620010111809D062001011180A1080704126908081D050500011D0508072003011D050808061512240113000306126D0B070512711269127512790E032000080720021D0513000808200212711D051D050B2003011280A912711180B5062001011280A90500010E1D050B0705127112691275127D0E0500011D050E052001011D05080003020E0E1180C908070312691D051D050500001280CD0520011D050E08B77A5C561934E08940380033003900430030004400390035003900440034004600340037003900430038003100450045003000420034003900350043004300330042003900360039000306121C0306121802060E080003114911490E020A000311491149115511590600020E11610E0500020E080E030000010720021D051161080620021D0508080720030213000E0E04010000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001401000F56734F6E6C696E652E53514C434C52000005010000000017010012436F7079726967687420C2A920203230313800002901002433383634663238332D643462632D346466352D393538302D66626231333465306666653700000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E362E310100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E362E3180AF0100030054020F497344657465726D696E697374696301540209497350726563697365005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000080AF0100030054020F497344657465726D696E697374696301540209497350726563697365015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737300000000000000000000AF83515C00000000020000001C01000054390000541B0000525344536415DFC3CCEEAA4B94D748057140D42301000000443A5C4465765C56534F6E6C696E65576562536974654769745C56734F6E6C696E655C56734F6E6C696E652E53514C434C525C6F626A5C52656C656173655C56734F6E6C696E652E53514C434C522E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000983A00000000000000000000B23A0000002000000000000000000000000000000000000000000000A43A0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C03000000000000000000004C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC020000010053007400720069006E006700460069006C00650049006E0066006F0000008802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D0065000000000000000000480010000100460069006C0065004400650073006300720069007000740069006F006E0000000000560073004F006E006C0069006E0065002E00530051004C0043004C0052000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000048001400010049006E007400650072006E0061006C004E0061006D0065000000560073004F006E006C0069006E0065002E00530051004C0043004C0052002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000560073004F006E006C0069006E0065002E00530051004C0043004C0052002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D00650000000000560073004F006E006C0069006E0065002E00530051004C0043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000C43A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
DECLARE @hash varbinary(64);
 
SELECT @hash = HASHBYTES('SHA2_512', @asmBin);
 
-- ADD (uncomment):
--/*
EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                 @description = @clrName;
--*/
 
-- DROP (uncomment):
/*
EXEC sys.sp_drop_trusted_assembly @hash = @hash;
*/
-- DROP by description
/*
DECLARE @hash varbinary(64);
SELECT @hash = (SELECT hash FROM sys.trusted_assemblies WHERE description='VsOnline.SQLCLR')
EXEC sys.sp_drop_trusted_assembly @hash = @hash
*/

USE DB;
SELECT name, sid FROM sys.sysusers WHERE name = 'login'
GO
USE master;
SELECT name, sid FROM sys.sql_logins WHERE name = 'login'
GO

SELECT 'create login [' + sp.name + '] ' + CASE
        WHEN sp.type IN (
                'U'
                ,'G'
                )
            THEN 'from windows '
        ELSE ''
        END + 'with ' + CASE
        WHEN sp.type = 'S'
            THEN 'password = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(sl.sid) + ', check_expiration = ' + CASE
                    WHEN sl.is_expiration_checked > 0
                        THEN 'ON, '
                    ELSE 'OFF, '
                    END + 'check_policy = ' + CASE
                    WHEN sl.is_policy_checked > 0
                        THEN 'ON, '
                    ELSE 'OFF, '
                    END + CASE
                    WHEN sl.credential_id > 0
                        THEN 'credential = ' + c.name + ', '
                    ELSE ''
                    END
        ELSE ''
        END + 'default_database = ' + sp.default_database_name + CASE
        WHEN len(sp.default_language_name) > 0
            THEN ', default_language = ' + sp.default_language_name
        ELSE ''
        END
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
LEFT JOIN sys.credentials c ON sl.credential_id = c.credential_id
WHERE sp.type IN (
        'S'
        ,'U'
        ,'G'
        )
    AND sp.name <> 'sa'
    AND sp.name NOT LIKE 'NT Authority%'
    AND sp.name NOT LIKE 'NT Service%'

USE [master]
GO
SELECT db.name AS [DATABASE],sp.name [Owner] FROM sys.databases db LEFT JOIN sys.server_principals sp ON db.owner_sid=sp.sid
WHERE database_id>5

SELECT 'USE [' + db.name+']; EXEC sp_changedbowner [sa];' FROM 
sys.databases db LEFT JOIN sys.server_principals sp
ON db.owner_sid=sp.sid
WHERE sp.name <>'sa'

ALTER LOGIN userlogin WITH
     PASSWORD = 'newPassword'
     OLD_PASSWORD = 'oldPassword';
GO

Показать список ролей сервера (список и предопределенных, и определяемых пользователем ролей сервера):

SELECT * FROM sys.server_principals WHERE TYPE = 'R';

https://learn.microsoft.com/ru-ru/sql/relational-databases/system-stored-procedures/sp-helpsrvrole-transact-sql

Показать пользователей являющихся членами роли уровня сервера:

SELECT
		sql_logins.principal_id			AS MemberPrincipalID
	,	sql_logins.name					AS MemberPrincipalName
	,	roles.principal_id				AS RolePrincipalID
	,	roles.name						AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins 
    ON server_role_members.member_principal_id = sql_logins.principal_id

https://learn.microsoft.com/ru-ru/sql/relational-databases/system-catalog-views/sys-server-role-members-transact-sql

USE DBforEXECUTE
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'USERforEXECUTE'

https://stackoverflow.com/questions/2298247/built-in-database-role-in-sql-server-2005-to-permit-execution-of-stored-procedur

SELECT  pri.name AS username
  ,pri.type_desc AS [USER TYPE]
  ,permit.permission_name AS [Permission]
  ,permit.state_desc AS [Permission State]
  ,permit.class_desc Class
  ,object_name(permit.major_id) AS [Object Name]
FROM sys.database_principals pri
LEFT JOIN
  sys.database_permissions permit
ON permit.grantee_principal_id = pri.principal_id

SELECT 
    pe.class_desc,
    pe.permission_name,
    pe.state_desc,
    grantee.name  AS grantee,
    grantor.name  AS grantor,
    ep.name       AS endpoint_name
FROM sys.server_permissions pe
JOIN sys.server_principals grantee ON pe.grantee_principal_id = grantee.principal_id
JOIN sys.server_principals grantor ON pe.grantor_principal_id = grantor.principal_id
LEFT JOIN sys.endpoints ep ON pe.class = 105 AND pe.major_id = ep.endpoint_id
WHERE pe.grantor_principal_id = SUSER_ID('DOMAIN\user');

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'superStrogPasswrodMK';
GO
CREATE CERTIFICATE TDE_Cert_26
WITH 
SUBJECT='Database_Encryption',
EXPIRY_DATE = '20260201';
GO

BACKUP CERTIFICATE TDE_Cert_26 TO FILE ='C:\TDE_Cert_26.crt'  
 WITH PRIVATE KEY   
 (   
  FILE ='E:\TDE_Cert_26.pvk',
  ENCRYPTION BY PASSWORD = 'superStrogPasswrodPVK'
 )
GO

Для Azure SQL в бинарный ASN формат:

SELECT CERTENCODED(CERT_ID('TDE_Cert_26'));
SELECT CERTPRIVATEKEY(CERT_ID('TDE_Cert_26'), 'superStrogPasswrodPVK'); 

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'superStrogPasswrodMK';
GO
CREATE CERTIFICATE TDE_Cert_26   
    FROM FILE = 'E:\TDE_Cert_26.crt'   
    WITH PRIVATE KEY (FILE = 'C:\TDE_Cert_26.pvk',   
    DECRYPTION BY PASSWORD = 'superStrogPasswrodPVK');
GO

Для Azure SQL из бинарного ASN формата:

CREATE CERTIFICATE TDE_Cert_26
    FROM BINARY = 0x3...D
    WITH PRIVATE KEY (
       BINARY = 0x1...3 
       , DECRYPTION BY PASSWORD = 'superStrogPasswrodPVK'
	);

USE dbname;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE dbname
SET ENCRYPTION ON;
GO

DECLARE @db sysname = 'mydb';
IF EXISTS (
    SELECT *
    FROM sys.dm_database_encryption_keys
    WHERE database_id = db_id(@db)
       AND encryption_state = 3 /* encrypted */
)
BEGIN
   print concat(quotename(@db), ' is encrypted.')
END
ELSE
BEGIN
   print concat(quotename(@db), ' is not encrypted.')
END

SELECT DB_NAME(database_id) AS name, * FROM sys.dm_database_encryption_keys
ORDER BY  name

encryption_state:
0 - Database Encryption Key (DEK) не создан.
1 - Database Encryption Key (DEK) создан, но база данных не зашифрована.
2 - Выполняется первоначальное шифрование.
3 - База данных зашифрована.
4 - Идет смена ключа.
5 - Идет расшифровка.

ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION OFF;  
GO  
USE AdventureWorks2012;  
GO  
DROP DATABASE ENCRYPTION KEY;  
GO  

USE master;
GO
SELECT
     *,
     CERTENCODED(C.certificate_id)
FROM sys.certificates C;

USE [master]
GO
SELECT
DB_NAME(db.database_id) DbName, db.encryption_state
, encryptor_type, cer.name, cer.expiry_date, cer.subject
FROM sys.dm_database_encryption_keys db
JOIN sys.certificates cer 
ON db.encryptor_thumbprint = cer.thumbprint
GO

BACKUP DATABASE [TDE_Test] 
TO  DISK = N'E:\Backup\TDE_Test.bak' WITH NOFORMAT, NOINIT,  
NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP LOG [TDE_Test] TO  DISK = N'E:\Backup\Backup\TDE_Test.trn' WITH NOFORMAT, NOINIT,  
NAME = N'TDE_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
ALTER AVAILABILITY GROUP "AG01" ADD DATABASE [TDE_Test]

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'E:\Backup\TDE_Test.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
RESTORE LOG [TDE_Test] FROM  DISK = N'E:\Backup\TDE_Test.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO
ALTER DATABASE [TDE_Test] SET HADR AVAILABILITY GROUP = "AG01";

docker run -p 1433:1433 \
 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<supper-password>' -e MSSQL_MEMORY_LIMIT_MB="2048" -e 'MSSQL_PID=Express' \
 -e MSSQL_COLLATION=Cyrillic_General_CI_AS -e TZ=Asia/Yekaterinburg \
 -v /data/docker/mssql/mssql:/var/opt/mssql/ \
 -v /data/docker/mssql/backups:/var/opt/mssql/backups \
 --restart on-failure:3 \
 -h mssql.domain.com -d --name mssql mcr.microsoft.com/mssql/server:2019-latest

docker-compose.yaml:

version: '3.4'
volumes:
  mssql:
    driver_opts:
      type: none
      device: ${VOLPATH-/data/docker/mssql}/mssql
      o: bind
  backups:
    driver_opts:
      type: none
      device: ${VOLPATH-/data/docker/mssql}/backups
      o: bind
 
services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    hostname: mssql.domain.com
    environment:
    - ACCEPT_EULA=Y
    - SA_PASSWORD=<super-password>
#    - MSSQL_PID=Express
    - MSSQL_MEMORY_LIMIT_MB=10240
#    - MSSQL_COLLATION=Cyrillic_General_CI_AS
#    - TZ=Asia/Yekaterinburg
    volumes:
    - mssql:/var/opt/mssql:rw
    - backups:/var/opt/mssql/backups:rw
    ports:
      - 1433:1433
    restart: on-failure:3
    deploy:
      replicas: 1
#      resources:
#        limits:
#          memory: 2g
      restart_policy:
        condition: any
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$${SA_PASSWORD}" -Q "SELECT 1" -b > /dev/null
      interval: 10s
      timeout: 3s
      retries: 5
      start_period: 60s

Запуск:

• docker-compose:

  docker-compose up -d

• swarm:

  docker stack deploy --compose-file docker-compose.yaml mssql

Запустить контейнеры с параметрами:

    hostname: node[1-2]
    environment:
    - ACCEPT_EULA=Y
    - SA_PASSWORD=<SA-Pa$$w0rd>
    - MSSQL_PID=Developer
    - MSSQL_ENABLE_HADR=1
    ports:
      - 1433:1433
      - 5022:5022

На первой ноде создать пользователя и сертификат к нему, сделать резервную копию сертификата и ключа:

CREATE LOGIN dbm_login WITH PASSWORD = 'super-Pa$$w0rd';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'super-Pa$$w0rd';
GO
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm',
  EXPIRY_DATE = '20320201';
GO
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/var/opt/mssql/backups/dbm_certificate.cer'
WITH PRIVATE KEY (
  FILE = '/var/opt/mssql/backups/dbm_certificate.pvk',
  ENCRYPTION BY PASSWORD = 'super-Pa$$w0rd'
);

На второй ноде создать пользователя и импортировать сертификат созданный на первой ноде:

CREATE LOGIN dbm_login WITH PASSWORD = 'super-Pa$$w0rd';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'super-Pa$$w0rd';
GO
CREATE CERTIFICATE dbm_certificate   
  AUTHORIZATION dbm_user
  FROM FILE = '/var/opt/mssql/backups/dbm_certificate.cer'
  WITH PRIVATE KEY (
  FILE = '/var/opt/mssql/backups/dbm_certificate.pvk',
  DECRYPTION BY PASSWORD = 'super-Pa$$w0rd'
)

На двух нодах создать endpoint:

CREATE ENDPOINT [Hadr_endpoint]
  AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)
  FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE dbm_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login]

Создать группу доступности на первой ноде:

CREATE AVAILABILITY GROUP [ag]
  WITH ( CLUSTER_TYPE = NONE, 
    AUTOMATED_BACKUP_PREFERENCE = NONE )
  FOR REPLICA ON
  N'node1'
  WITH (
    ENDPOINT_URL = N'tcp://node1:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
  ),
  N'node2'
  WITH (
    ENDPOINT_URL = N'tcp://node2:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
  )

Добавить второй кластер в группу доступности:

ALTER AVAILABILITY GROUP [ag] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [ag] GRANT CREATE ANY DATABASE

На первой ноде добавить базу данных в группу доступности:

ALTER AVAILABILITY GROUP [ag] ADD DATABASE [DatabaseName]

https://dbtut.com/index.php/2020/06/09/sql-server-2019-alwayson-availability-group-on-docker-containers/
https://github.com/enriquecatala/sqlserver-docker-alwayson

FROM ubuntu:18.04 as build
 
ADD so/nodirect_open.c /
RUN apt update && apt install -y gcc && \
    gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl
 
FROM mcr.microsoft.com/mssql/server:2019-CU9-ubuntu-18.04
USER root
COPY --from=build /nodirect_open.so /so/nodirect_open.so
RUN echo "/so/nodirect_open.so" >> /etc/ld.so.preload
ENV LD_PRELOAD=/so/nodirect_open.so

SELECT
    DB_NAME(dbid) AS DBName,
    COUNT(dbid) AS Connections,
    loginame AS LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY dbid, loginame
ORDER BY Connections DESC;

SELECT spid, STATUS, loginame, hostname, db_name(dbid) AS 'Database', cmd FROM sys.sysprocesses WHERE spid > 50

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Показать возможность обновления текущей базы данных:

IF (DATABASEPROPERTYEX(DB_NAME(), 'Updateability') = 'READ_WRITE')
  BEGIN
    PRINT 'PRIMARY'
  END
ELSE
  BEGIN
    PRINT 'SECONDARY'
  END

или:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Показать текущее имя сервера:

SELECT @@SERVERNAME

#!/bin/bash
 
DATE=$(date +%F)
 
DB="dbname"
DBUSER="sa"
DBPASS="password"
REMOTEHOST="docker4"
PATHB="/data/1c"
 
FILENAME=$DB\_$DATE
if ! [ -d $PATHB ]; then mkdir -p $PATHB; fi
 
#Backup sql
/opt/mssql-tools/bin/sqlcmd -S c1 -U $DBUSER -P $DBPASS -Q "BACKUP DATABASE $DB to DISK='/var/opt/mssql/backups/$FILENAME.bak'"
 
#Move & compress backup
CONTAINER=$(ssh $REMOTEHOST "docker ps -q -f name=mssql")
ssh $REMOTEHOST "docker exec $CONTAINER cat /var/opt/mssql/backups/$FILENAME.bak" | gzip > $PATHB/$FILENAME.bak.tgz
ssh $REMOTEHOST "docker exec $CONTAINER rm /var/opt/mssql/backups/$FILENAME.bak"
 
#Clean backup
while [ `ls $PATHB/$DB\_* | wc -l` -gt 7 ] ; do
    rm `ls $PATHB/$DB\_* | head -1`
done
 
 
#Restore
#zcat $PATHB/$FILENAME.bak.tgz | ssh $REMOTEHOST docker exec -i $CONTAINER dd status=none of=/var/opt/mssql/backups/$FILENAME.bak
#/opt/mssql-tools/bin/sqlcmd -S c1 -U $DBUSER -P $DBPASS -Q "RESTORE DATABASE $DB to DISK='/var/opt/mssql/backups/$FILENAME.bak'"
#ssh $REMOTEHOST "docker exec $CONTAINER rm /var/opt/mssql/backups/$FILENAME.bak"

@echo off
setlocal EnableDelayedExpansion
set LOG=D:\service\backup_sql.log
echo %TIME% Start... > %LOG%
 
:: Сколько копий храним
set count=7
:: Куда бэкапим
set dst=D:\backup
 
:: Устанавливаем текущую дату
for /f "tokens=1" %%n in ('date /t') do set thedate=%%n
set dd=%thedate:~0,2%
set mm=%thedate:~3,2%
set yyyy=%thedate:~6,4%
set TODAY=%yyyy%%mm%%dd%
 
call :BACKUP
call :COMPRESS
call :CLEARARHIVE
goto END
 
:END
echo %TIME% :END >> %LOG%
goto :eof
 
:BACKUP
echo %TIME% :BACKUP >> %LOG%
sqlcmd -U user -P pass -i D:\service\backup.sql >> %LOG%
goto :eof
 
:COMPRESS
echo %TIME% :COMPRESS >> %LOG%
"C:\Program Files\7-Zip\7z.exe" a %dst%\%TODAY%_nvp_book.7z %dst%\%TODAY%_nvp_book.bak
del /Q %dst%\%TODAY%_nvp_book.bak
goto :eof
 
:CLEARARHIVE
echo %TIME% :CLEARARHIVE >> %LOG%
for /F "delims=" %%i in ('dir %dst%\*.7z /B /O:-D') do (
set /A count=!count! - 1
if !count! LSS 0 del /F /Q "%dst%\%%i" >> %LOG% 2>&1
)
:eof

DECLARE @pathName NVARCHAR(512) 
SET @pathName = 'D:\Backup\' + Convert(varchar(8), GETDATE(), 112) + '_dbname.bak' 
BACKUP DATABASE [dbname] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N'dbname', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'Z:\diff\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('DB1','DB2','DB3')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @fileName = @path + @name + '_' + @fileDate + '.bak'
   BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION, STATS = 10, DIFFERENTIAL
 
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT
    r.[command]
    ,db_name = db_name(r.database_id)
    ,r.[start_time]
    ,eta_completion_time = DATEADD(ms, r.[estimated_completion_time], GETDATE())
    ,r.[percent_complete]
    ,r.[estimated_completion_time] / 60000. AS [estimated_completion_time_min]
    ,r.session_id
    ,s.original_login_name
    ,t.text
FROM master.sys.dm_exec_requests r
JOIN master.sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN master.sys.dm_exec_connections h ON r.session_id = h.session_id
CROSS APPLY master.sys.dm_exec_sql_text(h.most_recent_sql_handle) t
WHERE r.[command] = 'BACKUP DATABASE'
    OR r.[command] = 'RESTORE DATABASE'
    OR r.[command] = 'RESTORE HEADERONLY'
    OR r.[command] = 'BACKUP LOG'
    OR r.[command] = 'RESTORE LOG'
    OR r.[command] LIKE '%DBCC%'

USE master
GO 
SELECT 
    req.session_id, 
    database_name = db_name(req.database_id),
    req.status,
    req.blocking_session_id, 
    req.command,
    [sql_text] = SUBSTRING(txt.TEXT, (req.statement_start_offset / 2) + 1, (
                (
                    CASE req.statement_end_offset
                        WHEN - 1 THEN Datalength(txt.TEXT)
                        ELSE req.statement_end_offset
                    END - req.statement_start_offset
                    ) / 2
                ) + 1),
    req.percent_complete,
    req.start_time,
    cpu_time_sec = req.cpu_time / 1000,
    granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),
    req.reads,
    req.logical_reads,
    req.writes,
    eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),
    elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),
    remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),
    eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),
    wait_type,
    wait_time_sec = wait_time/1000, 
    wait_resource
FROM sys.dm_exec_requests AS req WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt 
WHERE req.session_id>50
    AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

Проверка целостности файла:

RESTORE VERIFYONLY FROM DISK = 'path_to_file.bak';

Просмотр содержимого бэкапа:

RESTORE FILELISTONLY FROM DISK = 'path_to_file.bak';

-- Backup Full
DECLARE @name NVARCHAR(256) -- database name
DECLARE @namelog NVARCHAR(256) -- log trnsaction name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @sqluse NVARCHAR(256) 
 
-- specify database backup directory
SET @path = 'Z:\full\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('db1','db2','db3','db4','db5')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @fileName = @path + @name + '_' + @fileDate + '.bak'
   BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

-- Backup Diff
DECLARE @name NVARCHAR(256) -- database name
DECLARE @namelog NVARCHAR(256) -- log trnsaction name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @sqluse NVARCHAR(256) 
 
-- specify database backup directory
SET @path = 'Z:\diff\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('db1','db2','db3','db4','db5')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
  IF NOT EXISTS (SELECT db_name(r.database_id)
    FROM master.sys.dm_exec_requests r
    WHERE (r.[command] = 'BACKUP DATABASE'
      OR r.[command] = 'BACKUP LOG'
      OR r.[command] LIKE '%DBCC%')
      AND db_name(r.database_id) LIKE @name)
  BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.bak'
    BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION, DIFFERENTIAL
    FETCH NEXT FROM db_cursor INTO @name
  END
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

-- Backup TransactionLog
DECLARE @name NVARCHAR(256) -- database name
DECLARE @namelog NVARCHAR(256) -- log trnsaction name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @sqluse NVARCHAR(256) 
 
-- specify database backup directory
SET @path = 'Z:\tl\'
-- specify filename format
SELECT  @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('db1','db2','db3','db4','db5')
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
  IF NOT EXISTS (SELECT db_name(r.database_id)
    FROM master.sys.dm_exec_requests r
    WHERE (r.[command] = 'BACKUP DATABASE'
      OR r.[command] = 'BACKUP LOG'
      OR r.[command] LIKE '%DBCC%')
      AND db_name(r.database_id) LIKE @name)
  BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.trn'
    BACKUP LOG @name TO DISK = @fileName WITH COMPRESSION
    SET @namelog = @name + '_log'
    SET @sqluse = 'USE ' + @name + '; DBCC SHRINKFILE (' + @namelog + ' , 1);'
    EXEC sp_sqlexec @sqluse
    FETCH NEXT FROM db_cursor INTO @name
  END
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT
t.Name AS TableName,
s.Name AS SchemaName,
p.Rows AS RowCounts,
SUM(a.total_pages) * 8 / 1048576.0 AS TotalSpaceGB,
SUM(a.used_pages) * 8 / 1048576 AS UsedSpaceGB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.Name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name;

SELECT dbschemas.[name] AS 'Schema',
  dbtables.[name] AS 'Table',
  dbindexes.[name] AS 'Index',
  indexstats.avg_fragmentation_in_percent,
  indexstats.avg_page_space_used_in_percent,
  indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') 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
ORDER BY dbtables.[name]

или

SELECT s.name schema, t.name TABLE, i.name INDEX, d.avg_fragmentation_in_percent fragmentation
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) d
INNER JOIN sys.tables  t ON d.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON d.object_id = i.object_id AND d.index_id = i.index_id
WHERE  d.index_id > 0 AND d.page_count > 8
ORDER BY fragmentation DESC

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)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent,
'ALTER INDEX ' + QUOTENAME(ind.name)  + ' ON ' +QUOTENAME(object_name(ind.object_id)) + 
CASE    WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD ' 
        WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE'
        ELSE NULL END AS [SQLQuery]  -- if <5 not required, so no query needed
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id 
    AND ind.index_id = indexstats.index_id 
WHERE 
--indexstats.avg_fragmentation_in_percent , e.g. >10, you can specify any number in percent 
ind.Name IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

EXEC sp_detach_db 'TestDB', 'true';

CREATE DATABASE [TestDB] ON 
  (FILENAME = N'D:\Data\TestDB.mdf'),
  (FILENAME = N'D:\Data\TestDB.ldf')
FOR ATTACH
--или
EXEC sp_attach_db @dbname = 'TestDB',
  @filename1 = N'D:\TestDB.mdf', 
  @filename2 = N'D:\TestDB_log.ldf'

если присоединилась в режиме READ_ONLY:

ALTER DATABASE [TestDB] SET READ_WRITE

--Get value advanced options
EXEC sp_configure 'show advanced options';
--To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO
--Get value xp_cmdshell
EXEC sp_configure 'xp_cmdshell';
--To allow xp_cmdshell
EXEC sp_configure 'xp_cmdshell','1'
GO
RECONFIGURE
GO
-- Mount
EXEC xp_cmdshell 'net use Z: "\\fs.domain.com\backup" /Y /persistent:yes'
--Revert
 
EXEC sp_configure 'xp_cmdshell','0'
GO
EXEC sp_configure 'xp_cmdshell'
EXEC sp_configure 'show advanced options', '0'
GO
EXEC sp_configure 'show advanced options'

DBCC SHRINKFILE (LogicalFileName, emptyfile)
GO
ALTER DATABASE vsonline REMOVE FILE LogicalFileName

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = temp2, FILENAME = 'T:\Data\tempdb_mssql_2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp3, FILENAME = 'T:\Data\tempdb_mssql_3.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp4, FILENAME = 'T:\Data\tempdb_mssql_4.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp5, FILENAME = 'T:\Data\tempdb_mssql_5.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp6, FILENAME = 'T:\Data\tempdb_mssql_6.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp7, FILENAME = 'T:\Data\tempdb_mssql_7.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp8, FILENAME = 'T:\Data\tempdb_mssql_8.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'T:\Data\templog.ldf');
GO

USE msdb
GO
DECLARE @TheResults VARCHAR(MAX),
 @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
 BEGIN
 --CREATE Profile [' + p.name + ']
 EXECUTE msdb.dbo.sysmail_add_profile_sp
 @profile_name = ''' + p.name + ''',
 @description = ''' + ISNULL(p.description,'') + ''';
 END --IF EXISTS profile
 '
 +
 '
 IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
 BEGIN
 --CREATE Account [' + a.name + ']
 EXECUTE msdb.dbo.sysmail_add_account_sp
 @account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',
 @email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',
 @display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',
 @replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',
 @description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',
 @mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',
 @mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',
 @port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
 @username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',
 @password = ''NotTheRealPassword'',
 @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
 @enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
 END --IF EXISTS account
 '
 + '
IF NOT EXISTS(SELECT *
 FROM msdb.dbo.sysmail_profileaccount pa
 INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
 INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
 WHERE p.name = ''' + p.name + '''
 AND a.name = ''' + a.name + ''')
 BEGIN
 -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
 EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = ''' + p.name + ''',
 @account_name = ''' + a.name + ''',
 @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
 END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
 FROM msdb.dbo.sysmail_profileaccount pa
 INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
 INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
 WHERE p.name = ''' + p.name + '''
 AND a.name = ''' + a.name + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
 END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
 END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
 BEGIN
 EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
 END
*/
 '
FROM sysmail_profile p
INNER JOIN sysmail_profileaccount pa ON p.profile_id = pa.profile_id
INNER JOIN sysmail_account a ON pa.account_id = a.account_id
LEFT OUTER JOIN sysmail_server s ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id
 ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1 UNION ALL
 SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
 E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
 E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
 E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
 --E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
 Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
 ItemSplit(
 ItemOrder,
 Item
 ) AS (
 SELECT N,
 SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
 FROM Tally
 WHERE N < DATALENGTH(@vbCrLf + @TheResults)
 --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
 AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
 )
 SELECT
 ROW_NUMBER() OVER (ORDER BY ItemOrder) AS ItemID,
 Item
 FROM ItemSplit 

• Версия целевого SQL Server должна быть такой же или новее
• Восстановится: история бэкапов, конфигурация Database Mail, пакеты SSIS в msdb, настройки log shipping, политики, планы обслуживания
• Агент должен быть остановлен, БД - не в использовании
• Логина-владельца должен быть на новом сервере

EXEC master.dbo.xp_servicecontrol N'STOP', N'SQLServerAgent';
GO
RESTORE DATABASE msdb FROM DISK = 'D:\msdb.bak' WITH REPLACE, RECOVERY;
GO
EXEC master.dbo.xp_servicecontrol N'START', N'SQLServerAgent';

Запрос для обновления:

SELECT * 
FROM [msdb].[dbo].[sysoperators]
 
SELECT
 'USE msdb;
 EXECUTE msdb.dbo.sp_update_operator
 @name = N'''+NAME+''',
 @email_address = N'''+email_address+''';'
FROM msdb.dbo.sysoperators

Запрос для создания:

SELECT * 
FROM [msdb].[dbo].[sysoperators]
 
SELECT
 'USE msdb;
 EXECUTE msdb.dbo.sp_add_operator
 @name = N'''+NAME+''',
 @email_address = N'''+email_address+''';'
FROM msdb.dbo.sysoperators

USE msdb;
CREATE USER username FOR LOGIN username;
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = 'username';
GO

EXECUTE AS USER  = 'app_vsomigration';
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'profile@domain.com',
  @recipients = 'toemail@domain.com',
  @body = 'BODY',
  @subject = 'TEST'
REVERT;

SELECT
    DB_NAME(mf.database_id) AS [DatabaseName],
    mf.name AS [LogicalName],
    mf.type_desc AS [FileType],
    mf.physical_name AS [FilePath],
    CONVERT(DECIMAL(18,2), mf.size / 128.0) AS [CurrentSizeMB],
    CASE 
        WHEN mf.is_percent_growth = 1 THEN 
            CAST(mf.growth AS VARCHAR(20)) + ' %'
        ELSE 
            RTRIM(
                CASE 
                    WHEN mf.growth * 8.0 / 1024 = FLOOR(mf.growth * 8.0 / 1024)
                        THEN CAST(CAST(mf.growth * 8.0 / 1024 AS INT) AS VARCHAR(20))
                    ELSE 
                        CAST(ROUND(mf.growth * 8.0 / 1024, 2) AS VARCHAR(20))
                END
            ) + ' MB'
    END AS [AutoGrowth],
    CASE 
        WHEN mf.max_size = -1 THEN 'Unlimited'
        ELSE 
            RTRIM(
                CASE 
                    WHEN mf.max_size * 8.0 / 1024 = FLOOR(mf.max_size * 8.0 / 1024)
                        THEN CAST(CAST(mf.max_size * 8.0 / 1024 AS INT) AS VARCHAR(20))
                    ELSE 
                        CAST(ROUND(mf.max_size * 8.0 / 1024, 2) AS VARCHAR(20))
                END
            ) + ' MB'
    END AS [MaxSize]
FROM sys.master_files AS mf
ORDER BY DB_NAME(mf.database_id), mf.type_desc;

DECLARE @DataGrowthMB INT = 1024;
DECLARE @LogGrowthMB  INT = 512;
 
DECLARE @name NVARCHAR(256)
DECLARE @SQL NVARCHAR(MAX) = N'';
 
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
  AND state = 0 -- database is online
  AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY name DESC
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
  WITH Files AS (
      SELECT
          d.name AS db_name,
          mf.name AS file_name,
          mf.type_desc
      FROM sys.databases d
      JOIN sys.master_files mf ON d.database_id = mf.database_id
      WHERE d.name = @name
  )
  SELECT 
      @SQL = STRING_AGG(cmd, CHAR(10) + CHAR(10))
  FROM (
      SELECT 
          N'USE [' + db_name + N'];' + CHAR(10) +
          N'ALTER DATABASE [' + db_name + N'] MODIFY FILE (NAME = N''' + file_name + N''', FILEGROWTH = ' +
          CASE WHEN type_desc = 'LOG' THEN CAST(@LogGrowthMB AS NVARCHAR(MAX))
               ELSE CAST(@DataGrowthMB AS NVARCHAR(MAX))
          END + N'MB);'
          AS cmd
      FROM Files
  ) AS t;
 
  PRINT @SQL;
  EXEC sp_executesql @SQL;
 
  FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

DECLARE @name sysname;
DECLARE @SQL nvarchar(MAX);
DECLARE db_cursor CURSOR READ_ONLY FOR
 
SELECT name
FROM master.sys.databases
WHERE database_id > 4 -- database is not system
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY name DESC
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'ALTER DATABASE [' + @name + N'] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);';
   PRINT @SQL;
   EXEC(@SQL);
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

DECLARE @name sysname;
DECLARE @SQL nvarchar(MAX);
DECLARE db_cursor CURSOR READ_ONLY FOR
 
SELECT name
FROM master.sys.databases
WHERE database_id > 4 -- database is not system
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY name DESC
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'ALTER DATABASE [' + @name + N'] FOR SECONDARY SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);';
   PRINT @SQL;
   EXEC(@SQL);
   FETCH NEXT FROM db_cursor INTO @name
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

Накладные расходы - данные QS с реплики перегоняются на primary. Обратить внимание на трафик/латентность между репликами и рост объёма QS (MAX_STORAGE_SIZE_MB теперь расходуется быстрее, статистика cо всех источников).

CREATE TABLE #QueryStoreOptions (
    database_name NVARCHAR(128),
    desired_state_desc NVARCHAR(60),
    actual_state_desc NVARCHAR(60),
    readonly_reason INT,
    current_storage_size_mb INT,
    max_storage_size_mb INT,
    max_plans_per_query INT
);
 
DECLARE @SQL NVARCHAR(MAX) = N'';
 
SELECT @SQL = @SQL + N'
USE [' + name + N'];
 
INSERT INTO #QueryStoreOptions
SELECT
  ''' + name + ''' AS database_name,
  desired_state_desc,
  actual_state_desc,
  readonly_reason,
  current_storage_size_mb,
  max_storage_size_mb,
  max_plans_per_query
FROM sys.database_query_store_options;
'
FROM sys.databases
WHERE state_desc = 'ONLINE';
 
EXEC sp_executesql @SQL;
 
SELECT * FROM #QueryStoreOptions;
 
DROP TABLE #QueryStoreOptions;

DECLARE @SQL NVARCHAR(MAX) = N'';
 
SELECT @SQL = @SQL +
    N'SELECT
        ''' + name + ''' AS database_name,
        desired_state,
        actual_state,
        readonly_reason,
        current_storage_size_mb,
        max_storage_size_mb,
        max_plans_per_query
     FROM [' + name + '].sys.database_query_store_options
     UNION ALL '
FROM sys.databases
WHERE state_desc = 'ONLINE';
 
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
 
EXEC sp_executesql @SQL;

Собрать статистику за указанный промежуток времени (24 часа) со всех бд где включён query store:

DECLARE @SQL nvarchar(MAX) = 
'
insert into DBA.dbo.QueryStoryStats
SELECT TOP 50
    ''{dbName}'' databaseName,
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),''-'') object_name,
    qt.query_sql_text query_sql_text,
    SUM(rs.count_executions) count_executions,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions)),2) as total_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions)) / SUM(rs.count_executions),2) as avg_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions)),2) as total_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions)) / SUM(rs.count_executions),2) as avg_logical_io_writes,
    COUNT(distinct p.plan_id) num_plans
--into DBA.dbo.QueryStoryStats
FROM [{dbName}].sys.query_store_runtime_stats rs
    JOIN [{dbName}].sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN [{dbName}].sys.query_store_query q ON q.query_id = p.query_id
    JOIN [{dbName}].sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE
    NOT (rs.first_execution_time > @end OR rs.last_execution_time < @start)
GROUP BY 
    p.query_id, qt.query_sql_text, q.object_id
HAVING 
    COUNT(distinct p.plan_id) >= 1
ORDER BY 
    total_physical_io_reads DESC
'
 
DECLARE @END datetime = getutcdate()
DECLARE @START datetime = DATEADD(HOUR, -24, @END)
 
TRUNCATE TABLE DBA.dbo.QueryStoryStats
 
DECLARE @databaseName nvarchar(50)
 
DECLARE dbCursor cursor FOR
SELECT name FROM sys.databases WHERE database_id > 4;
 
OPEN dbCursor
fetch NEXT FROM dbCursor INTO @databaseName
 
while @@FETCH_STATUS = 0
BEGIN
    print @databaseName
 
    DECLARE @sql2 nvarchar(MAX) = REPLACE(@SQL, '{dbName}', @databaseName)
    EXEC sp_executesql @sql2,N'@end datetime,@start datetime',@END=@END,@START=@START
 
    fetch NEXT FROM dbCursor INTO @databaseName
END
close dbCursor;
deallocate dbCursor;

Показать статистику топ 25 баз на чтение:

;WITH q AS (
SELECT top 25
    databaseName, 
    SUM(count_executions) cnt,
    SUM(total_physical_io_reads) total
FROM 
    DBA.dbo.QueryStoryStats 
GROUP BY
    databaseName)
SELECT
    *,
    format(cnt, 'n0') cntStr,
    format(total, 'n0') totalStr,
    format(100. * total / SUM(total) OVER (partition BY 1), 'n2') rateStr
FROM q 
ORDER BY 
    total DESC

Показать статистику топ 25 запросов на чтение:

SELECT top 25
    databaseName, 
    object_name, 
    query_sql_text, 
    format(count_executions, 'n0') cnt,
    format(total_physical_io_reads, 'n0') total,
    format(100. * total_physical_io_reads / (SUM(total_physical_io_reads) OVER (partition BY 1)), 'n2') rate,
    format(avg_physical_io_reads, 'n2') [avg]
FROM 
    DBA.dbo.QueryStoryStats 
ORDER BY 
    total_physical_io_reads DESC

Показать статистику топ 25 баз на запись:

;WITH q AS (
SELECT top 25
    databaseName, 
    SUM(count_executions) cnt,
    SUM(total_logical_io_writes) total
FROM 
    DBA.dbo.QueryStoryStats 
GROUP BY
    databaseName)
SELECT
    *,
    format(cnt, 'n0') cntStr,
    format(total, 'n0') totalStr,
    format(100. * total / SUM(total) OVER (partition BY 1), 'n2') rateStr
FROM q 
ORDER BY 
    total DESC

Показать статистику топ 25 запросов на запись:

SELECT top 25
    databaseName, 
    object_name, 
    query_sql_text, 
    format(count_executions, 'n0') cnt,
    format(total_logical_io_writes, 'n0') total,
    format(100. * total_logical_io_writes / (SUM(total_logical_io_writes) OVER (partition BY 1)), 'n2') rate,
    format(avg_logical_io_writes, 'n2') [avg]
FROM DBA.dbo.QueryStoryStats 
ORDER BY 
    total_logical_io_writes DESC

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [Text] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog
 
SELECT TOP 5
	[LogDate]
	,SUBSTRING([Text], CHARINDEX(') is ', [Text]) + 4,CHARINDEX(' complete (', [Text]) - CHARINDEX(') is ', [Text]) - 4) AS PercentComplete
	,CAST(SUBSTRING([Text], CHARINDEX('approximately', [Text]) + 13,CHARINDEX(' seconds remain', [Text]) - CHARINDEX('approximately', [Text]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([Text], CHARINDEX('approximately', [Text]) + 13,CHARINDEX(' seconds remain', [Text]) - CHARINDEX('approximately', [Text]) - 13) AS FLOAT)/3600.0 AS HoursRemaining
	,[Text]
 
FROM @ErrorLog 
WHERE CHARINDEX(@DBName, [Text]) > 0 AND CHARINDEX('approximately', [Text]) > 0
ORDER BY [LogDate] DESC 

USE master;
GO
ALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE DB1 SET ONLINE WITH ROLLBACK IMMEDIATE;

Ошибка: Drop failed for LogFile $NAME. One or more files listed in the statement could not be found or could not be initialized. (Microsoft SQL Server, Error: 5009)

Решение: Создать и удалить ещё один файл

USE DBName;
GO
ALTER DATABASE [DBName] ADD FILE ( NAME = N'DBName_log2', FILENAME = N'DISK:\Data\Test_log2.ldf', SIZE = 1MB, MAXSIZE = 1MB );
GO
BACKUP LOG Test TO DISK = N'DISK:\Backup\DBName.tl' WITH COMPRESSION;
GO
DBCC SHRINKFILE (N'DBName_log2' , EMPTYFILE);
GO
ALTER DATABASE [DBName]  REMOVE FILE [DBName_log2];