netnerds.net » PowerShell

25Jan/092

PowerShell 1.0: Backup and Restore a Database Between Two SQL 2005 Servers

My technical partner-in-crime, Brandon, created this PowerShell script last year in an effort to help him understand PowerShell a bit more. He noted that while it may not all be best practices SQL-wise, he tried his best to use as much PowerShell as possible.

Backup and Restore



#Powershell to backup and restore a SQL database from server1 to server2.

#2008, Brandon Abshire / netnerds.net


#load assemblies

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null


# create source and destination sql servers

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server1'

$d = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server2'

# set backup directory, dbname and grab date

$bkdir = 'e:\'

$remoteDir = 'e$\'

$dbname = 'databasename'

$dt = get-date -format yyyyMMddHHmmss


#create backup object and set properties

$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')

$dbbk.Action = 0

$dbbk.BackupSetDescription = "Backup of " + $dbname

$dbbk.BackupSetName = $dbname + " Backup"

$dbbk.Database = $dbname

$dbbk.Devices.AddDevice($bkdir + "\" + $dbname + $dt + ".bak", 2)

$dbbk.MediaDescription = "Disk"

$dbbk.Incremental = $false


#Backup the database

$dbbk.SqlBackup($s)


#create restore object and set properties.

$dbres = New-Object ('Microsoft.SqlServer.Management.Smo.Restore')

$dbres.Database = $dbname

$dbres.Action = 0

$dbres.ReplaceDatabase = $true


#Add the backup that is on the remote server by whatever method you wish.

#You can copy it locally first.  I just used named pipes to grab it off the network, since it is a fast enough connection.


$dbres.Devices.AddDevice("\\" + $s + "\" + $remoteDir + $dbname + $dt + ".bak", 2)

$dbres.NoRecovery = $false


#Without the KillAll, my restore got stuck in "Restoring..." forever.

$d.KillAllProcesses('databasename')


#restore the database.

$dbres.SqlRestore($d)


#The database is now restored from one server to another.

#We must re-map the mappings on the database for my user, or login will fail.


#Set db to the specific database we want on the destination server.

$db = $d.databases["databasename"]


#Set newUser to the user we want to give permissions to and drop it.

#This user was restored from the other database and doesn't match up

#to the one on the destination SQL server.

#sp_change_users_login 'autofix','userlogin' could also be used but I

#wanted to practice PS moreso than SQL


$newuser = $db.users["username"]

$newuser.drop()


#Create a new user.  Set the parent to the database we restored.

$newuser = New-Object ('Microsoft.SqlServer.Management.Smo.User')

$newuser.parent = $db


#Set the username and login name.

$newuser.name = "My User"

$newuser.login = "username"


#Set the default Schema, create the user and add the appropriate roles.

$newuser.defaultSchema = "dbo"

$newuser.Create()

$newuser.AddToRole("db_datareader")

$newuser.AddToRole("db_datawriter")

Credit goes out to the Allen White, MVP of the MSDN Forums for a PowerShell/SQL quickstart.

Posted by: Chrissy Filed under: PowerShell, SQL Server 2 Comments

11Nov/073

PowerShell 2.0 CTP now Out!

Not even one-handed typing with a gimp shoulder in a rotator cuff cast can stop me from blogging this... PowerShell 2.0 CTP is now available!

I won't get to play for weeks, sadly. So...on an unrelated note; anyone know what's the best dictation software out there? Thanks.

Posted by: Chrissy Filed under: PowerShell 3 Comments

25Oct/075

PowerShell: Parse an RSS Feed and E-mail/Text Yourself the Results

I'm so excited -- my best friend and tech partner-in-crime, Brandon, recently picked up PowerShell after I gave him a copy of Wrox's Professional PowerShell. He totally loves it and has already created a few new PowerShell scripts and converted other ASP.NET/VBScript scripts. One of his favorite scripts checks the Woot.com RSS feed and e-mails him with the daily item. He reduced a 25 line VBScript to a 5 line PowerShell script which goes something like this:



$rssUrl = "http://www.woot.com/blog/rss.aspx"

$blog = [xml](new-object System.Net.WebClient).DownloadString($rssUrl)

$results = $blog.rss.channel.item[0].title


$smtpmail = [System.Net.Mail.SMTPClient]("smtp.san.rr.com")

$smtpmail.Send("me@netnerds.net", "xxxxxxxxxx@vtext.com ", "Woot Deal", $results)

Five silly lines! PowerShell is just so straightforward:

1) Assign URL to a variable
2) Create a new WebClient and pull the RSS Feed into it.
3) Assign the value of the first title element to a variable
4) Create a SMTP Client and assign it the value of the smtp host
5) Access the Send Method and pass it (From, To, Subject, Body)

He then used Task Scheduler (tasksch) to run the script each night.

Another script he wrote checks for backups older than seven days then deletes them. If you use this script, be sure to remove the -whatif if you are happy with the results.



foreach ($file in (get-childitem -Path C:\backups\))

{

  $curTime = [System.DateTime]::get_now()

  if ($file.LastWriteTime -lt $curTime.Add("-7"))

   {

    remove-item -recurse $file -whatif

   }

}

For the record, I have Professional Windows PowerShell (Wrox/Andrew Watt), Windows PowerShell Cookbook (O'reilly/Lee Holmes, MSFT) and PowerShell in Action (Manning/Bruce Payette, MSFT). I suggest reading all three of them, in that order. The Wrox and O'reilly books provide the "how" while Payette's book provides the "why?". All three are very well written, especially the Cookbook, for which I was a technical editor

Posted by: Chrissy Filed under: PowerShell 5 Comments

29Jul/070

PowerShell 1.0: Adding Virtual FTP Directories to IIS 6 or 7

While my firm explores using WebDAV and SharePoint 2007 for exchanging large amounts of files, we're temporarily using FTP dropboxes to fill the void. Last Monday, I setup 11 new accounts and it took a total of one hour to complete the same 15 step process (give or take) for each account. By the time I was finished, I decided automating FTP account creation would be my first PowerShell project. What you see below is part of that project.

The code below creates a virtual directory in the "Default FTP Site" of the machine that is running the PowerShell script. The virtual directory called "NewUser" is mapped to C:\FTP\NewUser and is set to be both readable and writable. For the record, I couldn't get WMI to work (get-wmiobject) and that's the reason I decided to use the .NET's Directory Services support.

$server = $env:computername

$service = New-Object System.DirectoryServices.DirectoryEntry("IIS://$server/MSFTPSVC")

$site = $service.psbase.children |Where-Object { $_.ServerComment -eq 'Default FTP Site' }

$site = New-Object System.DirectoryServices.DirectoryEntry($site.psbase.path+"/Root") # <-- IIS 6 requires this. Not sure why. Otherwise, it never appears to commit changes. This line is not required for IIS 7.

$virtualdir = $site.psbase.children.Add("NewUser","IIsFtpVirtualDir")

$virtualdir.psbase.CommitChanges()

$virtualdir.put("Path","C:\FTP\NewUser")

$virtualdir.put("AccessRead",$true)

$virtualdir.put("AccessWrite",$false)

$virtualdir.psbase.CommitChanges()

$service.psbase.refreshCache() # OPTIONAL

Alternatively, you could do go straight for the path if you know it (IIS 6 seems to like this):

$service = New-Object DirectoryServices.DirectoryEntry("IIS://localhost/MSFTPSVC/1/Root")

$virtualdir = $service.psbase.children.Add("NewUser", "IIsFtpVirtualDir")

$virtualdir.psbase.CommitChanges()

$virtualdir.put("Path","C:\FTP\NewUser")

$virtualdir.put("AccessRead",$true)

$virtualdir.put("AccessWrite",$false)

$virtualdir.psbase.CommitChanges()

If you would like to iterate through each of the virtual directories on your FTP server, you can use the following code:

$service = New-Object System.DirectoryServices.DirectoryEntry("IIS://$env:computername/MSFTPSVC")

$site = $service.psbase.children | Where-Object { $_.ServerComment -eq 'Default FTP Site' }

$virtualdirs = $site.psbase.children.Find("Root","IIsFtpVirtualDir").psbase.children

foreach ($virtualdir in $virtualdirs) {$virtualdir.psbase.name}

This code is likely applicable to many of the objects in the IIS ADSI provider. While I've only tested this on Vista (IIS 7), this should also work for Windows XP and 2003's IIS 6 as Vista uses IIS 6's MMC for management.

Also, if you are wondering how I know when to use psbase or psbase.children, I really don't. I just fumble around until I get it to work. The 4 lines above, specifically $virtualdirs = $site.psbase.children.Find("Root","IIsFtpVirtualDir").psbase.children took me about seven hours to figure out. I hear PowerShell 2.0 will have much better support for Directory Services and hopefully that will include support the IIS FTP service.

Posted by: Chrissy Filed under: IIS, PowerShell No Comments

28Jul/0710

PowerShell: Set-Acl Does Not Appear to Work

If you've ever dealt with NTFS permissions in VBScript, you will no doubt appreciate just how easy PowerShell now makes it to manage access control lists. Basic examples in PowerShell books and around the 'net look something like this:

$directory = "Test"

$acl = Get-Acl $directory

$accessrule = New-Object system.security.AccessControl.FileSystemAccessRule("IUSR_CRACKLIN", "Modify", "Allow")

$acl.AddAccessRule($accessrule)

set-acl -aclobject $acl $directory

In the example above, user "IUSR_CRACKLIN" is given Modify access to the Test directory. Running the code above will not produce any errors but upon checking permission via the GUI, it seems as though the user was added, but no permissions were set.

I thought that perhaps this was an issue with Vista and I tried it on Windows Server 2003. And that's when I noticed that the directory had been given "Special Permissions." When I checked the Advanced permissions, I could see that Modify access had been assigned, but only to "This Folder." Other folders that had the checkboxes checked listed "This Folder, subfolders and files"

Since I wanted the Test directory permissions to match the others, I searched the Google to see which flags would give me "This Folder, subfolders and files." I found Damir Dobric's blog post titled "Directory Security and Access Rules which sported a handy reference table flags that must be set to achieve various scenarios.

Subfolders and Files only	InheritanceFlags.ContainerInherit, InheritanceFlags.ObjectInherit, PropagationFlags.InheritOnly
This Folder, Subfolders and Files	InheritanceFlags.ContainerInherit, InheritanceFlags.ObjectInherit, PropagationFlags.None
This Folder, Subfolders and Files	InheritanceFlags.ContainerInherit, InheritanceFlags.ObjectInherit, PropagationFlags.NoPropagateInherit
This folder and subfolders	InheritanceFlags.ContainerInherit, PropagationFlags.None
Subfolders only	InheritanceFlags.ContainerInherit, PropagationFlags.InheritOnly
This folder and files	InheritanceFlags.ObjectInherit, PropagationFlags.None
This folder and files	InheritanceFlags.ObjectInherit, PropagationFlags.NoPropagateInherit

So it setting the following should give me what I need:
InheritanceFlags.ContainerInherit, InheritanceFlags.ObjectInherit and PropagationFlags.None.

$directory = "Test"

$inherit = [system.security.accesscontrol.InheritanceFlags]"ContainerInherit, ObjectInherit"

$propagation = [system.security.accesscontrol.PropagationFlags]"None"

$acl = Get-Acl $directory

$accessrule = New-Object system.security.AccessControl.FileSystemAccessRule("IUSR_CRACKLIN", "Modify", $inherit, $propagation, "Allow")

$acl.AddAccessRule($accessrule)

set-acl -aclobject $acl $directory

I then checked the permissions and voila:

Imagine that.. PowerShell can set any number of permissions with about 6 lines of code while VBScript requires over 36 lines JUST to set the constants needed for managing permissions. I'm so excited thinking about the possibilities: PowerShell + Windows Core + SSH is going to be awesome.

Posted by: Chrissy Filed under: PowerShell, Security 10 Comments

26Jul/075

Powershell: Working with Passwords

When creating a new Active Directory user from the command line in PowerShell, you will likely find yourself using Read-Hosts's asSecureString switch when entering the password.

$password = Read-Host "Enter password" -AsSecureString

Next, you'll probably look around the Internets for a few hours or so trying to figure out how to change the password of the newly created user. You will soon discover that the user creation process in PowerShell 1.0 isn't very straightfoward and it even requires a specific order for proper account creation. First, you create the account, then you set some basic properties, next you call SetInfo(), and finally you invoke setPassword using the follwing syntax:

$newUser.psbase.Invoke("SetPassword",$password)

Now you may find yourself with the following exception: Exception calling "Invoke" with "2" argument(s): "Exception has been thrown by the target of an invocation." Originally, this post mentioned using toString() to address the problem but PowerShell team member Lee Holmes wrote to let me know that the password was changed literally to System.Security.SecureString. He also said that "there is no really easy way to convert a secure string to plain text - on purpose. Since a SecureString is supposed to prevent plain text from littering your computer's memory, converting it to plain text defeats the purpose."

My primary reason for using asSecureString is to encode the string into asterisks when typing it at the prompt. So Lee gave me two ways to convert the password to be used while invoking SetPassword. Note that unless you are using a secure LDAP channel, the password will be sent over the network in clear text.

$temporaryCredential = New-Object System.Management.Automation.PsCredential "None",$password

$newUser.psbase.Invoke("SetPassword",$temporaryCredential.GetNetworkCredential().Password)

Or, alternatively:

$temporaryCredential = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))$newUser.psbase.Invoke("SetPassword",$temporaryCredential)

If you continue to see this exception, check to make sure that the password you entered meets your domain's password complexity requirements.

Posted by: Chrissy Filed under: Active Directory, PowerShell, Security 5 Comments

26Jul/070

PowerShell 1.0: Find the Fully Qualified Domain Name of Current Active Directory Domain

So I'm making the move to PowerShell. It's painful learning such alien (to me) concepts but books like Lee Holmes' PowerShell: The Definitive Guide help a ton. I was fortunate enough to be the editor for Chapters 1-5 and got a sneak preview. It's a fantastic book and can't wait to receive the title, complete with indexes! For now, I'm searching both the 36 Word documents and the sample code for solutions using Vista's built-in search functions.

My first task, which I'll explain in later posts, includes some AD stuff. One tiny part of the equation is dynamically finding the FQDN of the current Active Directory domain. This should be easy! Using RootDSE, it's super easy to find out the DistinguishedName or even the FQDN of the domain controller being queried, so wouldn't there be a similar entry for FQDN of the whole domain? Apparently not (or if it's there, I can't find it). I've spent the morning and part of last night digging through LDAP filters, looking on Google for examples of objectcategory=crossref, dnsroot, dnshostname, etc. But after finding this useful codeplex page, I played around with GetCurrentDomain() and realized that finding the DNS hostname for an AD domain all boils down to this one line:

$strDomainDNS = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name

Or this one liner, as Lee just suggested to me

$strDomainDNS = $env:USERDNSDOMAIN

Ahh! So it was that easy. Why, then, did it take 8 hours to find that? Now I wonder how to get the FQDN of any domain. Being a newb is hard work.

If you're wondering what dnshostname is for, it's to resolve the local machine you are working with. Michael at brnets.com provides the following example embedded in a big ol Exchange script:

$rootDSE = [adsi]"LDAP://RootDSE"

$DCDNShostname = $rootDSE.dnsHostName

$DCDNShostname

Oh, as a bonus, here's some another query you may find helpful. I started with benp's script then made it a little skinnier (and more prone to errors! )

Search for Active Directory User Object in the Current Domain

$domain = New-Object DirectoryServices.DirectoryEntry

$search = [System.DirectoryServices.DirectorySearcher]$domain

$search.Filter = "(&(objectClass=user)(sAMAccountname=Chrissy))"

$user = $search.FindOne().GetDirectoryEntry()

$user.Name

My original code made reference to GetDirectoryEntry() but John Brennan suggested looking for alternative approaches. Thanks for the tip, John!

Posted by: Chrissy Filed under: Active Directory, PowerShell No Comments