where-Object<\/B> Windows PowerShell cmdlet to locate only those databases for which the backup is greater than a day:<\/P>DIR “SQLSERVER:\\SQL\\serverName\\instanceName\\Databases” |\nwhere-Object {((get-Date)-($_.LastBackupDate)).days -gt 1} \n<\/PRE>\nYou can see that the Where-Object<\/B><\/I>cmdlet is followed by a set of braces. This encloses what we want to do with the collection of items to the results of the dir <\/B>command. We are just asking for the LastBackupDate<\/B> property on each item (remember that we can see those properties by using the Get-Member <\/B>cmdlet). The $_<\/B><\/I>part stands in for each database, one at a time. The interesting part is that we are enclosing that whole string with another cmdlet that can read dates\u2014G<\/B>et-Date<\/B>.<\/B> We ask for a property of that cmdlet (the days<\/B> part), and the whole thing is enclosed by that Where-Object<\/B> cmdlet. So in plain old English, here is what we are really saying:<\/P>\n
\u201cSee all those databases? Show me the ones that have a property of <\/I>LastBackupDate<\/B> greater than one day.\u201d<\/P>\n
Even though this shows us the databases, it does not show the information that we want. No worries. We will just pipe that out to another command that selects the objects and shows certain properties. The name of this mysterious cmdlet is Select-Object<\/B>:<\/P>DIR “SQLSERVER:\\SQL\\serverName\\instanceName\\Databases” | \nwhere-Object {((get-Date)-($_.LastBackupDate)).days -gt 1} | \nsort-Object -Property LastBackupDate | \nselect-Object Name, RecoveryModel, LastBackupDate\n<\/PRE>\nNot bad. What else can we do? Do you remember a couple of articles back when we used the Server Management Objects (SMO) Library to script out certain objects? With the Windows PowerShell provider for SQL Server, it is even easier. It does not even need much explanation, although it could do with a warning.<\/P>\n
Because you are in the SQL Server PowerShell provider, each SMO library object class is now a directory, exactly like the databases we just used. Now we can type dir<\/B> to gain access to those objects. One dir<\/B> command and we can loop through all the objects it contains. But as the Scripting Guys\u2019 grandmother used to say, “Just because you can<\/I> do something, does not mean that you should<\/I><\/I>do something.” Scripting is one of those times. If you script out all the tables in a database, you could really take some time and processor cycles on your system. You can do it, of course, but we are just saying that perhaps you might want to take a microsecond and reconsider. <\/P>\n
With all those warnings and repeating ourselves that you should be on a test system, here is a simple script to script out all the databases. Not all the objects in them, mind you, but the databases themselves: <\/P>
DIR “SQLSERVER:\\SQL\\serverName\\instanceName\\Databases” | \nforEach-object {$_.Script()}\n<\/PRE>\nThe same command works for other containers such as tables and views. You could even restrict the tables with that useful Select-Object<\/B> cmdlet to limit the scripting to a certain schema, name, or other property. Oh\u2014you want that to go out to a file? No problem. Just add the Out-File<\/B> cmdlet with another pipeline operator:<\/P>DIR “SQLSERVER:\\SQL\\serverName\\instanceName\\Databases” | \nforEach-object {$_.Script()} | \nout-File \u2013FilePath C:\\temp\\Scripts.sql\n<\/PRE>\nAnother way to the SQL provider is to send the script to a .sql file, and then use the script file to make a change to the database object. Run the script again, but change the output file to another name. Then just use the Compare-Object<\/B> cmdlet to see the differences: Get-Help <\/B>C<\/B>ompare-Object<\/B>.<\/P>\n
No problem, we have several ways to do this. The first way is just to use Invoke-Sqlcmd <\/B>cmdlet that the SQL Server provider has built in. Here is a sample. As always, replace the serverName<\/B> and instanceName<\/B> for your system:<\/P>invoke-Sqlcmd -query “select @@VERSION” \u2013ServerInstance serverName\\instanceName\n<\/PRE>\nYou must be able to connect to that instance with different credentials? No worries:<\/P>
invoke-Sqlcmd -query “select @@VERSION” \u2013ServerInstance serverName\\instanceName `\n\u2013UserName yourUserName \u2013Password yourPassword\n<\/PRE>\nReplace the yourUserName<\/B><\/I>part with, you know, you user name and the same for the password. By the way, this works for SQL Server 2005 and even SQL Server 2000 systems, too.<\/P>\n
\u201cHey, wait a minute,\u201d we hear you say. \u201cThat is cheating. I want a real drive letter on that other system, exactly as I have it here. I want to browse the objects, do the dir<\/B> thing and all that. Can I do that?\u201d No problemo. We will just make a completely new drive, by using the Windows PowerShell cmdlet called New-PSDrive<\/B>. It makes a new Windows PowerShell drive from any installed provider. It requires a string representing the new drive and the kind of provider you want to use. From there, you add the server name that you want to connect to and there you have it:<\/P>new-PSDrive “OtherServer” -PSProvider SqlServer -Root “SQLSERVER:\\SQL\\ serverName\\instanceName”\n<\/PRE>\nNow just type OtherServer<\/B>:<\/I> (or whatever you called it) and press ENTER. Voila! You are in the Instance<\/B> folder.Now that you have learned enough to be dangerous, you can really get in there and dig around to think of all kinds of useful scripts for working with your databases. And here is the real beauty of working with Windows PowerShell and SQL Server: Windows PowerShell lets you work with all kinds of objects in addition to the database, such as files, Web pages, and more. In fact, you can manage Windows servers, Microsoft Exchange servers, System Center, and most other Microsoft products with Windows PowerShell. And because Windows PowerShell is based on the .NET Framework, you have access to all those items, too. Who says DBAs can\u2019t learn new tricks?<\/P>\n
<\/P>\n
Well, SC, thanks for an excellent question to end this week. Buck Woody, you have our sincere appreciation for an entertaining and educational series of SQL Server articles. We are skipping Quick-Hits Friday this week because Monday was a holiday for us, and sometimes even we need to take a holiday. Next week, we take a look back at some of the best content from Scripting Games history. Just to get you all juiced up for this year\u2019s Summer Scripting Games, June 15\u201326. Until then, peace.<\/P>\n
<\/P>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/B><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have several items I have to check daily on my SQL 2008 servers. I currently have some VBScripts that do many of my tasks, but there are some new things I want to do. I figure that Windows PowerShell would be easier. Do you have any pointers?– SC Hi SC, It […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[729,146,56,3,176,730,45],"class_list":["post-53643","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-buck-woody","tag-databases","tag-guest-blogger","tag-scripting-guy","tag-sql-server","tag-sql-server-2008","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have several items I have to check daily on my SQL 2008 servers. I currently have some VBScripts that do many of my tasks, but there are some new things I want to do. I figure that Windows PowerShell would be easier. Do you have any pointers?– SC Hi SC, It […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/53643","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=53643"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/53643\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=53643"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=53643"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=53643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/H2>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
\n