Introduction

When we are DBAs, we need to deal with files and folders all the time. We need to handle data files, transaction log files, system files, backup filess, log files, external files, BLOBs, etc. PowerShell is a great assistant for help us administering these files. In this article, we will learn the following topics in PowerShell:

• How to store information from a query to a HTML file.
• How to create a file with the current date  as a suffix in the file name.
• How to create a directory.
• How to copy files.
• How to share a folder.
• How to compare two files and find the differences.
• How to append two files.

Requirements

We need the following requirements:

• SQL Server Installed with PowerShell installed.

Getting Started

First, start PowerShell. You may have an icon in your start menu, or you may want to start a Powershell command line.

The first step is to move to your SQL Server, but typing "sqlserver:"

In this example, we will query the HumanResources.department table in Adventureworks. You can use any SQL table of your preference:

SELECT
       [Name]
      ,[GroupName]
      ,[ModifiedDate]
  FROM [AdventureWorks2016CTP3].[HumanResources].[Department]

Create a SQL folder in the c:\ drive or any path of your preference. Save the script in a .sql file named Department report.sql

Now, we will query the table using the script just created and then we will store the query results in a HTML file:

invoke-sqlcmd  -Inputfile "c:\sql\Department report.sql" | ConvertTo-HTML | Out-File C:\sql\departmentreport.html

ConvertTo-HTML | Out-File C:\sql\departmentreport.html are the magic words to save the query results in a HTML file:

This is fine. However, If we want to store the file with a name including the current date as a suffix. Can we do that in PowerShell?

Yes, Get-Date is used to show the date. -Format is used to display the result in a specified format:

Get-Date -Format o

This willl display the following result:

2016-08-31T07:39:26.4038230-07:00

However, I want my results in the format dd/mm/yyyy. To do that, use the following cmdlet:

Get-Date -Format d

It will show the date date in the format expected:

8/31/2016

Here you have some popular formats that you can use:

We will store the date in a string and convert the result to a string with this code:

$date=(Get-Date -Format d).toString()

We will convert the / to - to avoid problems in the file name with the replace method.

$date=$date.replace("/","-")

The date displayed will be the following:

8-31-2016

$filename = "c:\sql\departmentreport"+$date+".html"
invoke-sqlcmd  -Inputfile "c:\sql\Department report.sql" | ConvertTo-HTML | Out-File $filename.tostring()

As you can see, the HTML file now includes the current date as a suffix:

We will now create a new directory, named Reports, for all the reports named reports in the c:\sql folder. We use the New-Item cmdlet to do this.

New-Item -ItemType directory -Path C:\sql\report

When you execute the cmdlet, it will display the mode, the last time someone wrote to the file and the name.

The mode is used to display the type of object created. D means directory. Here you have a list of common Mode values:

If everything is OK, a new folder will be created:

To copy a file from one folder to another, the Copy-Item cmdlet is used:

Copy-Item C:\Sql\departmentreport8-31-2016.html C:\sql\report

We copied the file departmentreport8-31-2016 from c:\sql to the new report folder just created:

To share the folder, Windows 2012 now includes the New-SMBShare cmdlet:

New-SMBShare –Name "Report Shared" –Path “C:\sql\report” –FullAccess "YourDomain\GroupName"

The -Name is the name of the share and the path is the folder to share. In this example, we are assigning Full access to a group. You can assign access to groups (recommended) or individual users.

The following list includes the most common permissions:

If the cmdlet works, you will receive a message similar to this one:

You can verify the Sharing properties of the folder and you will notice that the folder is shared:

Another common task is to compare two files. This is very useful to compare the differences between reports, SQL Log files, etc. In this example, I am attaching a zip file named files to compare.zip at the bottom of this article in Resource Files with two files. The difference is the last row that includes executives in one of the files:

To compare the two text files, you can use the Compare-Object cmdlet:

Compare-Object -ReferenceObject $(Get-Content C:\sql\departmentreport8-31-2016.html) -DifferenceObject $(Get-Content C:\sql\departmentreport8-30-2016.html)

The commands will compare both files and show the differences:

Another common task is to append two files. In this example, we will append the files used before.

The Get-Content command is used to append two files in a file named union.html:

Get-content "C:\sql\departmentreport8-31-2016.html", "C:\sql\departmentreport8-30-2016.html" | Set-Content "C:\sql\union.html"

Get-content will get the content of both files and Set_Content will store the content in a file named union.html. Here it is the union.html file:

Conclusion

In this article, we learned how to store SQL query results in a file. Also, how to add specific dates to a name, how to copy, merge, compare, share folders and files in PowerShell. If you have questions or suggested articles related to this one, just write us your comments.

References

• Formatting Dates and Times
• Copy-Item
• Get-Date
• Compare-Object