{"id":80291,"date":"2018-08-08T21:55:04","date_gmt":"2018-08-08T21:55:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=80291"},"modified":"2021-08-24T13:39:19","modified_gmt":"2021-08-24T13:39:19","slug":"introduction-to-powershell-with-sql-server-using-invoke-sqlcmd","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/introduction-to-powershell-with-sql-server-using-invoke-sqlcmd\/","title":{"rendered":"Introduction to PowerShell with SQL Server Using Invoke-SQLCMD"},"content":{"rendered":"

One of the most valuable tools I\u2019ve seen added to my toolkit over the years is PowerShell. While I had gotten very adept at writing some pretty complex CMD files, PowerShell has opened up whole new possibilities. Often now when a client says, \u201chey, I need to do X\u201d and X is not specifically a query, my first thought is to reach for PowerShell and use that to solve the problem.<\/p>\n

A client recently asked for something that is a common request: \u201cCan we BCP out the data from these tables, just in case we want to reload the data after the upgrade?\u201d<\/p>\n

This could be done with a cursor and some fancy T-SQL, but what\u2019s the fun in that? Not much. So, I reached for PowerShell.<\/p>\n

PowerShell to the Rescue<\/h2>\n

The first thing one has to keep in mind with PowerShell is that it deals with objects. If you\u2019re used to writing pure SQL, this can be a bit hard to get your mind around at times, but once you get the concept, it\u2019s quite useful.<\/p>\n

I\u2019m going to take the time to show you how I built up to my final goal of exporting all the tables in a database, and this should help you understand how PowerShell works and how my brain works. The following examples can be run in the PowerShell ISE, a lightweight PowerShell IDE (integrated development environment) that comes with Windows 10. Cut and paste each line into the text window and then press F5. If you want to run only part of a script, highlight the lines in question and press F8.<\/p>\n

$bcpstring =\"bcp Sales.Customer out c:\\temp\\customers_data.bcp -S MYSERVER -d adventureworks2016 -T -E -n\" <\/pre>\n
This is pretty straightforward: it\u2019s a basic BCP command and it\u2019s assigned to a string. Obviously, you should replace MYSERVER<\/strong> with the name of your server and replace the actual tables and databases you want to use.<\/p>\n
Saving the command to a string is not that useful, so to do something with it, enter and run the next line:<\/p>\n
Invoke-Expression $bcpstring <\/pre>\n
 <\/p>\n
Assuming there are no errors, you\u2019ll see something like this in the output window at the bottom of the app:<\/p>\n
<\/p>\n
\n PowerShell cmdlets<\/h2>\n
PowerShell uses what are called cmdlets<\/strong>. They are in the form of verb-noun. This one, Invoke_Expression<\/strong>, should be self-explanatory: invoke (aka run) a particular expression. In this case, execution the previously constructed string. When this executes you should see it export over 19,000 rows.<\/p>\n
If you want to simply see what the string $bcpstring<\/strong> contains, you can run Write-Host $bcpstring<\/strong>, and this will show you the string.<\/p>\n
<\/p>\n
To make this useful, you will probably want to try a few more commands. This is where objects come in. To get a list of tables, try running this:<\/p>\n
$tables = invoke-sqlcmd -server MYSERVER -Database AdventureWorks2016 \"select ss.name as schema_name, so.name as table_name, ss.name+'.'+so.name as full_name from sysobjects so inner join sys.schemas ss on ss.schema_id=so.uid where type='u' order by ss.name, so.name\" <\/pre>\n
\n
NOTE: If you do not have the Invoke-sqlcmd installed, you can install it with instructions from here<\/a><\/em><\/p>\n<\/div>\n
Take a close look at the code. The cmdlet here should be obvious, you\u2019re going to run a SQL command. In this case, it\u2019s a simple query that will return the schema name and table name of every user defined table. To be more complete, it also returns the dotted two part name of each table. The results of the query are stored in the $tables<\/strong> variable.<\/p>\n
To see the list of tables, run this:<\/p>\n
Write-Host  $tables <\/pre>\n
The output should look similar to this:<\/p>\n
<\/p>\n
Working with Objects<\/h2>\n
You\u2019ll find you don\u2019t get what you expect at all; you don\u2019t get a list of tables or schemas or anything useful. You get a series of lines that simply says System.Data.DataRow<\/strong>. This is your clue that something interesting is up. Remember how I mentioned that PowerShell works with objects. Well, $tables<\/strong> is not a string like $bcpstring<\/strong>. It\u2019s an object. So, you need to treat it as an object. Try this command instead:<\/p>\n
write-host ($tables | Format-Table | Out-String) <\/pre>\n
Without delving too deep into this, the |<\/strong> symbols are pipes. So essentially the command is piping $tables<\/strong> to Format-Table<\/strong> which, in turn, is piped to Out-String<\/strong>. This is then written by write-host<\/strong>. Now strictly speaking in this case, Format-Table<\/strong> isn\u2019t necessary in this situation, but it\u2019s a good idea to get used to using it because it will be useful for other PowerShell objects.<\/p>\n
Now if you execute that cmdlet you get a nice table showing you the results of your select.<\/p>\n
<\/p>\n
If you\u2019re curious and try to execute just the variable $tables<\/strong> with nothing else, you might notice you get the same format. This is true. I didn\u2019t start here though because I wanted to focus on the fact that $tables<\/strong> is an object and that Write-Host<\/strong> operates on objects. Now that you know the secret, take things a step further and pass the output of $tables <\/strong>to a new cmdlet: Out-GridView<\/strong>.<\/p>\n
If you execute:<\/p>\n
$tables | Out-GridView <\/pre>\n
You\u2019ll see a very nice GUI window pop up:<\/p>\n
<\/p>\n
That Add criteria<\/em> button looks interesting, doesn\u2019t it?<\/p>\n
Click on it, select tablename<\/em> and then tell the window the table name must contain CUSTOMER<\/em> because you want to know all the tables that may be a customer table. In this case, I have a couple of custom objects containing customer dat.<\/p>\n
<\/p>\n
That can be pretty useful in many situations but doesn\u2019t help meet the final goal \u2013 exporting all the tables. You can take advantage of the fact that it\u2019s an object and build up a loop.<\/p>\n
$tables = invoke-sqlcmd -server MYSERVER -Database AdventureWorks \"select ss.name as schema_name, so.name as table_name, ss.name+'.'+so.name as full_name from sysobjects so inner join sys.schemas ss on ss.schema_id=so.uid where type='u' order by ss.name, so.name\"\r\nforeach ($table in $tables)\r\n{\r\n    $bcpstring =\"bcp $($table.table_name) out c:\\temp\\$($table.table_name).bcp -S MYSERVER -d adventureworks2016 -T -E -n\"\r\n    write-host $bcpstring\r\n}<\/pre>\n
The foreach<\/strong> keyword and {}<\/strong> form the loop. A new object is created, $table<\/strong> which contains a single table from $tables<\/strong> each time though the loop. Since a specific property of the object is needed, the code uses $table.table_name<\/strong>. PowerShell has a nice property that it can interpret values within strings. However, since this is an object within a string, you need to make sure PowerShell proper expands it. This is why the object is wrapped in $()<\/strong>. You\u2019ll see when you run this, that you get a nice fully formatted BCP string.<\/p>\n
<\/p>\n
For now, it\u2019s using write-host $bcpstring<\/strong> so that you can see what you\u2019re about to do, without actually writing out the data to files.<\/p>\n
Fine-tuning the Command<\/h2>\n
But wait. There\u2019s a problem. Remember, not all the tables belong to the dbo<\/strong> schema. The schema must be included.<\/p>\n
$tables = invoke-sqlcmd -server MYSERVER -Database AdventureWorks \"select ss.name as schema_name, so.name as table_name, ss.name+'.'+so.name as full_name from sysobjects so inner join sys.schemas ss on ss.schema_id=so.uid where type='u' order by ss.name, so.name\"\r\nforeach ($table in $tables)\r\n{\r\n    $bcpstring =\"bcp $($table.schema_name).$($table.table_name) out c:\\temp\\$($table.schema_name).$($table.table_name).bcp -S MYSERVER -d adventureworks -T -E -n\"\r\n    write-host $bcpstring\r\n} <\/pre>\n
<\/p>\n
Now, this will work, but what if there is a table with a space in its name or is a reserved word (such as PLAN, which came up in the database I tested this on)? You won\u2019t see the problem when using write-host<\/strong>, but it will be a problem when passing $bcpstring<\/strong> to the Invoke-Expression<\/strong> cmdlet.<\/p>\n
To correct this issue, try this:<\/p>\n
$tables = invoke-sqlcmd -server MYSERVER -Database AdventureWorks \"select ss.name as schema_name, so.name as table_name, ss.name+'.'+so.name as full_name from sysobjects so inner join sys.schemas ss on ss.schema_id=so.uid where type='u' order by ss.name, so.name\"\r\nforeach ($table in $tables)\r\n{\r\n    $bcpstring =\"bcp [$($table.schema_name)].[$($table.table_name)] out c:\\temp\\[$($table.schema_name)].[$($table.table_name)].bcp -S MYSERVER -d adventureworks -T -E -n\"\r\n    write-host $bcpstring\r\n} <\/pre>\n
<\/p>\n
The filenames may look a bit funky with brackets in them, but this works.<\/p>\n
Getting Even More Dynamic<\/h2>\n
But wait, there\u2019s more! Hopefully you\u2019re already starting to see the power here. You have hardcoded the name of the server and database itself, but there\u2019s really no reason to do that. You can loop through a list of databases. Since the list of databases is something SQL Server itself maintains, you can simply ask SQL Server for a list of databases.<\/p>\n
$sourceserver = 'MYSERVER' \r\n$databases = invoke-sqlcmd -server $sourceserver 'select name from master.sys.databases where database_id > 4 order by name' <\/pre>\n
This command ignores the system databases by only selecting databases with a database_id > 4<\/strong>. You could of course include them or use any other criteria you want in the WHERE<\/strong> clause.<\/p>\n
Now you have a new object called $databases<\/strong> that contains a list of databases on the server. The next step should be obvious: build a loop based on that list.<\/p>\n
$sourceserver = 'MYSERVER' \r\n$databases = invoke-sqlcmd -server $sourceserver 'select name from master.sys.databases where database_id>4 order by name'\r\nforeach ($database in $databases)\r\n{\r\n    $tables = invoke-sqlcmd \u2013server $sourceserver -Database $database.name \"select ss.name as schema_name, so.name as table_name, ss.name+'.'+so.name as full_name from sysobjects so inner join sys.schemas ss on ss.schema_id=so.uid where type='u' order by ss.name, so.name\"\r\n    \r\n    foreach ($table in $tables)\r\n    {\r\n        $bcpstring =\"bcp [$($table.schema_name)].[$($table.table_name)] out c:\\temp\\[$($table.schema_name)].[$($table.table_name)].bcp -S \u2013d $sourceserver $($database.name) -T -E -n\"\r\n        write-host $bcpstring\r\n    }\r\n}<\/pre>\n
You should now see bcp statements for every table in every database!<\/p>\n
If you\u2019re paying close attention, you\u2019ll see I snuck something extra in there. I also made the name of the server itself a variable. I\u2019ll leave it as an exercise, but you should be able to see how you could save this script and call it with a parameter with the name of the server you want to get your bcp dump from. Also of course change the write-host $bcpstring<\/strong> command in the final example to Invoke-Expression $bcpstring<\/strong>. You should also be thinking about how you could change the location where the files are written to, and perhaps again use a parameter to handle that.<\/p>\n
And there you have it a quick and easy way to bcp out all of your data across one or more databases in just a couple of lines of PowerShell.<\/p>\n
Conclusion<\/h2>\n
I hope you enjoyed this first look at using PowerShell with SQL Server. In a future article, I will discuss how PowerShell can actually treat your entire SQL Server as an object.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
PowerShell is the preferred scripting tool used by Windows and Active Directory administrators. It can also be used to administer SQL Server or even just export data. In this article, Greg Moore demonstrates how to use the PowerShell cmdlet Invoke-SQLCMD to export data from SQL Server.…<\/p>\n","protected":false},"author":319367,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53,35],"tags":[95506],"coauthors":[61343],"class_list":["post-80291","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-featured","category-powershell","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80291","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/319367"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=80291"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80291\/revisions"}],"predecessor-version":[{"id":80361,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80291\/revisions\/80361"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=80291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=80291"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=80291"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=80291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}