PowerShell implementation code that connects to the SQL Server database for operation _powershell

Core code

#配置信息 $Database = ' demodb ' $Server = ' win-ahau9no5r6u\dog ' $UserName = ' kk ' $Password = ' 123456 ' #创建连接对象 = N Ew-object System.Data.SqlClient.SqlConnection #使用账号连接MSSQL $SqlConn. ConnectionString = "Data source= $Server; Initial catalog= $Database user id= $UserName;p wd= $Password "#或者以 Windows Authentication connection MSSQL # $SqlConn. ConnectionString =" Data source=$

Server;initial catalog= $Database; integrated Security=sspi; " #打开数据库连接 $SqlConn. Open () #执行语句方法一 $SQLCMD = New-object System.Data.SqlClient.SqlCommand $SqlCmd. Connection = $SqlConn $S Qlcmd.commandtext = ' Delete Top (1) from dbo. B ' $SQLCMD. ExecuteNonQuery () #执行语句方法二 $SQLCMD = $SqlConn. CreateCommand () $SqlCmd. CommandText = ' Delete Top (1) from dbo. B ' $SQLCMD. ExecuteScalar () #方法三, the query displays $SQLCMD. CommandText = ' Select Name,recovery_model_desc,log_reuse_wait_desc from sys.databases ' $SqlAdapter = New-object System.Data.SqlClient.SqlDataAdapter $SqlAdapter. SelectCommand = $SQLCMD $set = New-object data.dataset $SqlAdapter. Fill ($set) $set. Tables[0] | Format-table-auto #关闭数据库连接 $SqlConn. Close ()

Here is the official Help document from Microsoft: Windows PowerShell: Using PowerShell to process databases

You can use some of the Windows PowerShell code to configure your database to store the information you need.

Don Jones.

The database is the king of data storage. So why not use these databases, such as SQL Server, to store management information that makes your life easier? You can store the server name, Windows version, Service pack level, last administrator login, this stuff.
It is easy with Windows PowerShell, because you can use it to access the Microsoft underlying database functionality. NET Framework. This now looks less like Windows PowerShell scripts and more like a C # program. However, the code you need is very easy to template. You can bring me here with a few minor changes it adapts to almost any situation.

For SQL scripts

Let's assume that you have a SQL Server 2008 database named SysInfo. A table of database-named servers. The table has five columns: server name, user name, Lastlogon, Reason, and spversion. This can be a varchar type of mostly simple text columns.
However, make Lastlogon a date-time column. It now uses the function that SQL Server sets to its default value. This way, each time you add a new row, it contains the current date and time without requiring you to manually specify it. Cause column varchar (MAX), if necessary, it can contain text, many.
Create a logon script in a Group Policy object (GPO), link the GPO to the organizational unit (OU) where your server resides. Every time someone logs on to the server to run the script. With Windows PowerShell script, you can do something on any computer that has Windows PowerShell 2.0 installed. You need to make sure that you have a GPO template that is based on the Windows PowerShell logon script option when you edit the GPO from Windows Server 2008 R2 (or Windows 7 Remote Server Administration Kit or boarding).
Here is the script you need. Please note that I use ↵ to indicate that the ENTER key should be pressed here. Just press the ENTER key when you come to the symbol, if you want it to work (you can copy the HTML using the symbol & Crarr; entity):

$reason = Read-host ' reason are you logging on ' ↵
$reason. Replace ("", "" ") ↵

These two lines prompt someone for the reason they are logging on to the server-this is a fairly important trace. The replacement feature replaces any single quotation mark with two single quotes, making sure that the SQL statement that we build later will work correctly. This is not intended to be a precaution against an intentional SQL injection attack. After all, is it right for us to talk about trusted administrators?

This line retrieves information about the operating system:

$os = Get Wmiinfo Win32_operatingsystem↵

The important data here is the name of the server and its current service pack version. You may also need the BuildNumber property to tell you the version of Windows that you are working on.
Part of this line load. The. NET framework responsible for processing the database:

[Assembly.reflection]::loadwithpartialname (' System.Data ') ↵

These lines create a new database connection:

$conn = New-object System.data.sqlclient.sqlconnection↵
$conn. ConnectionString = "Data source=sqlserver;initial catalog=sysinfo;integrated Security=sspi;" ↵

If you do not use SQL Server 2008, the connection string may be somewhat different (access to connectionstrings.com to find a sample connection string for various different databases):

$conn.open () ↵

The connection is now open and available for use.

These two lines are created by SQL commands that I will use to send queries using SQL Server. I have set the Connection property set to open so that Windows PowerShell has actually sent the command:

$cmd = New-object System.data.sqlclient.sqlcommand↵
$cmd. Connection = $conn ↵

All this hard work. It creates an SQL insert query to insert a new row into a database table. Note that I inserted four messages into the query using the –F format operator. The information is inserted into the {x} placeholder, which provides the same order in the comma-delimited list of operators as follows:

$cmd. CommandText = "INSERT into servers (Servername,username,spversion,reason) VALUES (' {0} ', ' {1} ', ' {2} ', ' {3} ')"-f
$os. __server, $env username, $os. ServicePackMajorVersion, $reason ↵

I will now execute the query and close the database connection:

$cmd. ExecuteNonQuery () ↵
$conn. Close () ↵

Make sure you close the connection or you'll have to punch your head in the back of the database administrator. You will see that for connectionstrings.com, you can access a large number of databases.
If you are using SQL Server, you need to change the "OLE DB" object name "SqlClient" section. In addition, it has not been recommended to use file-based database access like. For one, to get the driver you have to install on, access your server, it will be a scary idea. Alternatively, the performance of these databases is not up to the level you will need to work towards.

If you don't have an instance of SQL Server that can host the database, get a place for the Express version installation. It's low enough that this technology will likely generate traffic.
Obviously, you can modify this to do quite a lot of technology. You can add columns to the database and have your scripts collect additional information. One thing you need to do is to be proficient in the SQL language itself. You don't need a guru, but you want to be able to write basic queries.
If you need to get started on the SQL language (almost the same level of each major database platform), check out this video series to create an industry standard SQL language provides a complete tutorial. It also includes major differences like SQL Server, Oracle, MySQL platform, and so on.
Here is the entire script:

$reason = Read-host ' reason are you logging on ' ↵
$reason. Replace ("" "," "") ↵
$os = Get-wmiinfo win32_ Operatingsystem↵
[Assembly.reflection]::loadwithpartialname (' System.Data ') ↵
$conn = New-object System.data.sqlclient.sqlconnection↵
$conn. ConnectionString = "Data source=sqlserver;initial catalog=sysinfo;integrated Security=sspi;" ↵
$conn. Open () ↵
$cmd = new-object system.data.sqlclient.sqlcommand↵
$cmd. Connection = $conn ↵
$ Cmd.commandtext = "INSERT into servers (Servername,username,spversion,reason) VALUES (' {0} ', ' {1} ', ' {2} ', ' {3} ')"-f $os. __server, $env. Username, $os. ServicePackMajorVersion, $reason ↵
$cmd. ExecuteNonQuery () ↵
$conn. Close () ↵