Backup

備份 SQL Server 2016 實例登錄

  • April 11, 2018

我需要備份 SQL Server 2016 的實例，我已經對所需數據庫進行了夜間備份，但是我需要備份實例的使用者和角色…..我該怎麼做？內置作業或自定義 T-sql 我可以安排執行嗎？

如果有人能指出我正確的方向，我對 SQL 不是很好

一般來說

我會考慮閱讀主數據庫包含的資訊以及為什麼需要備份系統數據庫（master、msdb、…）。

參考： 備份和還原系統數據庫 (SQL Server) (Microsoft Docs)

主數據庫

主數據庫包含每個 SQL Server 實例的以下資訊：

主數據庫記錄 SQL Server 系統的所有系統級資訊。這包括實例範圍的元數據，例如登錄帳戶、端點、連結伺服器和系統配置設置。在 SQL Server 中，系統對像不再儲存在主數據庫中；相反，它們儲存在資源數據庫中。此外，master 是記錄所有其他數據庫的存在和那些數據庫文件的位置並記錄 SQL Server 的初始化資訊的數據庫。因此，如果主數據庫不可用，SQL Server 將無法啟動。

（強調我的）

參考： master 數據庫(Microsoft Docs)

msdb 數據庫

msdb 數據庫包含 SQL Server 實例的以下資訊：

SQL Server 代理使用 msdb 數據庫來調度警報和作業，並由 SQL Server Management Studio、Service Broker 和數據庫郵件等其他功能使用。

例如，SQL Server在 msdb 的表中自動維護完整的線上備份和恢復歷史記錄。此資訊包括執行備份的一方的名稱、備份的時間以及儲存備份的設備或文件。SQL Server Management Studio 使用此資訊提出恢復數據庫和應用任何事務日誌備份的計劃。**所有數據庫的備份事件都會被記錄下來，即使它們是使用自定義應用程序或第三方工具創建的。**例如，如果您使用呼叫 SQL Server 管理對象 (SMO) 對象的 Microsoft Visual Basic 應用程序來執行備份操作，則該事件將記錄在 msdb 系統表、Microsoft Windows 應用程序日誌和 SQL Server 錯誤日誌中。為了幫助您保護儲存在 msdb 中的資訊，我們建議您考慮將 msdb 事務日誌放在容錯儲存上。

（強調我的）

參考： msdb 數據庫(Microsoft Docs)

結論

如果備份主數據庫，則實例的所有登錄資訊都是安全的。但是，如果您想單獨備份（*如：*用於將登錄名轉移到不同的實例，用於重置密碼）實例的 SQL 登錄名，則必須應用不同的解決方案。

個人 SQL Server 登錄備份解決方案

工具參考列表

增加獎金

最後一個參考是一篇有趣的文章，介紹如何手動導出 SQL Server 登錄名和散列密碼，以便手動將它們傳輸到新的 SQL Server 實例。本文涉及創建兩個儲存過程，然後執行一個來創建資訊。

以下是引用文章中創建這兩個過程的程式碼：

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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
 DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE 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
       -- obtain password and sid
           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

       -- obtain password policy state
       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
   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
GO

創建儲存過程後，執行 sp_help_rev_login 以檢索資訊。

EXEC sp_help_revlogin

sp_help_revlogin 儲存過程生成的輸出腳本是登錄腳本。此登錄腳本創建具有原始安全標識符 (SID) 和原始密碼的登錄。

參考： 如何在 SQL Server 實例之間傳輸登錄名和密碼（Microsoft 知識庫文章）

享受

引用自：https://dba.stackexchange.com/questions/203622

相關問答

Sql-Server

你能備份一個複制的數據庫嗎？

  • February 23, 2022
檢視問答
Sql-Server

達到最大錯誤計數

  • January 27, 2022
檢視問答
Sql-Server-2016

當我們將備份拆分為多個文件時，文件數是否有限制？

  • December 16, 2021
檢視問答
Sql-Server

大型數據庫分區和僅更新數據的備份（部分備份）

  • October 3, 2021
檢視問答
Sql-Server

如何檢查備份是否可靠？

  • April 22, 2021
檢視問答
Sql-Server-2016

簡單的備份計劃問題

  • March 21, 2021
檢視問答