💶 🍐 👩🏾‍🔬 将所有MS SQL Server数据库传输到另一台计算机 🎾 🐀 🌩️

最近，需要将所有数据库（在一个SQL Server实例上> 50个数据库）从开发环境转移到另一个SQL Server实例，该实例位于另一台硬件上。我想最大程度地减少体力劳动，并尽快完成所有工作。

免责声明

针对一种特定情况编写脚本：这是一个开发环境，简单恢复模型中的所有数据库，数据文件和事务日志都在同一堆上。

下面写的所有内容仅适用于这种情况，但是您可以轻松地自己完成（根据您的条件）而无需付出太多努力。

这些脚本没有使用新的STRING_AGG和其他好东西，因此一切都应从SQL Server 2008（或2008 R2，从我不记得备份压缩出现在哪里）开始起作用。对于较旧的版本，您需要从备份命令中删除WITH COMPRESSION，但是复制文件可能不再存在时间差异。

这不是指令-“如何”进行此类转移。这演示了如何在动态SQL中使用元数据。

当然，最快的方法是简单地将磁盘架重新连接到新服务器，但这不是我们的选择。分离-复制-考虑了附加，但不合适，因为通道非常狭窄，并且以未压缩的形式传输数据库将花费相当长的时间。

结果，我们决定在新服务器上进行压缩的情况下进行备份，然后将其还原到该服务器上。新旧位置的铁都不错，备份也不错，时间增益也不错。

因此，“脚本生成器”被编写为：

DECLARE @unc_backup_path AS varchar(max) = '\\newServer\backup_share\'
	, @local_backup_path AS varchar(max) = 'E:\Backup\'
	, @new_data_path as varchar(max) = 'D:\SQLServer\data\';

SELECT name	
	, 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command
	, 'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' AS offline_command
	, 'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @local_backup_path + name + '.bak'' WITH ' 
		+ (
			SELECT 'MOVE ''' + mf.name + ''' TO ''' + 
				@new_data_path + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name))-1)) +
				''', '
			FROM sys.master_files mf
			WHERE mf.database_id = d.database_id
			FOR XML PATH('')
		) + 'REPLACE, RECOVERY, STATS = 5;' AS restore_command
FROM sys.databases d
WHERE database_id > 4 AND state_desc = N'ONLINE';

在输出中，我们获得了现成的命令，可用于将备份创建到正确的位置，然后离线传输数据库，从而使用户无法在旧服务器上使用它们，也无法使用脚本将脚本还原到新服务器上（将所有数据文件和事务日志自动传输到指定位置））

这样做的问题是，要么有人必须坐下来依次执行所有脚本（离线备份恢复），要么有人必须首先启动所有备份，然后断开所有数据库的连接，然后还原所有内容-动作少，但您需要坐下来和跟踪。

我想自动化所有这些操作。一方面，一切都很简单-已经有现成的命令，可以包装在光标中并执行。而且，原则上，我只是这样做了，在旧服务器上添加了新服务器作为链接服务器，然后启动了它。在本地服务器上，通过EXECUTE（@sql_text）执行命令；在链接服务器上，通过EXECUTE（@sql_text）AT [linkedServerName]执行命令。

因此，操作是按顺序执行的-本地备份，脱机转换本地数据库，还原到链接服务器。一切开始，欢呼，但是在我看来，如果备份和还原彼此独立地执行，则可以稍微加快该过程。
然后，将发明的游标分为两部分-在游标中的旧服务器上，每个数据库都备份并脱机转移，然后第二台服务器必须了解已经出现了新任务并执行数据库还原。为了实现此机制，我在链接服务器上的表中使用了一条记录，并使用了无限循环（我懒得提出停止条件），该循环看起来是否有新记录，并尝试还原某些记录（如果有）。

决断

在旧服务器上，将创建并填充一个全局临时表## CommandList，该表中将收集所有命令，并且可以在同一位置跟踪备份的状态。该表是全局表，因此您随时可以在另一个会话中看到那里正在发生的事情。

DECLARE @unc_backup_path AS varchar(max) = 'D:\SQLServer\backup\' --       
	, @local_backup_path AS varchar(max) = 'D:\SQLServer\backup\'	--        
	, @new_data_path as varchar(max) = 'D:\SQLServer\data\';		--      ,    

SET NOCOUNT ON;

IF OBJECT_ID ('tempdb..##CommandList', 'U') IS NULL
	CREATE TABLE ##CommandList (
		dbName sysname unique			-- 
		, backup_command varchar(max)	--   
		, offline_command varchar(max)	--        
		, restore_command varchar(max)	--       
		, processed bit				-- : NULL -  , 0 -  , 1 - 
		, start_dt datetime			--  
		, finish_dt datetime			--  
		, error_msg varchar(max)		--  ,  
	);

INSERT INTO ##CommandList (dbname, backup_command, offline_command, restore_command)
SELECT name	
	, 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @unc_backup_path + name + '.bak'' WITH INIT, COPY_ONLY, STATS = 5;' AS backup_command -- INIT -      
	, 'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;' AS offline_command
	, 'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @local_backup_path + name + '.bak'' WITH ' 
		+ (
			SELECT 'MOVE ''' + mf.name + ''' TO ''' + 
				@new_data_path + REVERSE(LEFT(REVERSE(mf.physical_name), CHARINDEX('\', REVERSE(mf.physical_name))-1)) +
				''', '	
			FROM sys.master_files mf
			WHERE mf.database_id = d.database_id
			FOR XML PATH('')
		) + 'REPLACE, RECOVERY, STATS = 5;' AS restore_command	
FROM sys.databases d
WHERE database_id > 4 
	AND state_desc = N'ONLINE'
	AND name NOT IN (SELECT dbname FROM ##CommandList)
	AND name <> 'Maintenance';	--  linked server -    ,   ,    "linked server"

让我们看看那里发生了什么（SELECT * FROM ## CommandList）：

很好，所有命令都收集在那里以备份/还原所有必需的数据库。

维护数据库是在新服务器上创建的，并且其中包含CommandList表，该表将包含有关还原数据库的信息：

USE [Maintenance]
GO

CREATE TABLE CommandList (
	dbName sysname unique				-- 
	, restore_command varchar(max)		--  
	, processed bit						-- 
	, creation_dt datetime DEFAULT GETDATE()	--  
	, start_dt datetime					--  
	, finish_dt datetime					--  
	, error_msg varchar(max)				-- ,  
);

在旧服务器上配置了链接服务器，以查看SQL Server的新实例。我在家里写的这篇文章中给出的脚本并没有打扰到新实例，我使用了一个实例并将其作为链接服务器连接到我自己。因此，这里我有相同的路径和本地的unc-path。

现在，您可以声明一个游标，用于在其中备份数据库，断开它们的连接并编写命令以还原到链接的服务器：

DECLARE @dbname AS sysname
	, @backup_cmd AS varchar(max)
	, @restore_cmd AS varchar(max)
	, @offline_cmd AS varchar(max);

DECLARE MoveDatabase CURSOR
FOR 
SELECT dbName, backup_command, offline_command, restore_command
FROM ##CommandList
WHERE processed IS NULL;

OPEN MoveDatabase;

FETCH NEXT FROM MoveDatabase INTO @dbname, @backup_cmd, @offline_cmd, @restore_cmd;

WHILE @@FETCH_STATUS = 0
	BEGIN
		--    ,  :
		--  
		--   -      
		--    ,      
		--     

		--    
		UPDATE ##CommandList
		SET start_dt = GETDATE()
		WHERE dbName = @dbname;

		BEGIN TRY
			
			RAISERROR ('  %s', 0, 1, @dbname) WITH NOWAIT; --   messages   
			
			--  
			EXEC (@backup_cmd);

			RAISERROR ('    %s', 0, 1, @dbname) WITH NOWAIT;

			--    -  linked server
			INSERT INTO [(LOCAL)].[Maintenance].[dbo].[CommandList] (dbName, restore_command)
			VALUES (@dbname, @restore_cmd);

			RAISERROR (' %s  OFFLINE', 0, 1, @dbname) WITH NOWAIT;

			--    
			EXEC (@offline_cmd);

			--  ,    
			UPDATE ##CommandList
			SET processed = 0
				, finish_dt = GETDATE()
			WHERE dbName = @dbname;

		END TRY
		BEGIN CATCH
			
			RAISERROR ('    %s.   error_msg  ##CommandList', 0, 1, @dbname) WITH NOWAIT;

			--  -   ,      
			UPDATE ##CommandList
			SET processed = 1
				, finish_dt = GETDATE()
				, error_msg = ERROR_MESSAGE();

		END CATCH

		FETCH NEXT FROM MoveDatabase INTO @dbname, @backup_cmd, @offline_cmd, @restore_cmd;
	END

CLOSE MoveDatabase;

DEALLOCATE MoveDatabase;

-- 
SELECT dbName
	, CASE processed WHEN 1 THEN '' WHEN 0 THEN '' ELSE ' ' END as Status 
	, start_dt
	, finish_dt
	, error_msg
FROM ##CommandList
ORDER BY start_dt;

DROP TABLE ##CommandList;

每个动作都被记录在SSMS的“消息”选项卡上-您可以在其中观察当前动作。如果原则上在RAISERROR中使用WITH LOG，则可以将其全部放在某个作业中，然后查看日志。

在运行时，您可以访问## CommandList并以表格形式查看发生了什么以及如何发生。

在新服务器上，并行地，一个无限循环在旋转：


SET NOCOUNT ON;

DECLARE @dbname AS sysname
	, @restore_cmd AS varchar(max);

WHILE 1 = 1	--   ,    
BEGIN
	SELECT TOP 1 @dbname = dbName, @restore_cmd = restore_command 
	FROM CommandList
	WHERE processed IS NULL; --    ,  

	IF @dbname IS NOT NULL 
	BEGIN
		--    
		UPDATE CommandList
		SET start_dt = GETDATE()
		WHERE dbName = @dbname;

		RAISERROR('  %s', 0, 1, @dbname) WITH NOWAIT;
		
		BEGIN TRY

			--  ,  -  ,  CATCH    
			EXEC (@restore_cmd);

			--   
			UPDATE CommandList
			SET processed = 0
				, finish_dt = GETDATE()
			WHERE dbName = @dbname;

			RAISERROR(' %s  ', 0, 1, @dbname) WITH NOWAIT;

		END TRY
		BEGIN CATCH

			RAISERROR('    %s', 0, 1, @dbname) WITH NOWAIT;

			UPDATE CommandList 
			SET processed = 1
				, finish_dt = GETDATE()
				, error_msg = ERROR_MESSAGE();

		END CATCH

	END
	ELSE	--   ,    
		BEGIN

			RAISERROR('waiting', 0, 1) WITH NOWAIT;

			WAITFOR DELAY '00:00:30';

		END
		
	SET @dbname = NULL;
	SET @restore_cmd = NULL;

END

他所做的一切-查看CommandList表（如果那里至少有一个原始记录）-将获取数据库名称和要还原的命令，并尝试使用EXEC（@sql_text）;执行该命令。如果没有条目，请等待30秒，然后重试。

游标和循环都只处理一次记录。没有解决？我们将错误消息写入表，并且不再在此处返回。

关于停止的条件-我实际上很懒。在键入时，我提出了至少三种解决方案-作为一种选择-添加“准备恢复\未准备恢复\完成”标志，在旧服务器上填写## CommandList并更新光标内部的标志时，立即填写数据库和命令列表。当没有“准备恢复的记录”时，我们会停止，因为我们立即知道全部工作量。

发现

但是没有结论。我认为对于有人来说如何使用元数据来形成和执行动态sql可能是有用/有趣的。帖子中给出的脚本实际上并不适合在产品上使用，但是，您可以为自己添加一些脚本并使用，例如，配置日志传送/数据库镜像/可用性组。

在进行备份时，运行SQL Server的帐户必须具有在其中写入的权限。

链接服务器的创建未在帖子中披露（在几分钟内直观地配置了GUI中的鼠标），并且未将登录名转移到新服务器。那些经历过用户迁移的人都知道，仅仅重新创建sql登录并没有多大帮助，因为他们拥有与数据库用户连接的sid。msdn上用于使用当前密码和正确的sid生成sql登录的脚本。

将所有MS SQL Server数据库传输到另一台计算机

免责声明

决断

发现

More articles: