Restore SQL DBs Using PowerShell

Of course the next logical thing after getting your backup script working is to create your restore script...  I used Donabel Santos's script from http://www.sswug.org/articles/viewarticle.aspx?id=44909 as a reference.

$restoreDir = "c:\shared\Temp\" # last slash very important!

if ((test-path $restoreDir) -eq $false ) # Verify folder exists
  {
     $a = Read-Host("Path Not Found!")
     Exit -1 
  }

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$files = get-childitem $restoreDir -recurse
foreach ($file in $files)
  {
    $backupFile = $restoreDir + $file
    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
    $backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
    $dbRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $dbRestore.NoRecovery = $false;
    $dbRestore.ReplaceDatabase = $true;
    $dbRestore.Action = "Database"
    $dbRestore.Devices.Add($backupDevice)
    $dbRestoreDetails = $dbRestore.ReadBackupHeader($server)
    "Restoring Database: " + $dbRestoreDetails.Rows[0]["DatabaseName"]
    $dbRestore.Database = $dbRestoreDetails.Rows[0]["DatabaseName"]
    $dbRestore.SqlRestore($server)
  }

Get First and Last Day of Current Month in SQL

I have been spending most of the last couple of day hammering out reports in SSRS.  I needed to get information for the current month, but needed to know the first and last dates to set my query.  I do not want to run into any kind of Azure Date issues (http://www.wired.com/wiredenterprise/2012/03/azure-leap-year-bug/)

declare @reportDate datetime 
declare @lastDate datetime 
set @reportDate = GETDATE() 
Set @reportDate = DateAdd(Day, 1, @reportDate - Day(@reportDate) + 1) -1 
Set @lastDate = DateAdd(Month, 1, @reportDate - Day(@reportDate) + 1) -1 
select @reportDate, @lastDate

 Update (05/01/2012):
I was not very happy with the above query, so I have updated it...  I have also added the functionality to set the time back to midnight...

declare @first datetime
declare @last datetime
set @first = dateadd(day, 1, getdate() - day(getdate()))
set @last = dateadd(day, -1, dateadd(month, 1, @first))
select @first, @last
set @first = DATEADD(dd, DATEDIFF(dd, 0, @first), 0)
set @last = DATEADD(dd, DATEDIFF(dd, 0, @last), 0)
select @first, @last

Backing Up SQL DBs Using PowerShell

My next project requires that I create several SQL Mirrors, and instead of backing up my databases with SQL, I thought I would try it in PowerShell.  The majority of the script is from Edwin Sarmiento's blog http://www.mssqltips.com/sqlservertip/1862/backup-sql-server-databases-with-a-windows-powershell-script/  (Excellent Post)

$bkdir = "\\serverName\Shared\Temp" # Set Backup Path! (optional "C:\Temp")

if ((test-path $bkdir) -eq $false ) # Verify folder else create it...
  {
     [IO.Directory]::CreateDirectory($bkdir) 
  }

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance

$dbs = $s.Databases
foreach ($db in $dbs) 
  {
     if(($db.Name -ne "tempdb") -and ($db.Name -ne "master") -and ($db.Name -ne "model") -and ($db.Name -ne "msdb")) 
          {
               $dbname = $db.Name
               $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
               $dbBackup.Action = "Database"
               $dbBackup.Database = $dbname
               $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + ".bak", "File")
               $dbBackup.SqlBackup($s)
               write-host($db.name + " has been backed up.")
          }
  }

If you are saving to a network location, the SQL SA account and the person running the script need to have read/write permissions to the location.

Update: (03/05/2012)
Added save location verification else create folder.

Get The DBO From All SQL Databases

I have finally decided to start keeping track of the useful SQL commands that I have used.  Mostly because I am tired of rewriting them.  Also, if I have had to use them, then I sure that someone else (or me again) might find them useful.
While moving databases around within SQL to optimize IOPS and/or drive utilization, you might have a need to put the Database Owner back to what it was originally. Before you drop your databases, take a look at the DBO first.
This will grab all the dbo's of all the databases on your server:

select SUSER_SNAME(owner_sid) as username, name from sys.databases

Now, if you want to change the DBO...

sp_changeDbOwner @loginame = 'domain\username'

However, you might run into an error is the DBO is already a user or aliased in the database.

To fix this problem, run the following:

USE <databaseName>
GO
SP_DROPUSER 'domain\username'
GO
SP_CHANGEDBOWNER 'domain\username'

UPDATE 02/04/2015
Added drop user and change owner code.

Add SharePoint Snap-In to PowerShell ISE

Let me start off with saying that I, in no way, came up with this solution.  It was first shown to me by Shannon Bray (http://shannonbray.wordpress.com) when he and Gary Lapointe were maintaining spPowerShell.com.
Since the site is no longer available, I have had to grab the following information from Kirk Evens (Add Microsoft.SharePoint.PowerShell Snap-In to All PowerShell Windows) and from Spence Harbar (Adding SharePoint 2010 PoweShell cmdlets to your PowerShell ISE).

Open up Windows PowerShell ISE (Run as Administrator), and run the following script:

# set the execution policy to run scripts
set-executionpolicy unrestricted -force

# Create the profile
if (!(test-path $profile.AllUsersAllHosts)) {new-item -type file -path $profile.AllUsersAllHosts –force}
psEdit $profile.AllUsersAllHosts

In the new Tab,
For SharePoint 2010 add the following:

cd 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\POWERSHELL\Registration'
.\SharePoint.ps1
cd \ 

For SharePoint 2013 add the following:

cd 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\CONFIG\POWERSHELL\Registration'
.\SharePoint.ps1
cd \ 

Save the Profile tab (no, you do not run the script)

Again, thanks to Shannon, Kirk, Gary, and Spence for their posts!

UPDATE (01/16/2012)
After speaking with Shannon, he has finally moved over the blog...  you can find his ISE blog here...
http://shannonbray.wordpress.com/2010/06/23/sharepoint-and-powershell-ise/
UPDATE (10/21/2012)
Added the section for SharePoint 2013...

SPSDenver: Using REST to Create A No Code Web Part

The actual file is located here:
https://skydrive.live.com/P.mvc#!/?cid=8e55aa8c038225f8&sc=documents&id=8E55AA8C038225F8%21286

SPCTCDC 2011 Codeless SQL Integration Presentation

The actual file is located here:
https://skydrive.live.com/redir.aspx?cid=8e55aa8c038225f8&resid=8E55AA8C038225F8!149

SPCTCDC 2011 Weather RSS Presentation

The actual file is located here:
https://skydrive.live.com/redir.aspx?cid=8e55aa8c038225f8&resid=8E55AA8C038225F8!149

Force .eml Files to Open in Outlook 2007

Background

Files being collected in SharePoint email enabled lists are being received as .eml files by default since SharePoint uses SMTP services for receiving email.  The problem is that people want to use Outlook, not Outlook Exprerss to view their emails, and .eml files are not native to Outlook 2007 or earlier.

Workaround

1)      Modify the client registry:

a.     Make a backup of the following eml-file registration:

                                                               i.       HKEY_CLASSES_ROOT\.eml

b.      Install appropriate eml-Outlook2007-xxx.reg file by double clicking the file.

c.       Information taken from http://www.msoutlook.info/question/354

2)      Set the .eml file default to open in Outlook 2007

a.       Right click a .eml file

b.      Open With à Choose default program…

c.       Choose Outlook.exe

                                                               i.      C:\\Program Files\Microsoft Office\Office12\Outlook.exe

3)      Modify the client registry again:

a.     Make a backup of the following registration:

                                                               i.       HKEY_CLASSES_ROOT\MIME

b.      Modify “HKEY_CLASSES_ROOT\MIME\Database\Content Type\message/rfc822”
extension=".eml"
CLSID=""

c.       Information take from:
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/d94c0d4e-0d32-4648-bdd6-dc3f28bb4797/

Creating a Central Admin Desktop Shortcut Using PowerShell

While working on a deployment script, I thought it would be nice to add the Central Admin shortcut to the desktop of All Users.  You can get a lot of information from http://ss64.com/vb/shortcut.html concerning creating shortcuts, but if you want to add the shortcut for All Users:

# Add Central Admin Shortcut to All Desktops
$wshshell = New-Object -ComObject WScript.Shell
$desktop =  $wshShell.SpecialFolders.Item("AllUsersDesktop")
$lnk = $wshshell.CreateShortcut($desktop + "\SharePoint 2010 Central Administration.lnk")
$lnk.TargetPath = "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN\psconfigui.exe"
$lnk.Arguments = "-cmd showcentraladmin"
$lnk.Description = "Views the Central Administration Web Application."
$lnk.IconLocation = "%SystemRoot%\Installer\{90140000-1014-0000-1000-0000000FF1CE}\shcentadm.exe"
$lnk.Save()