Mar 262013
 

Database mirroring has been around since SQL Server 2005, and it’s turned out to be an excellent step up from log shipping.  However, like log shipping, it is still only a database-level disaster recovery solution.  Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server.  This is where the DBA needs to plan ahead and create their own custom jobs to script and/or document these types of shortcomings.

My solution is to use Powershell.  In this example, I have setup database mirroring for the AdventureWorks2012 database.  For this demo, both instances, TEST1 and TEST2, are on the same physical server.
There are two logins on the principal server that currently do not exist on the mirror server.  One is a SQL login, AWLogin1, and the other is a Windows Authenticated login, TRON2AWLogin2.
The first step of our Powershell script will need to connect to the principal server to generate a CREATE LOGIN script for those two logins.  To generate the script, we need to grab the login name, the SID, and the hashed password if it’s a SQL login.  This is accomplished by running the following code.

SELECT ‘USE master; CREATE LOGIN ‘ + QUOTENAME(p.name) + ‘ ‘ +
CASE WHEN p.type in (‘U’,‘G’)
    THEN ‘FROM WINDOWS ‘
    ELSE
    END
+ ‘WITH ‘ +
CASE WHEN p.type = ‘S’
    THEN ‘PASSWORD = ‘ + master.sys.fn_varbintohexstr(l.password_hash) + ‘ HASHED, ‘ + ‘SID = ‘ + master.sys.fn_varbintohexstr(l.sid) +  ‘, CHECK_EXPIRATION = ‘ +
    CASE WHEN l.is_expiration_checked >0
        THEN ‘ON, ‘
        ELSE ‘OFF, ‘
        END
    + ‘CHECK_POLICY = ‘ +
    CASE WHEN l.is_policy_checked >0
        THEN ‘ON, ‘
        ELSE ‘OFF, ‘
        END +
    CASE WHEN l.credential_id > 0
        THEN ‘CREDENTIAL = ‘ + c.name + ‘, ‘
        ELSE
        END
ELSE
END
+ ‘DEFAULT_DATABASE = ‘ + p.default_database_name+
CASE WHEN LEN(p.default_language_name)> 0
    THEN ‘, DEFAULT_LANGUAGE = ‘+ p.default_language_name
    ELSE
    END
+ ‘;’ AS ‘LoginScript’
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
    ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
    ON l.credential_id = c.credential_id
WHERE p.type IN (‘S’,‘U’,‘G’)
    AND p.name NOT IN (‘sa’, ‘NT AUTHORITYSYSTEM’)
    AND p.name NOT LIKE ‘##%##’
    AND p.name NOT LIKE ‘BUILTIN%’
    AND p.name NOT LIKE ‘NT SERVICE%’
ORDER BY p.name;
In this example, you can see we have one row for each of the two logins.

The next step of the Powershell script will need to write those two rows of data to a file on the mirror server.  This is done using the System.IO.StreamWriterclass.

foreach($row in $commandList.Tables[0].Rows)
{
    try
    {
            $output = $row[“LoginScript”].ToString()
            $stream.WriteLine($output)
      }
      catch
      {
            $stream.Close()
            CheckForErrors
      }
}
When there is a need to failover to the mirror server, the DBA can then open this script and run it.  All logins will be created and with their original SID value and password.
The second half of the Powershell script will use the same procedures to script out any server role memberships or server-level permissions these two logins may have on the principal server.  This is done using the following block of code.

— BUILD SERVER ROLE MEMBERSHIPS
SELECT ‘USE master; EXEC sp_addsrvrolemember @loginame = ‘+QUOTENAME(s.name)+‘, @rolename = ‘+QUOTENAME(s2.name)+‘;’  AS ‘ServerPermission’
FROM master.sys.server_role_membersr INNER JOIN master.sys.server_principals s
    ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2
    ON s2.principal_id = r.role_principal_id
WHERE s2.type = ‘R’
    AND s.is_disabled = 0
    AND s.name NOT IN (‘sa’,‘NT AUTHORITYSYSTEM’)
    AND s.name NOT LIKE ‘##%##’
    AND s.name NOT LIKE ‘NT SERVICE%’
UNION ALL
— BUILD SERVER-LEVEL PERMISSIONS
SELECT ‘USE master; ‘+sp.state_desc+‘ ‘+sp.permission_name+‘ TO ‘+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+‘;’  AS ‘ServerPermission’
FROM sys.server_permissions sp JOIN sys.server_principals s
    ON sp.grantee_principal_id= s.principal_id
WHERE s.type IN (‘S’,‘G’,‘U’)
    AND sp.type NOT IN (‘CO’,‘COSQ’)
    AND s.is_disabled = 0
    AND s.name NOT IN (‘sa’,‘NT AUTHORITYSYSTEM’)
    AND s.name NOT LIKE ‘##%##’
    AND s.name NOT LIKE ‘NT SERVICE%’;
From the output, you can see the TRONAWLogin2 is a member of the BULKADMIN server role and has the VIEW SERVER STATE permission.  These two rows will be written to a file in the same file share as the previous file.  
As before, once the database is failed over to the mirror server, the DBA can run this script to apply any missing permissions.
Finally, this Powershell script can be scheduled to run from any server; however, I choose to setup this job on the principal server.  I schedule it to run once a day through SQL Agent.  Each run of the script will overwrite the existing file, so if there are any logins or permissions that have been added or removed, it will show up in the latest version of the files.

Using this Powershell script can make it very easy to script out logins and permissions.  While this example was used with database mirroring, then same strategy will work for log shipping.  The entire Powershell script is below.

########################################################################################### 
#
#   File Name:    CopyLoginsPermissions.ps1
#
#   Applies to:   SQL Server 2008
#                 SQL Server 2008 R2
#                 SQL Server 2012
#
#   Purpose:      To copy logins and permissions from a PRIMARY server
#                 to it's corresponding MIRROR server.
#
#   Prerequisite: Powershell v2.0 must be installed.
#                 SQL Server components must be installed.
#
#   Parameters:   [string]$sourceInstance - Production SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$destinationPath - BCP backup share (UNC Path Ex: \\SERVER\backup$)
#
#   Author:       Patrick Keisler
#
#   Version:      1.0.0
#
#   Date:         03/24/2013
#
#   Help:         http://www.patrickkeisler.com/
#
###########################################################################################

#Enable Debug Messages
#$DebugPreference = "Continue"

#Disable Debug Messages
$DebugPreference = "SilentlyContinue"

#Terminate Code on All Errors
$ErrorActionPreference = "Stop"

#Clear screen
CLEAR

#Load Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

function CheckForErrors {
    $errorsReported = $False
    if($Error.Count -ne 0)
    {
  Write-Host
  Write-Host "******************************"
        Write-Host "Errors:" $Error.Count
        Write-Host "******************************"
        foreach($err in $Error)
        {
            $errorsReported  = $True
            if( $err.Exception.InnerException -ne $null)
            {
                Write-Host $err.Exception.InnerException.ToString()
            }
            else
            {
                Write-Host $err.Exception.ToString()
            }
            Write-Host
        }
        throw;
    }
}
function GetServer {
    Param([string]$serverInstance)

    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($serverInstance)
    $server.ConnectionContext.ApplicationName = "Copy Logins and Permissions"
 $server.ConnectionContext.ConnectTimeout = 5
    $server;
}
function GetServerLogins {
 Param([string]$serverInstance, [string]$destinationPath)
 
 $array = $serverInstance.Split("\")
 if($array.Length -eq 1)
 {
  $machineName = $array[0]
  $instanceName = "DEFAULT"
 }
 else
 {
  $machineName = $array[0]
  $instanceName = $array[1]
 }
 
 $fileName = $destinationPath + "\" + $machineName + "_" + $instanceName + "_ServerLogins.sql"
 $stream = New-Object System.IO.StreamWriter($fileName)

 $server = GetServer($serverInstance)
 $db = $server.Databases["master"]
 $commandList = $db.ExecuteWithResults(
 @"
SELECT 'CREATE LOGIN ' + QUOTENAME(p.name) + ' ' +
CASE WHEN p.type in ('U','G')
        THEN 'FROM WINDOWS '
        ELSE ''
        END
+ 'WITH ' +
CASE WHEN p.type = 'S'
        THEN 'PASSWORD = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' HASHED, ' + 'SID = ' + master.sys.fn_varbintohexstr(l.sid) +  ', CHECK_EXPIRATION = ' +
        CASE WHEN l.is_expiration_checked > 0
                THEN 'ON, '
                ELSE 'OFF, '
                END
        + 'CHECK_POLICY = ' +
        CASE WHEN l.is_policy_checked > 0
                THEN 'ON, '
                ELSE 'OFF, '
                END +
        CASE WHEN l.credential_id > 0
                THEN 'CREDENTIAL = ' + c.name + ', '
                ELSE ''
                END
ELSE ''
END
+ 'DEFAULT_DATABASE = ' + p.default_database_name +
CASE WHEN LEN(p.default_language_name) > 0
        THEN ', DEFAULT_LANGUAGE = ' + p.default_language_name
        ELSE ''
        END
+ ';' AS 'LoginScript'
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
 ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
 ON l.credential_id = c.credential_id
WHERE p.type IN ('S','U','G')
 AND p.name NOT IN ('sa','NT AUTHORITY\SYSTEM')
 AND p.name NOT LIKE '##%##'
 AND p.name NOT LIKE 'BUILTIN\%'
 AND p.name NOT LIKE 'NT SERVICE\%'
ORDER BY p.name;
"@
 )
 
 Write-Host "Writing to server logins file..." -NoNewline
 $stream.WriteLine("/* This file should not be blank!!!*/")
 $stream.WriteLine("/* These commands were generated from: $serverInstance */")

 if ($commandList.Tables[0].Rows.Count -ge 1)
 {
  foreach($row in $commandList.Tables[0].Rows)
  {
      try
      {
    $debug = "Script: " + $row["LoginScript"]
    Write-Debug $debug
    $output = $row["LoginScript"].ToString()
    $stream.WriteLine($output)
   }
   catch
   {
    $stream.Close()
    CheckForErrors
   }
  }
 }
 $stream.close()
 Write-Host "OK"
}

function GetServerPermissions {
 Param([string]$serverInstance, [string]$destinationPath)
 
 $array = $serverInstance.Split("\")
 if($array.Length -eq 1)
 {
  $machineName = $array[0]
  $instanceName = "DEFAULT"
 }
 else
 {
  $machineName = $array[0]
  $instanceName = $array[1]
 }
 
 $fileName = $destinationPath + "\" + $machineName + "_" + $instanceName + "_ServerPermissions.sql"
 $stream = New-Object System.IO.StreamWriter($fileName)

 $server = GetServer($serverInstance)
 $db = $server.Databases["master"]
 $commandList = $db.ExecuteWithResults(
 @"
-- BUILD SERVER ROLE MEMBERSHIPS
SELECT 'USE master; EXEC sp_addsrvrolemember @loginame = '+QUOTENAME(s.name)+', @rolename = '+QUOTENAME(s2.name)+';'  AS 'ServerPermission'
FROM master.sys.server_role_members r INNER JOIN master.sys.server_principals s 
 ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2 
 ON s2.principal_id = r.role_principal_id 
WHERE s2.type = 'R' 
 AND s.is_disabled = 0 
 AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM') 
 AND s.name NOT LIKE '##%##' 
 AND s.name NOT LIKE 'NT SERVICE\%' 
UNION ALL
-- BUILD SERVER-LEVEL PERMISSIONS
SELECT 'USE master; '+sp.state_desc+' '+sp.permission_name+' TO '+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+';'  AS 'ServerPermission' 
FROM sys.server_permissions sp JOIN sys.server_principals s 
 ON sp.grantee_principal_id = s.principal_id 
WHERE s.type IN ('S','G','U') 
 AND sp.type NOT IN ('CO','COSQ') 
 AND s.is_disabled = 0 
 AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM') 
 AND s.name NOT LIKE '##%##' 
 AND s.name NOT LIKE 'NT SERVICE\%';
"@
 )
 
 Write-Host "Writing to server permissions file..." -NoNewline
 $stream.WriteLine("/* If this file is blank, then no server-level permissions (i.e Server Role Memberships) existed on the production server. */")
 $stream.WriteLine("/* These commands were generated from: $serverInstance */")
 
 if ($commandList.Tables[0].Rows.Count -ge 1)
 {
  foreach($row in $commandList.Tables[0].Rows)
  {
      try
      {
    $debug = "Script: " + $row["ServerPermission"]
    Write-Debug $debug
    $output = $row["ServerPermission"].ToString()
    $stream.WriteLine($output)
   }
   catch
   {
    $stream.Close()
    CheckForErrors
   }
  }
 }
 $stream.close()
 Write-Host "OK"
}

function PerformValidation {
    Param($sourceInstance, $destinationPath)
 
 Write-Host "Validating parameters..." -NoNewline
    if([String]::IsNullOrEmpty($sourceInstance))
    {
  Write-Host "ERROR"
     $errorMessage = "Source server name is not valid."
        throw $errorMessage
    }
    if([String]::IsNullOrEmpty($destinationPath))
    {
  Write-Host "ERROR"
        $errorMessage = "Destination path name is not valid."
     throw $errorMessage
    }
 else
    {
        if(-not($destinationPath.StartsWith("\\")))
        {
   Write-Host "ERROR"
            $errorMessage = "Destination path is not valid: " + $destinationPath
            throw $errorMessage
        }
    }
    
 Write-Host "OK"
 
 Write-Host "Verifying source SQL Server connectivity..." -NoNewline
 $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sourceInstance)
    $conn.ApplicationName = "Copy Logins and Permissions"
 $conn.NonPooledConnection = $true
 $conn.ConnectTimeout = 5
 try
 {
  $conn.Connect()
        $conn.Disconnect()
 }
 catch
 {
  CheckForErrors
 }
    Write-Host "OK"


    Write-Host "Verifying destination file share exists..." -NoNewline
    if((Test-Path -Path $destinationPath) -ne $True)
    {
  Write-Host "ERROR"
        $errorMessage = "File share: " + $destinationPath + " does not exists"
        throw $errorMessage
    }
    Write-Host "OK"
}

function Main{
 
 Param([string]$sourceInstance, [string]$destinationPath)
 
 $Error.Clear()

    Write-Host
    Write-Host "============================================================="
    Write-Host " 1: Perform Initial Checks & Validate Input Parameters"
    Write-Host "============================================================="
 
 PerformValidation $sourceInstance $destinationPath
 
    Write-Host
    Write-Host "============================================================="
    Write-Host " 3: Create Server Logins Script"
    Write-Host "============================================================="

 GetServerLogins $sourceInstance $destinationPath

    Write-Host
    Write-Host "============================================================="
    Write-Host " 4: Create Server Permissions Script"
    Write-Host "============================================================="

 GetServerPermissions $sourceInstance $destinationPath

    Write-Host
    Write-Host "============================================================="
    Write-Host "  Copy Logins and Permissions to DR Completed Successfully   "
    Write-Host "============================================================="
}

#Capture inputs from the command line.
$sourceInstance = $args[0]
$destinationPath = $args[1]

#Hard-coded values used only for development
#$sourceInstance = "TRON\TEST1"
#$destinationPath = "\\TRON\BACKUP1"

#Prompt for inputs for an interactive script
#$sourceInstance = $(Read-Host "Source SQL Server name (Ex: server\instance)")
#$destinationPath = $(Read-Host "Destination share to copy backup file to (UNC Path Ex: \\SERVER\backup$)")

$debug = "Source Instance Parameter: " + $sourceInstance
Write-Debug $debug
$debug = "Destination Path Parameter: " + $destinationPath
Write-Debug $debug

Main $sourceInstance $destinationPath
Feb 122013
 

T-SQL Tuesday – This month’s party is hosted by Wayne Sheffield (blog|twitter), and the topic is about Powershell and how to use it for anything SQL Server.

With that challenge, I’d like to share a script I’ve written that takes a backup file from one server, copies to another server, and and then restores it.  That may sound pretty easy, but I’ve added in a few requirements to the restore.




Here’s the scenario:
We have two SQL Servers, one production (TRON2R2PROD) and one test (TRON3R2TEST), and we have one user database (AdventureWorks2008R2) on each of the production and test servers.  The test server is used by a developer.  The developer send us a request to “refresh the development database with a copy of production“.  This translates into: he needs the most recent backup of that production database copied from the production server over to the test server, then restored to it by overwriting the existing database, all while preserving his existing dbo level permissions.
The manual approach to completing this task.
  1. Figure out which full database backup file is the most recent for AdventureWorks2008R2.
  2. Copy the file from TRON2 to TRON3.
  3. On TRON3R2TEST, script out all existing user permissions for the AdventureWorks2008R2 database.
  4. Restore the backup.
  5. Run the script from step 3 to reapply the developers permissions.
  6. Delete the backup file from TRON3.

Total time to execute this task manually: ~ 10 minutes.

That many not seem like much time out of your entire workday, but what if that same developer wants you to complete this task each morning at 8AM.  Now you’re up to 10 minutes per day.  And what if he asked you to do it several times a day, every day of the week.  That 10 minutes can really add up.
The Powershell approach to completing this task.
  1. Run the AutoDatabaseRefresh.ps1 script.
Total time to execute this task using Powershell: < 30 seconds.

How’s that for performance improvement?

The great thing about Powershell is that it allows you to connect to different systems, such as Windows and SQL Server, all from a single programming language.  The entire script is written using the SQL Management Objects (SMO).  It does not use any of the SQL Server cmdlets, so there are no modules to import.  Let’s take a closer look.
For this script you need to pass 6 parameters to this script.
  1. $sourceInstance – Source SQL Server name
    • Example: “TRON2R2PROD
  2. $sourceDbName – Source database
    • Example: “AdventureWorks2008R2
  3. $sourcePath – Source share where the file exists (UNC Path)
    • Example: “\TRON2BACKUPR2PRODAdventureWorks2008R2
  4. $destinationInstance – Destination SQL Server name
    • Example: “TRON3R2TEST
  5. $destinationDbName – Database to be refreshed on destination server
    • Example: “AdventureWorks2008R2
  6. $destinationPath – Destination share to copy backup file to (UNC Path)
    • Example: “\TRON3BACKUP
The script needs to know both the source and destination SQL Servers (#1 and #4), and the source and destination database names (#2 and #5).  The other two parameters are the source paths (#3 and #6) and they must be UNC file shares.  This is so the Powershell script can be executed from any server or from any DBA’s workstation.
The basic workflow of the Powershell script is as follows:
Step 1:  Validate the input parameters.  All connectivity to the SQL Servers and to the file shares use Windows Authentication.  Tests for blank parameters.  Tests the connectivity to each SQL Server. Test that each file share exists.  If any of these validation tests fail, the script will halt.
if([String]::IsNullOrEmpty($sourceInstance))
{
    Write-Host “ERROR”
    $errorMessage = “Source server name is not valid.”
    throw $errorMessage

}

Step 2:  Connect to $sourceInstance to get the name of the most recent backup file for $sourceDbName.  This is accomplished by running this TSQL script.
$server = GetServer($serverInstance)
$db = $server.Databases[“msdb”]
$fileList = $db.ExecuteWithResults(
    @”
DECLARE
     @BackupId int
    ,@DatabaseName nvarchar(255);
SET @DatabaseName = ‘$sourceDbName’;
— Get the most recent full backup for this database
SELECT TOP 1
     @DatabaseName ASDatabaseName
    ,m.physical_device_name
    ,RIGHT(m.physical_device_name,CHARINDEX(,REVERSE(physical_device_name),1) 1) AS ‘FileName’
    ,b.backup_finish_date
    ,b.type AS ‘BackupType’
FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE b.database_name =@DatabaseName
    AND b.type = ‘D’
    AND b.is_snapshot = 0
    AND b.is_copy_only = 0
    AND b.backup_finish_date ISNOT NULL
ORDER BY b.database_backup_lsn DESC;
    “@
This give us the following output.

Step 3:  Copy the file from $sourcePath to $destinationPath.  From the output above, the physical file, AdventureWorks2008R2_db_201302060836.BAK, is located in D:BackupR2PRODAdventureWorks2008R2, so the $sourcePath must match this location.  Our UNC path is \TRON2BACKUPR2PRODAdventureWorks2008R2.  This step uses the Copy-Item cmdlet.  In my testing I have seen this cmdlet outperform the regular Windows copy and even Robocopy.
$source = $sourcePath + “” + $backupFile
Write-Host “Copying file…”
copy-item $source -destination $destinationpPath
Step 4:  Connect to $destinationInstance and script out all user-level permissions and database roles for the $destinationDbName.  The is accomplished by using the following script.
$server = GetServer($serverInstance)
$db = $server.Databases[“$destinationDbName”]
if(-not $db)
{
    Write-Host “Database does not exist on: $serverInstance”
}
else
{
    Write-Host “Saving permissions on $destinationDbName…” -NoNewline
    $commandList = $db.ExecuteWithResults(
        @”
IF OBJECT_ID(‘tempdb..#Commands’) IS NOT NULL
    DROP TABLE #Commands;
CREATE TABLE #Commands(
     RowId int identity(1,1)
    ,Cmd varchar(2000));
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’+QUOTENAME(d.name,CHAR(39))+‘) ALTER USER ‘ + QUOTENAME(d.name) + ‘ WITH LOGIN = ‘ + QUOTENAME(s.name) + ‘;’
FROM[$destinationDbName].sys.database_principalsd LEFT OUTER JOIN master.sys.server_principals s
    ON d.sid = s.sid
WHERE s.name IS NOT NULL
    AND d.type = ‘S’
    AND d.name <> ‘dbo’;
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’+QUOTENAME(d.name,CHAR(39))+‘) CREATE USER ‘ + QUOTENAME(d.name) + ‘ FOR LOGIN ‘ + QUOTENAME(s.name) + ‘ WITH DEFAULT_SCHEMA = ‘+ QUOTENAME(d.default_schema_name) + ‘;’
FROM[$destinationDbName].sys.database_principalsd LEFT OUTER JOIN master.sys.server_principals s
    ON d.sid = s.sid
WHERE s.name IS NOT NULL
    AND d.type = ‘S’
    AND d.name <> ‘dbo’;
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’+QUOTENAME(d.name,CHAR(39))+‘) CREATE USER ‘ + QUOTENAME(d.name) + ‘ FOR LOGIN ‘ + QUOTENAME(s.name) + ‘;’
FROM[$destinationDbName].sys.database_principalsd LEFT OUTER JOIN master.sys.server_principals s
    ON d.sid = s.sid
WHERE s.name IS NOT NULL
    AND d.type IN (‘U’,‘G’);
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’+QUOTENAME(p.name,CHAR(39))+‘) CREATE ROLE ‘ + QUOTENAME(p.name) + ‘ AUTHORIZATION ‘+QUOTENAME(o.name)+‘;’
FROM[$destinationDbName].sys.database_principalsp JOIN [$destinationDbName].sys.database_principalso
    ON o.principal_id = p.owning_principal_id
WHERE p.type = ‘R’
    AND p.is_fixed_role = 0
    AND p.principal_id <>0;
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];EXEC sp_addrolemember N’ + QUOTENAME(d.name,””) + ‘, N’ + QUOTENAME(m.name,CHAR(39)) + ‘;’
FROM[$destinationDbName].sys.database_role_membersr JOIN [$destinationDbName].sys.database_principalsd
    ON r.role_principal_id =d.principal_id JOIN[$destinationDbName].sys.database_principalsm
    ON r.member_principal_id =m.principal_id
WHERE m.principal_id > 5;
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];’ +dp.state_desc +‘ ‘ + dp.permission_name + ‘ TO ‘ + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS +‘;’
FROM[$destinationDbName].sys.database_permissionsdp JOIN [$destinationDbName].sys.database_principalsd
    ON dp.grantee_principal_id =d.principal_id
WHERE dp.major_id = 0
    AND dp.state <> ‘W’
    AND dp.permission_name <>‘CONNECT’
ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC;
INSERT #Commands(Cmd)
SELECT ‘USE [$destinationDbName];GRANT ‘ + dp.permission_name + ‘ TO ‘ + QUOTENAME(d.name) COLLATELatin1_General_CI_AS + ‘ WITH GRANT OPTION;’
FROM[$destinationDbName].sys.database_permissionsdp JOIN [$destinationDbName].sys.database_principalsd
    ON dp.grantee_principal_id =d.principal_id
WHERE dp.major_id = 0
    AND dp.state = ‘W’
    AND dp.permission_name <>‘CONNECT’
ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC;
SELECT Cmd FROM #Commands
ORDER BY RowId;
        “@
}
This gives us the existing permissions that we’ll re-apply later in step 6.  You can see we’re creating code to resync logins (ALTER USER…WITH LOGIN), create the user if it doesn’t exist, create database roles if they don’t exist, and add users to those database roles.

Step 5:  Restore the backup file to $destinationInstance using the $destinationDbName name.  This is the real meat and potatoes of the script.  
$restore = new-object (‘Microsoft.SqlServer.Management.Smo.Restore’)
$restore.Database = $destinationDbName
$restore.NoRecovery = $false
$restore.PercentCompleteNotification = 10
$restore.Devices.AddDevice($backupDataFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

First it checks $destinationInstance to see if $destinationDbName already exists.  If it does, then it just restores over it.  If $destinationDbName does not exist, then the script will create it using the RESTORE…WITH MOVEcommand.  Since the source and destination SQL Servers have different instance names, the file folders for the physical MDF & LDF files will be different.  The script uses the default folder locations to store the data and log files.  This folders were specified when you installed SQL Server.  If the $sourceDbName has several NDF files, all of them will be placed in the default data folder.
$defaultMdf = $server.Settings.DefaultFile
$defaultLdf =$server.Settings.DefaultLog

Before the restore, the script will set the recovery mode of $destinationDbName to SIMPLE.  This is avoid the “backup tail log” error message in case the database is in FULL recovery mode.  It sets the database to single-user mode to kill any existing connections before the restore.  And after the restore is complete, it sets the recovery mode back to SIMPLE.
$db.RecoveryModel =[Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple
$db.UserAccess = “Single”
$db.Alter(
    [Microsoft.SqlServer.Management.Smo.TerminationClause]
    “RollbackTransactionsImmediately”)
Step 6:  Apply the saved permissions from step 4 to $destinationDbName.  These are the permissions that were scripted out from step 4.  They are applied to the $destinationDbName one line at a time.
foreach($Row in $commandList.Tables[0].Rows)
{
    $db.ExecuteNonQuery($Row[“Cmd”])
}

Step 7:  Delete the backup from $destinationPath.  This is the cleanup step.
remove-item$backupFile

When running the script from a console, the output will look like this.
=============================================================
 1: Perform Initial Checks & Validate Input Parameters
=============================================================
Validating parameters…OK
Verifying source SQL Server connectivity…OK
Verifying source database exists…OK
Verifying destination SQL Server connectivity…OK
Verifying source file share exists…OK
Verifying destination file share exists…OK
=============================================================
 2: Get Source Backup for the Restore
=============================================================
Connecting to TRON2R2PROD to find a restore file…
Selected file:  D:BackupR2PRODAdventureWorks2008R2AdventureWorks2008R2_db_201302060836.BAK
Verifying file: \TRON2BACKUPR2PRODAdventureWorks2008R2AdventureWorks2008R2_db_201302060836.BAK exists…
Source file existence: OK
=============================================================
 3: Copy Backup File to the Destination
=============================================================
Copying file…
Copy file: OK
=============================================================
 4: Get Current Permissions on the Destination Database
=============================================================
Saving permissions on AdventureWorks2008R2…OK
=============================================================
 5: Restore Backup File to the Destination Server
=============================================================
Restoring database…
Database Restore: OK
=============================================================
 6: Restore Permissions to the Destination Database
=============================================================
Restoring existing permissions…
Existing permissions restored: OK
=============================================================
 7: Delete Backup File from the Destination Server
=============================================================
Deleting file…
Delete file: OK
=============================================================
    Database refresh completed successfully
=============================================================

The best part about using the Powershell script, is you can setup a SQL Agent job to call the script with the parameters already specified.  That way when the developer asks you refresh the same database then all you have to do is run the job, or you can work the developer to schedule the job to run automatically each day.

The SQL Agent job will need to setup as an “Operating system (CmdExec)” job type.  This is because it uses Powershell components that are outside the normal SQLPS group of commands.
The entire script is below.  Feel free to modify it as you see fit for your environment.
###########################################################################################
#
#   File Name:    AutoDatabaseRefresh.ps1
#
#   Applies to:   SQL Server 2008
#                 SQL Server 2008 R2
#                 SQL Server 2012
#
#   Purpose:      Used to automatically restore a database in another environment.
#
#   Prerequisite: Powershell v2.0 must be installed.
#                 SQL Server components must be installed.
#
#   Parameters:   [string]$sourceInstance - Source SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$sourceDbName - Source database
#                 [string]$sourcePath - Source share where the file exists
#                 [string]$destinationInstance - Destination SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$destinationDbName - Database to be refreshed/created on desitination server
#                 [string]$destinationPath - Share to copy backup file to (UNC Path Ex: \\SERVER\backup$)
#
#   Author:       Patrick Keisler
#
#   Version:      1.0.0
#
#   Date:         02/06/2013
#
#   Help:         http://www.patrickkeisler.com/
#
###########################################################################################

#Enable Debug Messages
#$DebugPreference = "Continue"

#Disable Debug Messages
$DebugPreference = "SilentlyContinue"

#Terminate Code on All Errors
$ErrorActionPreference = "Stop"

#Clear screen
CLEAR

#Load Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

function CheckForErrors {
    $errorsReported = $False
    if($Error.Count -ne 0)
    {
		Write-Host
		Write-Host "******************************"
        Write-Host "Errors:" $Error.Count
        Write-Host "******************************"
        foreach($err in $Error)
        {
            $errorsReported  = $True
            if( $err.Exception.InnerException -ne $null)
            {
                Write-Host $err.Exception.InnerException.ToString()
            }
            else
            {
                Write-Host $err.Exception.ToString()
            }
            Write-Host
        }
        throw;
    }
}
function GetServer {
    Param([string]$serverInstance)

    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($serverInstance)
    $server.ConnectionContext.ApplicationName = "AutoDatabaseRefresh"
	$server.ConnectionContext.ConnectTimeout = 5
    $server;
}
function GetRestoreFileList {
	Param([string]$serverInstance, [string]$sourcePath)

	Write-Host "Connecting to $serverInstance to find a restore file..."
	$server = GetServer($serverInstance)
	$db = $server.Databases["msdb"]
	$fileList = $db.ExecuteWithResults(
	@"
DECLARE
	 @BackupId int
	,@DatabaseName nvarchar(255);

SET	@DatabaseName = '$sourceDbName';
		
-- Get the most recent full backup for this database
SELECT TOP 1
	 @DatabaseName AS DatabaseName
	,m.physical_device_name
	,RIGHT(m.physical_device_name, CHARINDEX('\',REVERSE(physical_device_name),1) - 1) AS 'FileName'
	,b.backup_finish_date
	,b.type AS 'BackupType'
FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE b.database_name = @DatabaseName
	AND b.type = 'D'
	AND b.is_snapshot = 0
	AND b.is_copy_only = 0
	AND b.backup_finish_date IS NOT NULL
ORDER BY b.database_backup_lsn DESC;
"@
	)

	CheckForErrors
	
	if ($fileList.Tables[0].Rows.Count -ge 1)
	{
		foreach($file in $fileList.Tables[0].Rows)
		{
			$source = $sourcePath + "\" + $file["FileName"]
			Write-Host "Selected file: " $file["physical_device_name"]
			
		    Write-Host "Verifying file: $source exists..."
		    if((Test-Path -Path $source) -ne $True)
		    {
	            $errorMessage = "File:" + $source + " does not exists"
	            throw $errorMessage
		    }
		}

		Write-Host "Source file existence: OK"
		$file["FileName"].ToString();
	}
	else
	{
        $errorMessage = "Source database " + $sourceDbName + " does not have any current full backups."
        throw $errorMessage	
	}
}
function GetExistingPermissions {
    Param([string]$serverInstance, [string]$destinationDbName)

    $server = GetServer($serverInstance)
	$db = $server.Databases["$destinationDbName"]

	if(-not $db)
	{
		Write-Host "Database does not exist on: $serverInstance"
	}
	else
	{
		Write-Host "Saving permissions on $destinationDbName..." -NoNewline
		$commandList = $db.ExecuteWithResults(
		@"
IF OBJECT_ID('tempdb..#Commands') IS NOT NULL
	DROP TABLE #Commands;
CREATE TABLE #Commands(
	 RowId int identity(1,1)
	,Cmd varchar(2000));

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') ALTER USER ' + QUOTENAME(d.name) + ' WITH LOGIN = ' + QUOTENAME(s.name) + ';'
FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s
	ON d.sid = s.sid 
WHERE s.name IS NOT NULL
	AND d.type = 'S'
	AND d.name <> 'dbo';

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') CREATE USER ' + QUOTENAME(d.name) + ' FOR LOGIN ' + QUOTENAME(s.name) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(d.default_schema_name) + ';'
FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s
	ON d.sid = s.sid 
WHERE s.name IS NOT NULL
	AND d.type = 'S'
	AND d.name <> 'dbo';

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') CREATE USER ' + QUOTENAME(d.name) + ' FOR LOGIN ' + QUOTENAME(s.name) + ';'
FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s
	ON d.sid = s.sid 
WHERE s.name IS NOT NULL
	AND d.type IN ('U','G');

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(p.name,CHAR(39))+') CREATE ROLE ' + QUOTENAME(p.name) + ' AUTHORIZATION '+QUOTENAME(o.name)+';'
FROM [$destinationDbName].sys.database_principals p JOIN [$destinationDbName].sys.database_principals o
	ON o.principal_id = p.owning_principal_id
WHERE p.type = 'R' 
	AND p.is_fixed_role = 0 
	AND p.principal_id <> 0;

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];EXEC sp_addrolemember N' + QUOTENAME(d.name,'''') + ', N' + QUOTENAME(m.name,CHAR(39)) + ';'
FROM [$destinationDbName].sys.database_role_members r JOIN [$destinationDbName].sys.database_principals d
	ON r.role_principal_id = d.principal_id JOIN [$destinationDbName].sys.database_principals m
	ON r.member_principal_id = m.principal_id
WHERE m.principal_id > 5;

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];' + dp.state_desc + ' ' + dp.permission_name + ' TO ' + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS + ';'
FROM [$destinationDbName].sys.database_permissions dp JOIN [$destinationDbName].sys.database_principals d
	ON dp.grantee_principal_id = d.principal_id
WHERE dp.major_id = 0 
	AND dp.state <> 'W'
	AND dp.permission_name <> 'CONNECT'
ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC;

INSERT #Commands(Cmd)
SELECT 'USE [$destinationDbName];GRANT ' + dp.permission_name + ' TO ' + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS + ' WITH GRANT OPTION;'
FROM [$destinationDbName].sys.database_permissions dp JOIN [$destinationDbName].sys.database_principals d
	ON dp.grantee_principal_id = d.principal_id
WHERE dp.major_id = 0 
	AND dp.state = 'W'
	AND dp.permission_name <> 'CONNECT'
ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC;

SELECT Cmd FROM #Commands
ORDER BY RowId;
"@
		)

	CheckForErrors
	Write-Host "OK"
	}

	$commandList;
}
function CopyFile {
    Param([string]$sourcePath, [string]$backupFile, [string]$destinationpPath)

    $source = $sourcePath + "\" + $backupFile
    
    try
    {
        Write-Host "Copying file..."
		Write-Debug "Copy $source to $destinationpPath"
		copy-item $source -destination $destinationpPath
    }
    catch
    {
        CheckForErrors
    }

	Write-Host "Copy file: OK"
}
function DeleteFile {
    Param([string]$backupFile)

    try
    {
        Write-Host "Deleting file..."
		Write-Debug "Deleting file: $backupFile"
		remove-item $backupFile
    }
    catch
    {
        CheckForErrors
    }
    
    Write-Host "Delete file: OK"
}
function RestoreDatabase {
    Param([string]$serverInstance, [string]$destinationDbName, [string]$backupDataFile, [string]$actionType)

	Write-Host "Restoring database..."

    $server = GetServer($serverInstance)
	$server.ConnectionContext.StatementTimeout = 0
	$db = $server.Databases["$destinationDbName"]

    #Create the restore object and set properties
    $restore = new-object ('Microsoft.SqlServer.Management.Smo.Restore')
    $restore.Database = $destinationDbName
    $restore.NoRecovery = $false
    $restore.PercentCompleteNotification = 10
    $restore.Devices.AddDevice($backupDataFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

	if(-not $db)
	{
		Write-Debug "$destinationDbName does not exist..."
		
        #Grab the default MDF & LDF file locations.
        $defaultMdf = $server.Settings.DefaultFile
    	$defaultLdf = $server.Settings.DefaultLog

        #If the default locations are the same as the master database, 
        #then those values do not get populated and must be pulled from the MasterPath.
        if($defaultMdf.Length -eq 0)
        {
                $defaultMdf = $server.Information.MasterDBPath
    	}
    	if($defaultLdf.Length -eq 0)
        {
                $defaultLdf = $server.Information.MasterDBLogPath
        }
        
        if(-not $defaultMdf.EndsWith("\"))
        {
            $defaultMdf = $defaultMdf + "\"
        }
        if(-not $defaultLdf.EndsWith("\"))
        {
            $defaultLdf = $defaultLdf + "\"
        }
        		
        $restore.ReplaceDatabase = $True

		#Get the database logical file names            
        try
		{
			$logicalNameDT = $restore.ReadFileList($server)
		}
		catch
		{
			CheckForErrors
		}

        $FileType = ""

		Write-Debug "Restoring $destinationDbName to the following physical locations:"

        foreach($Row in $logicalNameDT)
        {
            # Put the file type into a local variable.
            # This will be the variable that we use to find out which file
            # we are working with.
            $FileType = $Row["Type"].ToUpper()

            # If Type = "D", then we are handling the Database File name.
            If($FileType.Equals("D"))
            {
                $dbLogicalName = $Row["LogicalName"]
				
				$targetDbFilePath = $Row["PhysicalName"]
				$position = $targetDbFilePath.LastIndexOf("\") + 1
				$targetDbFilePath = $targetDbFilePath.Substring($position,$targetDbFilePath.Length - $position)
				$targetDbFilePath = $defaultMdf + $targetDbFilePath
				
			    if((Test-Path -Path $targetDbFilePath) -eq $true)
				{
					$targetDbFilePath = $targetDbFilePath -replace $dbLogicalName, $destinationDbName
				}

				#Specify new data files (mdf and ndf)
		        $relocateDataFile = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile')
		        $relocateDataFile.LogicalFileName = $dbLogicalName            
		        $relocateDataFile.PhysicalFileName = $targetDbFilePath
		        $restore.RelocateFiles.Add($relocateDataFile) | out-null
		
				Write-Debug $relocateDataFile.PhysicalFileName
            }
            # If Type = "L", then we are handling the Log File name.
            elseif($FileType.Equals("L"))
            {
                $logLogicalName = $Row["LogicalName"]
				
				$targetLogFilePath = $Row["PhysicalName"]
				$position = $targetLogFilePath.LastIndexOf("\") + 1
				$targetLogFilePath = $targetLogFilePath.Substring($position,$targetLogFilePath.Length - $position)
				$targetLogFilePath = $defaultLdf + $targetLogFilePath

			    if((Test-Path -Path $targetLogFilePath) -eq $true)
				{
					$tempName = $destinationDbName + "_Log"
					$targetLogFilePath = $targetLogFilePath -replace $logLogicalName, $tempName
				}

				#Specify new log files (ldf)
		        $relocateLogFile  = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile')
		        $relocateLogFile.LogicalFileName = $logLogicalName            
		        $relocateLogFile.PhysicalFileName = $targetLogFilePath          
    		    $restore.RelocateFiles.Add($relocateLogFile) | out-null
		
				Write-Debug $relocateLogFile.PhysicalFileName
	        }          
        }
	}
	else
	{
		Write-Debug "Overwritting existing database..."
		
		#Set recovery model to simple on destination database before restore
		if($db.RecoveryModel -ne [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple)
    	{
            Write-Debug "Changing recovery model to SIMPLE"
            $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple
			try
			{
            	$db.Alter()
			}
			catch
			{
				CheckForErrors
			}
    	}

		#Set destination database to single user mode to kill any active connections
		$db.UserAccess = "Single"
		try
		{
			$db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]"RollbackTransactionsImmediately")
		}
		catch
		{
			CheckForErrors
		}
	}
	
    #Do the restore
	try
	{
    	$restore.SqlRestore($server)
	}
	catch
	{
    	CheckForErrors
	}
	
	#Reload the restored database object
	$db = $server.Databases["$destinationDbName"]
	
	#Set recovery model to simple on destination database after restore
	if($db.RecoveryModel -ne [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple)
    {
        Write-Debug "Changing recovery model to SIMPLE"
        $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple
		try
		{
        	$db.Alter()
		}
		catch
		{
			CheckForErrors
		}
    }
	
    Write-Host $actionType.ToString() "Restore: OK"
}
function RestorePermissions {
    Param([string]$destinationInstance, [string]$destinationDbName, $commandList)

	Write-Host "Restoring existing permissions..."
	$server = GetServer($destinationInstance)
	$db = $server.Databases[$destinationDbName]
	
	foreach($Row in $commandList.Tables[0].Rows)
	{
		#Apply existing permissions back to destination database
		Write-Debug $Row["Cmd"]
		try
		{
			$db.ExecuteNonQuery($Row["Cmd"])
		}
		catch
		{
			CheckForErrors
		}
	}
	
	Write-Host "Existing permissions restored: OK"
}
function PerformValidation {
    Param($sourceInstance, $sourceDbName, $sourcePath, $destinationInstance, $destinationDbName, $destinationPath)
	
	Write-Host "Validating parameters..." -NoNewline
	
	if([String]::IsNullOrEmpty($sourceInstance))
	{
		Write-Host "ERROR"
        $errorMessage = "Source server name is not valid."
        throw $errorMessage
    }
    if([String]::IsNullOrEmpty($sourceDbName))
    {
		Write-Host "ERROR"
        $errorMessage = "Source database name is not valid."
        throw $errorMessage
    }
    if([String]::IsNullOrEmpty($sourcePath))
    {
		Write-Host "ERROR"
        $errorMessage = "Source path is not valid."
        throw $errorMessage
    }
	else
    {
        if(-not $sourcePath.StartsWith("\\"))
        {
			Write-Host "ERROR"
            $errorMessage = "Source path is not valid: " + $sourcePath
            throw $errorMessage
        }
    }
    if([String]::IsNullOrEmpty($destinationInstance))
    {
		Write-Host "ERROR"
        $errorMessage = "Destination server name is not valid."
        throw $errorMessage
    }
    if([String]::IsNullOrEmpty($destinationDbName))
    {
		Write-Host "ERROR"
        $errorMessage = "Destination database name is not valid."
        throw $errorMessage
    }
	if([String]::IsNullOrEmpty($destinationPath))
    {
		Write-Host "ERROR"
        $errorMessage = "Destination path name is not valid."
        throw $errorMessage
    }
	else
    {
        if(-not $destinationPath.StartsWith("\\"))
        {
			Write-Host "ERROR"
            $errorMessage = "Destination path is not valid: " + $destinationPath
            throw $errorMessage
        }
    }
    
	Write-Host "OK"

    Write-Host "Verifying source SQL Server connectivity..." -NoNewline
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sourceInstance)
    $conn.ApplicationName = "AutoDatabaseRefresh"
	$conn.NonPooledConnection = $true
	$conn.ConnectTimeout = 5
	try
	{
		$conn.Connect()
        $conn.Disconnect()
	}
	catch
	{
		CheckForErrors
	}
    Write-Host "OK"
	
	Write-Host "Verifying source database exists..." -NoNewline
	$sourceServer = GetServer($sourceInstance)
    $sourcedb = $sourceServer.Databases[$sourceDbName]
	if(-not $sourcedb)
    {
		Write-Host "ERROR"
        $errorMessage = "Source database does not exist on $sourceInstance"
        throw $errorMessage
    }
    Write-Host "OK"

    Write-Host "Verifying destination SQL Server connectivity..." -NoNewline
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($destinationInstance)
    $conn.ApplicationName = "AutoDatabaseRefresh"
	$conn.NonPooledConnection = $true
	$conn.ConnectTimeout = 5
	try
	{
		$conn.Connect()
        $conn.Disconnect()
	}
	catch
	{
		CheckForErrors
	}
	Write-Host "OK"
	
    Write-Host "Verifying source file share exists..." -NoNewline
    if((Test-Path -Path $sourcePath) -ne $True)
    {
		Write-Host "ERROR"
        $errorMessage = "File share:" + $sourcePath + " does not exists"
        throw $errorMessage
    }
    Write-Host "OK"

    Write-Host "Verifying destination file share exists..." -NoNewline
    if((Test-Path -Path $destinationPath) -ne $True)
    {
		Write-Host "ERROR"
        $errorMessage = "File share:" + $destinationPath + " does not exists"
        throw $errorMessage
    }
    Write-Host "OK"
}

function Main{
	
	Param([string]$sourceInstance, [string]$sourceDbName, [string]$sourcePath, [string]$destinationInstance, [string]$destinationDbName, [string]$destinationPath)
	
	$Error.Clear()

    Write-Host
    Write-Host "============================================================="
    Write-Host " 1: Perform Initial Checks & Validate Input Parameters"
    Write-Host "============================================================="
	
	PerformValidation $sourceInstance $sourceDbName $sourcePath $destinationInstance $destinationDbName $destinationPath
	
    Write-Host
    Write-Host "============================================================="
    Write-Host " 2: Get Source Backup for the Restore"
    Write-Host "============================================================="

	$restoreFile = GetRestoreFileList $sourceInstance $sourcePath
		
    Write-Host
    Write-Host "============================================================="
    Write-Host " 3: Copy Backup File to the Destination"
    Write-Host "============================================================="
	
	CopyFile $sourcePath $restoreFile $destinationPath

    Write-Host
    Write-Host "============================================================="
    Write-Host " 4: Get Current Permissions on the Destination Database"
    Write-Host "============================================================="

	$existingPermissions = GetExistingPermissions $destinationInstance $destinationDbName
	
    Write-Host
    Write-Host "============================================================="
    Write-Host " 5: Restore Backup File to the Destination Server"
    Write-Host "============================================================="

	$restoreFile = $destinationPath + "\" + $restoreFile
	RestoreDatabase $destinationInstance $destinationDbName $restoreFile "Database"
		
    Write-Host
    Write-Host "============================================================="
    Write-Host " 6: Restore Permissions to the Destination Database"
    Write-Host "============================================================="

	if($existingPermissions)
	{
		RestorePermissions $destinationInstance $destinationDbName $existingPermissions
	}
	
    Write-Host
    Write-Host "============================================================="
    Write-Host " 7: Delete Backup File from the Destination Server"
    Write-Host "============================================================="

	DeleteFile $restoreFile
	
    Write-Host
    Write-Host "============================================================="
    Write-Host "    Database refresh completed successfully"
    Write-Host "============================================================="
}

#Hard-coded values used only for development
#$sourceInstance = "TRON2\R2PROD"
#$sourceDbName = "AdventureWorks2008R2"
#$sourcePath = "\\TRON2\BACKUP\R2PROD\AdventureWorks2008R2"
#$destinationInstance = "TRON3\R2TEST"
#$destinationDbName = "AdventureWorks2008R2"
#$destinationPath = "\\TRON3\BACKUP"

#Prompt for inputs for an interactive script
#$sourceInstance = $(Read-Host "Source SQL Server name (Ex: SERVER\INSTANCE)")
#$sourceDbName = $(Read-Host "Source database")
#$sourcePath = $(Read-Host "Source share where the file exists (UNC Path Ex: \\SERVER\BACKUP)")
#$destinationInstance = $(Read-Host "Destination SQL Server name (Ex: SERVER\INSTANCE)")
#$destinationDbName = $(Read-Host "Database to be refreshed/created on desitination server")
#$destinationPath = $(Read-Host "Destination share to copy backup file to (UNC Path Ex: \\SERVER\BACKUP)")

#Capture inputs from the command line.
$sourceInstance = $args[0]
$sourceDbName = $args[1]
$sourcePath = $args[2]
$destinationInstance = $args[3]
$destinationDbName = $args[4]
$destinationPath = $args[5]

$debug = "Source Instance Parameter: " + $sourceInstance
Write-Debug $debug
$debug = "Source Database Parameter: " + $sourceDbName
Write-Debug $debug
$debug = "Source Path Parameter: " + $sourcePath
Write-Debug $debug
$debug = "Destination Instance Parameter: " + $destinationInstance
Write-Debug $debug
$debug = "Destination Database Parameter: " + $destinationDbName
Write-Debug $debug
$debug = "Destination Path Parameter: " + $destinationPath
Write-Debug $debug

Main $sourceInstance $sourceDbName $sourcePath $destinationInstance $destinationDbName $destinationPath
Sep 272012
 

Idera SQL Diagnostic Manager (or as I call it “SQLdm”) is a great monitoring and performance tuning tool that I was introduced to a few years ago.  There are no server-side agents, so it’s a very lightweight tool and good for monitoring all SQL Servers in your environment.  The  GUI is great for managing servers except when I need to put all monitored servers in maintenance mode.  If I’m only monitoring a few servers, it’s not that big of a deal.  However, if I’m monitoring a hundred servers, then using the GUI to place them all in maintenance mode could take 20 or 30 minutes.  Luckily for us, Idera has included a Powershell plugin for managing SQLdm.  If you’re not familiar with Powershell, then hop on over to Microsoft site to start learning before you get left behind.  http://technet.microsoft.com/en-US/scriptcenter/dd742419.aspx.  For the rest of this post, I’ll assume you have some basic knowledge of how Powershell works.

You can run the SQLdm Powershell commands from any workstation that has the SQLdm console installed.  For this example, I am running it directly from the repository server.

Start up a Powershell session in the Command Prompt window.

Start, Run, “Powershell”


Load the SQLdm snapin.

Add-PSSnapin SQLdmSnapin


Using the New-SQLdmDrive cmdlet, create a SQLdm drive, specifiing drive name,  the SQL Server that hosts the repository and the repository database name.


New-SQLdmDrive dm TRON4TEST1 SQLdmRepository


Change to the dm drive (Figure 1).


Figure 1


Now use the Set-SQLdmMonitoredInstance cmdlet to set a monitored server into maintenance mode until further notice (Figure 2).

Set-SQLdmMonitoredInstance (Escape-SQLDMName -name TRON4TEST2) -MMAlways


Figure 2


Simple enough right?  Yes, but it’s probably just as slow to key in that same command for each server in your inventory as clicking in the GUI.  Now comes the real “power” in Powershell.  You can programmatically loop through all servers in your inventory to enable maintenance mode.

Start by getting a list of monitored servers from the SQLdm repository.  Run the following TSQL code against the SQLdmRepository database.

SELECT InstanceName FROM MonitoredSQLServers
WHERE Active = 1
ORDER BY InstanceName;


In order to combine that server list with the SQLdm cmdlets, we need to add the SQL Server Powershell snapin.

Add-PSSnapin SqlServerCmdletSnapin100


Then you can use a foreach loop and the Invoke-Sqlcmd cmdlet to create a simple script to enable maintenance mode by looping through the results of that TSQL query.

foreach ($db in Invoke-Sqlcmd -query “SELECT InstanceName FROM MonitoredSQLServers WHERE Active = 1 AND MaintenanceModeEnabled = 0 ORDER BY InstanceName;” -database SQLdmRepository -serverinstance TRON4TEST1 ) {    
    #Enable MM for the selected server
    Set-SQLdmMonitoredInstance (Escape-SQLDMName -name $db.InstanceName) -MMAlways 
} 



You can use the same script to disable maintenance mode on all servers just by changing “-MMAlways ” to “-MMNever “.

In this example, all of the monitored servers are named instances, so that’s why we must use the Escape-SQLDMName cmdlet.  It’s allows you to specify server names that contain special characters such as: */:<>|?[].  If all of your monitored servers are using the default instance then you won’t need to use this cmdlet.

Now let’s build on this example by adding some more logic and features to our script.  Let’s say you are monitoring a total of 5 servers, and 1 of them (TRON4TEST1) is always in maintenance mode for some odd reason.  When you run this Powershell script to enable maintenance mode, then all 5 servers will be placed in maintenance mode.  Consequently, when you run the script to disable maintenance mode, it will disable it for all 5 including the 1 server that you didn’t want it disabled.

In order to prevent the TEST1 instance from being disabled, we need to create a table to store the data when we execute the script.

You can create this table directly in the SQLdmRepository database, but it can be placed in any database.  When I need to customize a vendor database, I always create my own schema.  This helps to prevent any issues when you upgrade to the next version from the vendor.

USE SQLdmRepository;
GO
CREATE SCHEMA DBA AUTHORIZATION dbo;
GO
CREATE TABLE DBA.MaintenanceMode(

InstanceName nvarchar(256) null,
MMEnabledOn datetime null,
LoginName nvarchar(128) null);

GO


When we execute the Powershell script, it first checks the DBA.MaintenanceMode table for any existing rows.   If the table is empty, then the script will enable maintenance mode for all servers not currently in maintenance mode.

SELECT InstanceName FROM MonitoredSQLServers 
WHERE Active = 1 AND MaintenanceModeEnabled = 0 
ORDER BY InstanceName;


When it’s done processing each one, it writes the server name to the DBA.MaintenanceMode table.
The next time the script is executed, it sees the DBA.MaintenanceMode table is not empty and selects the servers to process from that table (Figure 3).

SELECT * FROM DBA.MaintenanceMode 
ORDER BY InstanceName;


Figure 3


The script processes each server and then deletes that row from the DBA.MaintenanceMode table.

What you have is a pure toggle script that only enables maintenance mode for servers not currently in that state, keeps track of that list of servers, and then disables maintenance mode for only that list of servers.  In our example, TRON4TEST1 server will always remain in maintenance mode.

Finally, create a SQL Agent job that executes this Powershell script, so the next time you need to enable maintenance mode for your entire environment you can just run the job.

The entire Powershell script is below.  Please feel free to modify it as you see fit for your environment.

###########################################################################################
#
#   File Name:    SQLdmToggleMM.ps1
#
#   Applies to:   Idera SQL Diagnostic Manager v7
#
###########################################################################################

#Clear screen
CLEAR

function LoadSnapins {

	# Load SqlServerCmdletSnapin100
	Add-PSSnapin SqlServerCmdletSnapin100
	
	# Load SQLdmSnapin
	Add-PSSnapin SQLdmSnapin
}

function ToggleMM {

	#Create a SQLdm drive
    New-SQLdmDrive dm TRON4\TEST1 SQLdmRepository | Out-Null
    #Change to the SQLdm drive
	cd dm:\
	Write-Host

	#Get row count of DBA.MaintenanceMode.
	#This will determine if MM needs to be enabled or disabled for all servers.
	$Count = Invoke-Sqlcmd -query "SELECT COUNT(*) AS 'RowCount' FROM DBA.MaintenanceMode;" -database SQLdmRepository -serverinstance TRON4\TEST1

	if($Count.RowCount -eq 0)
	{
		#Table DBA.MaintenanceMode is empty, so enable MM for all servers.
	    Write-Host "DBA.MaintenanceMode table is empty so enabling MM for all servers"
	    
		foreach ($db in Invoke-Sqlcmd -query "SELECT InstanceName FROM MonitoredSQLServers WHERE Active = 1 AND MaintenanceModeEnabled = 0 ORDER BY InstanceName;" -database SQLdmRepository -serverinstance TRON4\TEST1 )
	    {
			#Enable MM for the selected server
	        Set-SQLdmMonitoredInstance (Escape-SQLDMName -name $db.InstanceName) -MMAlways | Out-Null
			
			#Save MM information in DBA.MaintenanceMode
			$Server=$db.InstanceName 
	        Invoke-Sqlcmd -query "INSERT DBA.MaintenanceMode SELECT '$Server',GETDATE(),SUSER_NAME();" -database SQLdmRepository -serverinstance TRON4\TEST1 
			Write-Host "Maintenance Mode Enabled for:" $db.InstanceName
	    }
	}
	else
	{
		#Table DBA.MaintenanceMode is not empty, so disable MM for all servers listed in this table.
	    Write-Host "DBA.MaintenanceMode table is not empty so disabling MM for all servers"
	    
	    foreach ($db in Invoke-Sqlcmd -query "SELECT InstanceName FROM DBA.MaintenanceMode ORDER BY InstanceName;" -database SQLdmRepository -serverinstance TRON4\TEST1 )
	    {
			#Enable MM for the selected server
	        Set-SQLdmMonitoredInstance (Escape-SQLDMName -name $db.InstanceName) -MMNever | Out-Null

			#Remove server from DBA.MaintenanceMode table
			$Server=$db.InstanceName 
	        Invoke-Sqlcmd -query "DELETE FROM DBA.MaintenanceMode WHERE InstanceName = '$Server';" -database SQLdmRepository -serverinstance TRON4\TEST1
			Write-Host "Maintenance Mode Disabled for:" $db.InstanceName
	    }
	}
}

function Main {

	Write-Host
	Write-Host "============================================================="
	Write-Host " 1. Loading Snapins"
	Write-Host "============================================================="

	LoadSnapins

	Write-Host
	Write-Host "============================================================="
	Write-Host " 2. Looping Through All Servers to Toggle MM"
	Write-Host "============================================================="

	ToggleMM

	Write-Host
	Write-Host "============================================================="
	Write-Host "    Toggle MM Completed Successfully"
	Write-Host "============================================================="
}

#Clear any previous errors
$Error.Clear()

#Go to main section
Main