如何将 RESTORE FILELISTONLY/HEADERONLY/VERIFYONLY 提供的信息插入到临时表中 [英] How to insert into a temp table the info given by RESTORE FILELISTONLY / HEADERONLY / VERIFYONLY

问题描述

如何插入命令给出的结果集

仅恢复文件列表恢复正常仅恢复验证

进入自动生成的临时表?

我想使用类似于(因此表是自动创建的，所有列都与结果集的列匹配)类似的技术

SELECT * INTO #TempTable从(从磁盘恢复文件列表 = 'c:\Test\Test.bak')

但这行不通.如果我可以填充一个 TempTable，我就可以在下面的 SQL 语句中使用其中包含的信息(在我的例子中是一个恢复数据库语句，我需要使用包含在 RESTORE FILELISTONLY 给出的结果集中的一些字符串)

我使用的是 sql server 2008.

解决方案

就我个人而言，这是一种我会避免使用纯 TSQL 并使用外部脚本或程序的场景.根据您尝试执行的操作，您可能会发现使用 Powershell 或 .NET 中的 Smo 完全避免了对 TSQL 的需求.我这么说是因为使用备份似乎总是导致使用数据库外的文件，然后 TSQL 就太笨拙了.

说了这么多，如果您确定必须在 TSQL 中执行此操作，那么您可以执行以下操作:

insert into dbo.BackupFiles (LogicalName, PhysicalName, ...)exec('RESTORE FILELISTONLY FROM DISK = ''c:\Test\Test.bak''')

或者更好一点:

声明@Command nvarchar(4000)-- 当然，您可以通过其他方式构建命令字符串set @Command = N'RESTORE FILELISTONLY FROM DISK = ''c:\Test\Test.bak'''插入 dbo.BackupFiles (LogicalName, PhysicalName, ...)exec sp_executesql @Command

不过，您仍然必须先创建表，这没什么大不了的，如果您经常这样做，无论如何都是有意义的.联机丛书列出了结果集中每一列的数据类型，但至少对我而言 (SQL2008 SP1) 文档与实际结果集不匹配，因此您可能需要对其进行调整.

How to insert the resultset given by the commands

RESTORE FILELISTONLY
RESTORE  HEADERONLY
RESTORE VERIFYONLY

into an automatically generated temp table ?

I would like to use a technique similar to (so the table is auto created, with all the columns matching the resultset's columns)

SELECT * INTO #TempTable 
FROM (RESTORE FILELISTONLY FROM DISK = 'c:\Test\Test.bak')

But this doesn't work. If I could populate a TempTable I could then be able to use the information contained in it in a following SQL Statement (in my case a restore DB statement in which I need to use some strings contained in the resultset given by RESTORE FILELISTONLY)

I am using sql server 2008.

解决方案

Personally, this is one scenario where I would avoid pure TSQL and use an external script or program. Depending on what you're trying to do, you might find that using Smo from Powershell or .NET completely avoids the need for TSQL anyway. I say that because working with backups always seems to lead to working with files outside the database, and then TSQL is just too awkward.

Having said all that, if you're sure that you must do this in TSQL, then you can do something like this:

insert into dbo.BackupFiles (LogicalName, PhysicalName, ...)
exec('RESTORE FILELISTONLY FROM DISK = ''c:\Test\Test.bak''')

Or to be a bit nicer:

declare @Command nvarchar(4000)
-- you can build the command string some other way, of course
set @Command = N'RESTORE FILELISTONLY FROM DISK = ''c:\Test\Test.bak'''

insert into dbo.BackupFiles (LogicalName, PhysicalName, ...)
exec sp_executesql @Command

You would still have to create the table first, though, which is no big deal and makes sense anyway if you do this a lot. Books Online lists the data type for each column in the result set, but at least for me (SQL2008 SP1) the documentation does not match the actual result set so you might need to tweak it.

这篇关于如何将 RESTORE FILELISTONLY/HEADERONLY/VERIFYONLY 提供的信息插入到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！