如何将SQL Server的tempdb数据库数据复制迁移到永久存储数据库

问题根因说明 #

• tempdb无备份/恢复选项：tempdb属于SQL Server内置系统临时数据库，设计特性为每次实例重启时自动基于model库重建，官方原生不支持备份、恢复、附加这类持久化操作，因此右键菜单不会显示对应功能入口。
• 迁移时出现带随机字符的异常表名：若你在tempdb中创建的是名称以#/##开头的临时表，SQL Server为了区分不同会话的同名临时表，会自动给这类表的物理存储名称追加随机后缀，会话内访问时可使用自定义短名，但直接导出元数据时会读取到带后缀的完整物理名，因此出现异常命名。

注意事项 #

• 临时表（#/##开头）的生命周期与创建它的会话绑定，迁移操作必须在创建临时表的会话未关闭时执行，会话断开后临时表会被tempdb自动清理，无法再读取。
• tempdb不存储持久化数据，确认迁移完成前不要重启SQL Server实例，否则tempdb所有数据会被清空。

正确迁移操作步骤 #

提前创建好目标永久数据库，例如命名为PermDb，确认当前操作账号对tempdb有读权限、对目标库有建表、写入权限。

方案1：逐表迁移（适合表数量较少的场景） #

通过SELECT INTO语句直接复制表结构和数据到永久库，不会出现随机后缀问题：

-- 迁移tempdb普通用户表语法
SELECT * INTO PermDb.dbo.自定义目标表名 FROM tempdb.dbo.tempdb中的原表名;

-- 迁移当前会话本地临时表语法
SELECT * INTO PermDb.dbo.自定义目标表名 FROM #你的临时表名;

方案2：批量迁移所有用户自建表（适合表数量多的场景） #

执行以下动态SQL，自动遍历tempdb中所有用户创建的非系统表，批量生成迁移语句：

USE tempdb;
GO
DECLARE @TargetDbName SYSNAME = 'PermDb'; -- 替换为你的永久库名称
DECLARE @Sql NVARCHAR(MAX) = N'';

SELECT @Sql += N'
SELECT * INTO ' + QUOTENAME(@TargetDbName) + N'.dbo.' + QUOTENAME(name) + N'
FROM tempdb.dbo.' + QUOTENAME(name) + N';
'
FROM sys.tables
WHERE is_ms_shipped = 0 -- 过滤系统内置表
-- 若需要迁移临时表可注释掉下面这行
AND name NOT LIKE '#%';

PRINT @Sql; -- 打印生成的迁移语句，核对无误后再执行
EXEC sp_executesql @Sql;

迁移后校验 #

迁移完成后可对比源表和目标表的行数，确认数据完整性：

-- 查询tempdb源表行数
SELECT COUNT(1) FROM tempdb.dbo.原表名;
-- 查询永久库目标表行数
SELECT COUNT(1) FROM PermDb.dbo.目标表名;

内容的提问来源于stack exchange，提问作者user113156