/*
--SQL 数据库导出迁移用户账户及权限
--EG. [sp_help_revlogin]
*/

--sp_hexadecimal
USE master;
GO
IF OBJECT_ID('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal;
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue VARBINARY(256),
@hexvalue VARCHAR(256) OUTPUT
AS
DECLARE @charvalue VARCHAR(256);
DECLARE @i INT;
DECLARE @length INT;
DECLARE @hexstring CHAR(16);
SELECT @charvalue = '0x';
SELECT @i = 1;
SELECT @length = DATALENGTH(@binvalue);
SELECT @hexstring = '0123456789ABCDEF';
WHILE (@i <= @length)
BEGIN
DECLARE @tempint INT;
DECLARE @firstint INT;
DECLARE @secondint INT;
SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue, @i, 1));
SELECT @firstint = FLOOR(@tempint / 16);
SELECT @secondint = @tempint - (@firstint * 16);
SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint + 1, 1) + SUBSTRING(@hexstring, @secondint + 1, 1);
SELECT @i = @i + 1;
END;
SELECT @hexvalue = @charvalue;
GO

----- sp_help_revlogin

IF OBJECT_ID('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin;
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL
AS
DECLARE @name sysname;
DECLARE @type VARCHAR(1);
DECLARE @hasaccess INT;
DECLARE @denylogin INT;
DECLARE @is_disabled INT;
DECLARE @PWD_varbinary VARBINARY(256);
DECLARE @PWD_string VARCHAR(514);
DECLARE @SID_varbinary VARBINARY(85);
DECLARE @SID_string VARCHAR(514);
DECLARE @tmpstr VARCHAR(1024);
DECLARE @is_policy_checked VARCHAR(3);
DECLARE @is_expiration_checked VARCHAR(3);
DECLARE @defaultdb sysname;
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid,
p.name,
p.type,
p.is_disabled,
p.default_database_name,
l.hasaccess,
l.denylogin
FROM sys.server_principals p
LEFT JOIN sys.syslogins l
ON (l.name = p.name)
WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name <> 'sa';
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid,
p.name,
p.type,
p.is_disabled,
p.default_database_name,
l.hasaccess,
l.denylogin
FROM sys.server_principals p
LEFT JOIN sys.syslogins l
ON (l.name = p.name)
WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name = @login_name;
OPEN login_curs;
FETCH NEXT FROM login_curs
INTO @SID_varbinary,
@name,
@type,
@is_disabled,
@defaultdb,
@hasaccess,
@denylogin;
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.';
CLOSE login_curs;
DEALLOCATE login_curs;
RETURN -1;
END;
SET @tmpstr = '/* sp_help_revlogin script ';
PRINT @tmpstr;
SET @tmpstr = '** Generated ' + CONVERT(VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */';
PRINT @tmpstr;
PRINT '';
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT '';
SET @tmpstr = '-- Login: ' + @name;
PRINT @tmpstr;
IF (@type IN ( 'G', 'U' ))
BEGIN -- NT authenticated account/group 
SET @tmpstr
= 'CREATE LOGIN ' + QUOTENAME(@name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']';
END;
ELSE
BEGIN -- SQL Server authentication
SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS VARBINARY(256));
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT;
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT;
SELECT @is_policy_checked = CASE is_policy_checked
WHEN 1 THEN
'ON'
WHEN 0 THEN
'OFF'
ELSE
NULL
END
FROM sys.sql_logins
WHERE name = @name;

SELECT @is_expiration_checked = CASE is_expiration_checked
WHEN 1 THEN
'ON'
WHEN 0 THEN
'OFF'
ELSE
NULL
END
FROM sys.sql_logins
WHERE name = @name;

SET @tmpstr
= 'CREATE LOGIN ' + QUOTENAME(@name) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = '+ @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']';

IF (@is_policy_checked IS NOT NULL)
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked;
END;
IF (@is_expiration_checked IS NOT NULL)
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked;
END;
END;
IF (@denylogin = 1)
BEGIN -- login is denied access 
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME(@name);
END;
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access 
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME(@name);
END;
IF (@is_disabled = 1)
BEGIN -- login is disabled 
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME(@name) + ' DISABLE';
END;

select @tmpstr=@tmpstr+ N';EXEC sp_addsrvrolemember N''' +sp.name+ ''' ,N''' + rsp.name+'''; '
FROM sys.server_principals sp 
LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id 
LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id 
where rsp.name is not NULL
AND sp.name=@name

PRINT @tmpstr;
END;
FETCH NEXT FROM login_curs
INTO @SID_varbinary,
@name,
@type,
@is_disabled,
@defaultdb,
@hasaccess,
@denylogin;
END;
CLOSE login_curs;
DEALLOCATE login_curs;
RETURN 0;

--生成创建登录账号脚本
exec master..sp_help_revlogin

USE master;
GO
DECLARE @FileName NVARCHAR(256);
SET @FileName = N'E:\backup\备份数据库名_FullBackup_20200707.bak';
BACKUP DATABASE [备份数据库名]
TO DISK = @FileName
WITH COMPRESSION,
INIT,
SKIP,
FORMAT;
GO

DECLARE @backupSetId AS INT;
SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'备份数据库名'
AND backup_set_id =
(
SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = N'备份数据库名'
);
IF @backupSetId IS NULL
BEGIN
RAISERROR(N'验证失败。找不到数据库“备份数据库名”的备份信息。', 16, 1);
END;
RESTORE VERIFYONLY
FROM DISK = N'E:\backup\备份数据库名_FullBackup_20200707.bak'
WITH FILE = @backupSetId,
NOUNLOAD,
NOREWIND;
GO

select * into table_name_bak  FROM  table_name  with (nolock);  --删除数据之前备份

DECLARE @coun INT;
DECLARE @i INT;
SET @i=0 ;
SELECT @coun=COUNT(*)/5000 FROM table_name with (nolock) ; --汇总需要删除的数据行数，从而得到需要循环次数
WHILE @i<@coun+1                        
begin
      DELETE TOP (5000) FROM  table_name ;  --每次删除5000行

  SET @i=@i+1 ;

  WAITFOR DELAY '00:00:01';  --每次循环延迟1S后继续循环

END

DECLARE @coun INT;
DECLARE @i INT;
SET @i=0 ;
SELECT @coun=COUNT(*)/5000 FROM aa_test a , ab_test b WHERE a.id=b.id ; --汇总需要删除的数据行数，从而得到需要循环次数 
WHILE @i<@coun+1                        
begin
      DELETE TOP (5000) a FROM aa_test a , ab_test b WHERE a.id=b.id  ;  --每次删除5000行 ，删除时必须是where 条件，不能使用 ON

  SET @i=@i+1 ;

  WAITFOR DELAY '00:00:01';  --每次循环延迟1S后继续循环

END