Here’s a preview on what I’ve been doing for work. I create about 12 new functions to assist me in automating a task to copy table(s) from one database to another. Then, this process will also rebuild the data, indexes, and constraints from the scripts extracted during this process.
All these functions are using embedded T-SQL scripts to assist in the scripting of the SQL Table objects. Finally, I will be ending having a module to load all my functions when is needed.
Just recently, I updated two on the main functions I posted in the Microsoft Script Repository:
1. “Function Write-SQLCreateTable” – Updated – 05/25/2011: Added three new switch parameters so we can create individual scripts for:DropTableOnly, CreateTableOnly, and TurnOffidentity.
2. “Function Write-SQLInsertSelect” – Updated – 05/25/2011: Add ability to detect if there’s an Identilty Column so it can add the “Set Identity_Insert …” line. And, Add set-location go back to your “Home Drive” when the process completed.
These two updated functions require you have the SQLPS module or in won’t work. That’s why I include to do both Import and Remove of SQLPS module. Also, these functions will not script out Indexes, and Constraints.
In my new SQLDevTools Module, all functions will not import/remove the SQLPS module. It will included functions to script out Indexes, Check Constraints, Foreign Keys and even Triggers. Functions name are change slightly, and a test functions script is included to assist in building rebuilding a table in another database. More functions will be added to this module. I will make it available to the community through CodePlex when is ready.
Here’s a preview snapshot of my new functions:
Special Thanks to the following bloggers for their contributions:
The first will script out your table(s) structure, adn the second one will build your “insert into… Select…” script so you can populate the data of the empty table(s). I included some documentation so is accessible using the get-help or help commands.
In the “Write-SQLCreateTable” function, I use some pre-defined scripting options but you may add/removed and only use the ones you need. Here’s the ones I use:
For now, you need to provide the table schema, and the name of the tables will rename the same. I will be updating this function to later allow to include a prefix to change the destination table name, and maybe include change the schema.
Keep in mind, both functions will display the results on screen. You can save the resutls to an PS Object and the use the “Invoke-SQLcmd” the “-query” parameter to execute the scripts.
I have to admit that having setup Office 365 was a breeze. Just make sure you have a reliable fast internet connection, and you are good to go. By the way… no servers equipment is needed.
Well, since I got Office 365 Beta setup in my computer I’ve been looking for information about how to use PowerShell. Now, thanks to one of my PowerShell MVP
college (Sean Kearney) mention about this information been well documented in
Office 365 Beta. I found it, and I couldn’t believe it was that SIMPLE!!! YES,
My computer environment is very basic. I have no Exchange, Linc, or Sharepoint. So now I’m part of the cloud experience with Office 365, and my answer of using PowerShell was at my fingertip.
If you want to try it, then check out the links below, and copy/paste the code. Make sure you meet all the requirements needed so you can experience using PowerShell with Office 365. By the way, It’s highly recommended by our PowerShell MVP’s colleges to set your Execution Policy to “ RemoteSigned” and not “unrestrincted”. Using “unrestricted” will still give you some security warning.
Office 365 – PowerShell – Resource Information Currently
available under: “Outlook Web App Help”
**You need to understand one thing, and quote this from the
help “… By default, all new user accounts are allowed to use Windows Remote Management (WinRM) to access the cloud-based organization with Windows PowerShell …”. But, the help documentation gives you the
code to prevent users to access remoting with Office 365. (read the help documentation)
By the way, there’s a recent change to the connection URI:
I love our PowerShell MVP Community. Here’s an observation from Aleksandar Nikolic: “… i think you should use generic Connectionuri(https://ps.outlook.com/powershell) and not specific server. you’ll be redirected … office365 will redirect you to closest, the least busy server …”. Thanks Alek.:)
This is totally true, and the only reason I included the change was to avoid the redirection warning message. So you can still use what’s documented:
##---- Getting Started with Office 365 PowerShell ----##
## - Get your Office 365 Credential
$LiveCred = Get-Credential
## - Prepare your Office 365 PS Session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
-ConnectionUri "https://pod51011psh.outlook.com/PowerShell-LiveID?PSVersion=2.0" `
-Credential $LiveCred -Authentication Basic -AllowRedirection
## - Load Office 365 PowerShell module into your current PS Console session.
$Office365PS = Import-PSSession $Session
## - Using the exported functions get a list of your mailboxes
## - Get command information, count and list names.
get-command -Module $Office365PS | more
Parking and admittance to this event is free. Lunch will provided. Please Register now. Let your friends and colleagues know about this event.
Join system administrators IT professionals and database professionals in addition to managers at all levels in Florida that work with Microsoft technologies for a “IT Camp Saturday”. IT Camps are a free, one day learning event for anyone seeking professional development. This event serves IT professionals and students with a focus on IT Pro related technologies. IT Camps offer a conference style learning environment free of charge to attendees and is open to presenters of
all backgrounds and expertise.
The call for speakers is open until 6/11. Topics can be anything applicable to System admins, and/or IT Pros including SharePoint, System Center, SQL Server, PowerShell, Active Directory, or Windows 2008 R2 and Windows 7.