使用 powershell 执行 .sql 文件并将输出存储在文本文件中 [英] execute .sql file using powershell and store the output in a text file

问题描述

我正在尝试从 powershell 运行 sql 脚本 .sql 文件并将结果保存到 .sql 文件中.概述:SQL 数据库还原需要预还原用户和权限备份，还原完成后，我们需要在数据库上执行输出(我们已预还原的用户权限备份).

I’m trying to run the sql script .sql file from powershell and save the result into .sql file. Overview : SQL database restore requires a user and permission backup pre-restore and once the restore is complete we need to execute the output( users permissions backup which we did pre-restore ) on the database.

这是我的脚本，当我执行时，我看不到任何输出

here’s my script and when i execute i dont see any output

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
$MyQuery = get-content "C:\Users\Security.sql";
$SqlConnection.ConnectionString = "Server = DBATest; Database = Testdb; Integrated   Security = True"
$SqlCmd.CommandText = $MyQuery;
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$DataSet.Tables[0] | out-file C:\users\outputuser.sql

有人能指出我正确的方向吗?提前致谢

Could someone point me in right direction ? thanks in advance

推荐答案

如果您安装了 SQL Server 管理工具(2008 或更高版本)，这将变得更加容易.

If you have the SQL Server management tools (2008 or newer) installed, this becomes much easier.

add-pssnapin SqlServerCmdletSnapin100;
$myData = invoke-sqlcmd -InputFile "c:\users\security.sql" -serverinstance dbatest -database testdb;
$mydata | out-file c:\users\outputuser.sql;
Remove-PSSnapin SqlServerCmdletSnapin100;

如果您安装了 2012(或更新版本)，您可以跳过管理单元并导入 sqlps 模块:

If you have 2012 (or newer) installed, you can skip the snap-in and import the sqlps module:

push-location;
import-module sqlps;
Pop-Location;
$myData = invoke-sqlcmd -InputFile "c:\users\security.sql" -serverinstance dbatest -database testdb;
$mydata | out-file c:\users\outputuser.sql;
remove-module sqlps;

输出文件是否为您提供了您正在寻找的格式，我不能说，因为您已经指定了您要查找的内容，也没有显示查询本身.您可能希望将其导出为 CSV 格式，或者使用 format-table 将纯文本结构化一点，或者转换为 HTML.

Whether the output file gives you the formatting you're looking for or not, I can't say, as you have specified what you're looking for, nor shown the query itself. You might want to export it to CSV format instead, or use format-table to structure the plain text a bit more, or convert to HTML.

这篇关于使用 powershell 执行 .sql 文件并将输出存储在文本文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！