Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automatically Build a SQL Server Script Database Library from Your Script Files


By:   |   Updated: 2015-11-24   |   Comments (5)   |   Related: More > Database Administration

Problem

As a contract consultant, I frequently need to create scripts for various tasks, so over the years with different clients, I have accumulated lots of scripts and they are often located in complex folders on different computers, is there any good way to archive and manage these scripts?

Solution

Being a SQL Server DBA, I always enjoy creating solutions based on databases. For text-based scripts, like T-SQL, batch, PowerShell, VBScript, etc., the file sizes are usually not big, in the range of a few KBs to a few hundred KBs. They are pretty suitable to be stored in a regular SQL Server table with a varchar(max) column. So with PowerShell, we can easily create a script repository in a few lines of codes and the best part is that you can always backup this database to a small file (assuming you can use backup compression), and carry it with you and restore it in a new environment. Searching scripts on file names or the script content on a table is way easier than searching the file system. So here is the solution logic:

  1. Use PowerShell get-childitem (alias: dir) to recursively scan through a folder of interest for script files
  2. Read the file content via get-content (alias: gc), and then insert the content, with other collected information, into a pre-defined database table.

Here is the code.

First we will create a table in SSMS to store the data.

Use MSSQLTips -- change to your own
if object_id('dbo.ScriptRepository', 'U') is not null
	drop table dbo.ScriptRepository;
go
create table dbo.ScriptRepository (
  id int identity primary key
, Folder varchar(300)
, [FileName] varchar(300)
, Script varchar(max) -- change to nvarchar(max) if needed
, LogDate DateTime default Current_Timestamp
);

Next, we will use a PowerShell script to do the file archiving.

#requires -version 3.0;
$folder ='c:\DBAdmin\'; #change to your own folder
[string[]]$include ="*.sql", "*.ps1"; # you may add other file types, such as ".bat", ".mdx", ".xmla" etc.

#create a System.DataTable object for the file uploading
$dt = New-Object System.Data.DataTable;

$dc = New-Object System.Data.DataColumn('Folder', [System.String]);
$dt.Columns.Add($dc);

$dc = New-Object System.Data.DataColumn('FileName', [System.String]);
$dt.Columns.Add($dc);

$dc = New-Object System.Data.DataColumn('Script', [System.String]);
$dt.Columns.Add($dc);

dir -LiteralPath $folder -Include $include -File -Recurse | 
foreach {
    $dr = $dt.NewRow();
    $dr.Folder = $_.Directory;
    $dr.FileName = $_.name;
    $dr.Script = (gc -path $_.FullName -raw);
    $dt.Rows.Add($dr);
}

Save-DataTable -SQLInstance tp_w520 -DestinationDB mssqltips -DestinationTable 'dbo.ScriptRepository' -SourceData $dt `
-ColumnMapping @{Folder='Folder'; FileName='FileName'; Script='Script'; } # replace tp_w520 / mssqltips to your own SQL Server instance and database

To read more about the Save-DataTable function you can read my previous tip.

When we want to extract a specific script from the repository to a local drive, we can do this like this:

  1. Define a query to retrieve the script(s) of interests
  2. Define the destination folder where we want to save the script(s)
  3. Run the PS script
Import-Module sqlps -DisableNameChecking; #this is only applicable to sql 2012+ versions
set-location c:
[string]$destPath = 'c:\temp\'; # change to you own destination folder
[string]$qry = "select filename, script from dbo.ScriptRepository where id in (1, 3)"; # modify the query to your needs

$db = get-item sqlserver:\sql\tp_w520\default\databases\mssqltips #change tp_w520\default to your own sql instance
                                                                  #mssqltips is the db where dbo.ScriptRepository resides
$ds = $db.ExecuteWithResults($qry);
$ds.tables[0].rows | % { $_.script | out-file -FilePath "$destPath\$($_.filename)" -Force} 

Summary

This tip discusses a simple way to quickly archive all script files to a central repository database. It helps me to preserve my numerous scripts in a place where I can easily access and search for my needed scripts.

There can be other uses for this tip, for example, in a team environment, we can load all the team's scripts into a repository database instead of SharePoint or a shared storage folder, which is difficult for finding a script. If necessary, we can rely on the powerful Full-Text Search function to do sophisticated queries on scripts.

One note, if the length of a fully qualified file name is more than 260 characters, you will get error like the following

dir :The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

Next Steps

You can modify the code to add any additional properties you want, such as file CreationTime, LastWriteTime, Version, etc. You may also consider categorizing all your scripts based on the folders (assuming your folder names contain some category meaning) and to make it even cooler, you can think bigger, such as how to automate or schedule your administration from the script library, or how to respond to common issues by running pre-defined scripts.

The following tips do similar tasks in different ways, so check them out:

Test the scripts in development first and modify the PS scripts to make PS functions you can use in command line mode.



Last Updated: 2015-11-24


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 24, 2016 - 5:00:31 PM - jeff_yao Back To Top

 

Hi @Bender,

The -raw parameter is introduced for get-content since PowerShell version 3, you can see the info here ( https://technet.microsoft.com/en-us/library/hh847788.aspx ):

-Raw <switch>

Ignores newline characters and returns the entire contents of a file in one string. By default, the contents of a file is returned as a array of strings that is delimited by the newline character.

Raw is a dynamic parameter that the FileSystem provider adds to the Get-Content cmdlet. This parameter works only in file system drives.

This parameter is introduced in Windows PowerShell 3.0. (I hightlighted this myself)

So I am not sure how come you encountered this issue, are you sure you are using PowerShell V4 as you mentioned in your previous comment?

To check PS version, use this:

$PSVersionTable

In my current environment, I have this:

Name                           Value                                                                                                                                                                                                           ----                           -----                                                                                                                                                                                                             

PSVersion                      5.0.10514.6                                                                                                                                                                                            

WSManStackVersion              3.0                                                                                                                                                                                                    

SerializationVersion           1.1.0.1                                                                                                                                                                                                   

CLRVersion                     4.0.30319.42000                                                                                                                                                                                      

BuildVersion                   10.0.10514.6                                                                                                                                                                                            

PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                                                           

PSRemotingProtocolVersion      2.3              

Just let me know if you still have the issue. (I recommend you to run the script in your PS ISE window and see how it goes)

Thanks,

Jeff   


Wednesday, February 24, 2016 - 4:06:17 PM - Bender Back To Top

 Hello Jeff,

 

I used the Save-DataTable you provided and that issue is resolved; However, I am still getting this error. It is one of the errors I was getting before. 

 

Get-Content : A parameter cannot be found that matches parameter name 'raw'.

At line:60 char:40

+     $dr.Script = (gc -path $_.FullName -raw);

+                                                      ~~~~

    + CategoryInfo          : InvalidArgument: (:) [Get-Content], ParameterBindingException

    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.GetContentCommand

 

Thank you for your help and assistance!!

 


Tuesday, February 23, 2016 - 12:24:54 PM - jeff_yao Back To Top

Hi @Bender.

 

Thanks for reading and trying to use the tip.

 

You are missing Save-DataTable, which I mentioned in the tip, for your convenience, I put the link here (in the middle of the tip, there is the script for Save-DataTable function)

https://www.mssqltips.com/sqlservertip/3632/monitoring-sql-server-transactional-replication-latency-via-powershell-and-rmo/

 

You can simply copy the code into your existing script (put Save-DataTable in the beginning of the script).

 

Let me know if you still have issue.

 

Thanks,

Jeff


Tuesday, February 23, 2016 - 8:52:26 AM - Bender Back To Top

 Hello, 

 

I have been following this guide and when I am attempting to copy the script files into my database via the powershell script you have under the "NEXT, We will use a powershell script to do the file archiving." I am getting the following error. 

 

Get-Content : A parameter cannot be found that matches parameter name 'Raw'.

At line:22 char:40

+     $dr.Script = (gc -path $_.FullName -Raw);

+                                        ~~~~

    + CategoryInfo          : InvalidArgument: (:) [Get-Content], ParameterBindingException

    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.GetContentCommand

 

Save-DataTable : The term 'Save-DataTable' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is 

correct and try again.

At line:26 char:1

+ Save-DataTable -ServerInstance MYINSTANCE-DestinationDB Scripts -Destinat ...

+ ~~~~~~~~~~~~~~

    + CategoryInfo          : ObjectNotFound: (Save-DataTable:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

 

I checked my version and I am using Version 4 of powershell. Are the scripts different for different versions of powershell? If I install powershell Version 3 will this script run?

 

Thank you


Wednesday, December 02, 2015 - 8:17:49 PM - Rick Willemain Back To Top

excellent article !  So well written.. 

Thank you


Learn more about SQL Server tools