Skip to main content

Discussion: How to script Database Objects in PowerShell

??Recovering from the unexpected disasters is one of the most important tasks of a DBA. Usually, the “Backup and restore” is the best option often chosen for recovery, scripting the database schema. More often, a user drops a stored procedure or change a view by mistake and you don’t wish to restore the entire database just to get back a single piece of code. Using the PowerShell, it is easy to script all the objects in the database manually. Moreover, you can schedule the scripting anytime as you like and you can do it for as many of your servers as you like.
Now, let’s discuss about scripting all the tables in a database. This step is about connecting to a database and navigates to the “tables” node.
>cd SQL:\SQLServer\localhost\default\databases\MyDB\Tables
Now, we have successfully connected to the tables node we can script all of the objects.
Dir | %{$_.Script()}
Now, let me explain the code. The code is about pulling a list of tables with the “dir” command. If you wish to use “gci”, you can use it. Then, we can pipe ‘|’ the results of that command to the foreach loop command. Here, the “%” is the alias foreach command. Everything in the foreach (%) loop is surrounded by the double brackets {}. If you’re in the foreach construct, you call the script method for each object encountered. Now, the ‘$_’ is the built-in iteration variable in PowerShell. Each of the iterations of the foreach loop, the ‘$_’ represents the one currently in the loop.

Now, let’s discuss about scripting out the stored procedure in the same database.
>cd SQL:\SQLServer\localhost\default\databases\MyDB\StoredProcedures
>Dir | %{$_.Script()}

If you look at the script, you can find no changes in the objects and the node you’re using gets changed. Moreover, you can follow the following tricks to change the nodes. Repeating the above steps for each node is the easiest way to script all the objects in your database. Just look at the code.
>cd SQL:\SQLServer\localhost\default\databases\MyDB\Tables
>Dir | %{$_.Script()}
>cd SQL:\SQLServer\localhost\default\databases\MyDB\StoredProcedures
>Dir | %{$_.Script()}
>cd SQL:\SQLServer\localhost\default\databases\MyDB\Views
>Dir | %{$_.Script()}

This prints the output to the screen. You can save the scripts to a file, so that you can restore them when it is required. Therefore, the following is a little modified script.
>dir | %{$_.Script() | out-file c:\Tables.txt -append}
This script says that once you’ve the object scripted, pipe the output to a file located at c:\Tables.txt and append the output to the end of the file. Do you know what happens when you don’t use the ‘-append’ flag the file? It will be overwritten for each object and you’ll get only the last object in the file. If you wish to have the objects in the HR schema, you can pass the “Dir” command to a “where-object” to limit the results and the script is as follows.
>dir | %{$_.Script() | where-object{$_.Schema –eq “HR”} | out-file c:\HRTables.txt -append}
If you wish to turn this process into a process that runs on multiple boxes, you can do it as it is a simple process. You can do it in many ways. However, the following steps are about putting the list of servers into a text file and cycling through them one at a time. Therefore, you can create a new text file and put your servers inside. Check the following script out.

Now, all you’ve to do is to put create a foreach that cycles through each server in the file. Check the entire line of the code out.
>get-content c:\Servers.txt | %{cd SQL:\SQLServer\$_\default\databases\MyDB\Tables;  dir | %{$_.Script() | out-file “c:\$_Tables.txt” -append}}

The only change made to the original code is the replacement of the server name with the ‘$_’ variable as it represents the current server in the loop coming from the Servers.txt file and enclosing the out-file filename in double quotes. Therefore, it is prefixed the filename itself with the ‘$_’ variable so that the server name is depicted in the filename itself. The main thing you’ve note is the backtick (`) is necessary as it is an escape character and informs the PowerShell that the ‘$_’ is the variable name and not ‘$_Tables’.
The main thing you’ve note about this is the code will get failed if you just paste it into SSMS to run.


Popular posts from this blog

New Micro battery, power packed for high performance

For a size of a few millimetres, the new micro-batteries are powerful enough to jump start a car battery and then quickly charge the cell phone. Researchers from the University of Illinois have developed the micro batteries which can be used to drive compact electronic devices and new applications for radio communications. The micro batteries are so good that they can even out power the super capacitors. The results were published in ‘Nature Communications’ in the April 16 issue. The micro batteries have energy and power and with the researchers tweaking the structure of the micro batteries a little bit, its use can be for wide range applications. The high performance of the micro battery is based on its 3-dimensional micro-structure. Based on the design of fast charging cathode by Professor Paul Braun’s group, researchers developed the matching anode and integrated it with the fast charging cathode at a micro-scale and made a complete battery with high performance. These batteries cou

HTC Touch Diamond SmartPhone!

The phones design is an innovative in which the user has the geometric form language to make sure a clutter-free environment for screening content. The San Francisco based studio One & Co collaborated with HTC Taiwan Design team to create Diamond HTC smartphone. The HTC phone is quite wonderful with design and the rapid development in the smartphone market the HTC has taken innovative steps to capture the SmartPhone market HTC develops these kind of new inventive designs. Inspired

Google Health Login Page is Ready!

Google's Marissa Mayer announced that the Google Health will be launched on 2008. The service of the Google Health Login Page is ready. It's only the first intro page is displayed and I didn't get passed to the next page. In the same page y0u can see the information displaying on the Google Health. If you point your mouse on the below link you can visit the Google Health Page With Google Health, you can: * Build online health profiles that belong to you * Download medical records from doctors and pharmacies * Get personalized health guidance and relevant news * Find qualified doctors and connect to time-saving services * Share selected information with family or caregivers This will be a nice development about caring health and this will helps in having some cautious about health. Inspired