SE の雑記

select
	@@servername AS server_name
	, DB_NAME(database_id) AS db_name
	, mirroring_state_desc
	, mirroring_role_desc
	, mirroring_safety_level_desc
	, mirroring_partner_name
	, mirroring_partner_instance
	, mirroring_witness_name
from
	sys.database_mirroring

select
	name
	, connection_auth_desc
	, encryption_algorithm_desc
from
	sys.database_mirroring_endpoints

BACKUP DATABASE [RDSTEST2] TO DISK = 'D:RDSDBDATABACKUPb5d954b4-aaa9-4537-996b-0b5f82635d4d.1402357569.database_backup' WITH FORMAT
DECLARE @after_backup_epoch BIGINT
SET @after_backup_epoch = CAST('1402357570' AS BIGINT)
BEGIN TRANSACTION
INSERT INTO [rdsadmin].[dbo].[createdb_activity]([family_guid], [database_name], [properly_created_time])
SELECT 'b5d954b4-aaa9-4537-996b-0b5f82635d4d', N'RDSTEST2', DATEADD(second, @after_backup_epoch, '1970-01-01 00:00:00') AS properly_created
INSERT INTO [rdsadmin].[dbo].[done_initial_db_backups]([family_guid])
VALUES ('b5d954b4-aaa9-4537-996b-0b5f82635d4d')
INSERT INTO rdsadmin.dbo.db_backups (family_guid, before_backup_epoch, after_backup_epoch, database_name, database_backup)
  SELECT 'b5d954b4-aaa9-4537-996b-0b5f82635d4d', '1402357569', '1402357570', N'RDSTEST2', bulkcolumn FROM OPENROWSET(BULK 'D:RDSDBDATABACKUPb5d954b4-aaa9-4537-996b-0b5f82635d4d.1402357569.database_backup', SINGLE_BLOB) AS new_backup;
COMMIT TRANSACTION
declare @p1 int
set @p1=104
exec sp_prepexec @p1 output,N'@mirroring_login_0 nvarchar(45),@mirroring_login_1 nvarchar(45),@mirroring_login_2 nvarchar(45)',N'SELECT
            CHECKSUM_AGG(
                CONVERT(INT,
                    HASHBYTES(''MD5'',
                        CONVERT(varchar(11),
                            BINARY_CHECKSUM(
                                p.name, p.sid, p.default_database_name, p.default_language_name, p.is_disabled,
                                p.is_policy_checked, p.is_expiration_checked, p.password_hash, perms.permission_name,
                                perms.state_desc, grantors.name
                            )
                        )
                    )
                )
            )
            FROM sys.sql_logins p
            LEFT OUTER JOIN sys.server_permissions perms ON p.principal_id = perms.grantee_principal_id
            LEFT OUTER JOIN sys.server_principals grantors ON perms.grantor_principal_id = grantors.principal_id
            WHERE p.type = ''S'' AND p.name NOT IN (''##MS_PolicyEventProcessingLogin##'', ''##MS_PolicyTsqlExecutionLogin##'',
            @mirroring_login_0, @mirroring_login_1, @mirroring_login_2)
            UNION ALL
            SELECT
            CHECKSUM_AGG(
                CONVERT(INT,
                    HASHBYTES(''MD5'',
                        CONVERT(varchar(11),
                            BINARY_CHECKSUM(
                                p.name, role_principal.name
                            )
                        )
                    )
                )
            )
            FROM sys.sql_logins p
            left outer join sys.server_role_members roles on p.principal_id = roles.member_principal_id
            left outer join sys.server_principals role_principal on roles.role_principal_id = role_principal.principal_id
            where p.type = ''S'' and role_principal.name is not null',@mirroring_login_0=N'AMAZONAGHLRIKB_login',@mirroring_login_1=N'AMAZONACHOVKR1_login',@mirroring_login_2=N'AMAZONA24V00NU_login'
select @p1
DECLARE @num_days_to_retain INT
DECLARE @earliest_retain_datetime DATETIME
DECLARE @earliest_retain_epoch BIGINT
SET @num_days_to_retain = CAST('36' AS INT)
IF @num_days_to_retain < 1 BEGIN
    RAISERROR('NumDaysToRetain is %d but must be at least 1', 16, 0, @num_days_to_retain) WITH LOG
END
SET @earliest_retain_datetime = DATEADD(day, 0 - @num_days_to_retain, SYSUTCDATETIME())
SET @earliest_retain_epoch = DATEDIFF(second, '1970-01-01 00:00:00', @earliest_retain_datetime)
DELETE FROM rdsadmin.dbo.log_backup_manifest
WHERE file_epoch < @earliest_retain_epoch
DELETE FROM rdsadmin.dbo.createdb_activity
WHERE properly_created_time < @earliest_retain_datetime
DELETE FROM rdsadmin.dbo.db_backups
WHERE family_guid != 'ccfa0f19-df9b-4f12-9ce2-8d6f3408add2'
  and after_backup_epoch < @earliest_retain_epoch
exec msdb..sp_delete_backuphistory @earliest_retain_datetime
DELETE FROM rdsadmin..db_mappings WHERE family_guid NOT IN
    (SELECT family_guid FROM sys.databases sysdb
    JOIN rdsadmin..db_mappings mappings
    ON sysdb.name = mappings.database_name)
AND created_time < GETDATE() - 15;
declare @request_action as varchar(50) = 'SET_WITNESS_SERVER';
IF @request_action = 'SET_PARTNER_SERVER'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER = 'TCP://rdswitness:1120';
        IF (select mirroring_state from sys.database_mirroring where database_id = (select database_id from sys.databases where name = 'RDSTEST2')) IS NOT NULL
        BEGIN
            ALTER DATABASE [RDSTEST2] SET PARTNER TIMEOUT 30;
        END
    END
ELSE IF @request_action = 'SET_PARTNER_FAILOVER'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER FAILOVER;
    END
ELSE IF @request_action = 'SET_PARTNER_FORCE_SERVICE_ALLOW_DATA_LOSS'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
    END
ELSE IF @request_action = 'SET_PARTNER_OFF'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER OFF;
    END
ELSE IF @request_action = 'SET_PARTNER_RESUME'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER RESUME;
    END
ELSE IF @request_action = 'SET_PARTNER_SAFETY_FULL'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER SAFETY FULL;
    END
ELSE IF @request_action = 'SET_PARTNER_SAFETY_OFF'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER SAFETY OFF;
    END
ELSE IF @request_action = 'SET_PARTNER_SUSPEND'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER SUSPEND;
    END
ELSE IF @request_action = 'SET_PARTNER_TIMEOUT'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET PARTNER TIMEOUT 30;
    END
ELSE IF @request_action = 'SET_WITNESS_SERVER'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET WITNESS = 'TCP://rdswitness:1120';
    END
ELSE IF @request_action = 'SET_WITNESS_OFF'
    BEGIN
        ALTER DATABASE [RDSTEST2] SET WITNESS OFF;
    END
ELSE
    BEGIN
        RAISERROR ('Error - unknown request action %s', 16, 1, @request_action);
    END

ALTER DATABASE RDSTEST2 SET PARTNER OFF
DROP DATABASE RDSTEST2