SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerShell Solutions - Working with files

By Daniel Calbimonte,

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:

Specifier Sample
d 01/09/2016
D Thursday, September 1, 2016
g 01/09/2016 10:35 PM
t 10:35 PM
T 10:35:56 PM
U Thursday, September 1, 2016 10:35:56 PM
s 2016-09-01T10:35:56
y,Y September, 2016
F Thursday, september, 2016 10:35:56 PM
m, M September 1

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

With that variable, we can now save the results of the SQL script in a file with the current date as a suffix name:
$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:

Mode Attribute
D Directory
A Archive
r Read-only
h Hidden
s System

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:

Permission
-FullAccess, assigns full permissions
-ChangeAccess, used to modify the access to the folder
-NoAccess, to avoid access
-ReadAccess, includes read only 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

 

Resources:

files to compare.zip
Total article views: 1659 | Views in the last 30 days: 1
 
Related Articles
FORUM

Compare published reports

Compare Published reports across Servers

ARTICLE

Comparing and Synchronizing Two Folders with Azure

In this article, we will compare two folders using PowerShell, the command prompt and other tools.

FORUM

SharePoint Content Reporting

Best Tool For Reporting SharePoint Lists Content

FORUM

Reporting Services List Formating -- horizontal formatting

Reporting Services List Formatting.

ARTICLE

SSRS Report Content Filtering by User Group

How to get an SSRS user's AD group membership in order to filter report content

Tags
copy    
create    
powershell    
server    
sql    
 
Contribute