다음을 통해 공유

SQL: How to refresh Multiple SQL Databases with PowerShell

  • 아티클

   This article won Technet GURU silver medal. You are welcome to update and give your valuable feedback

Abstract

How to automate of DB refresh activity? Is it possible to refresh more than one db? Can this be automated through T-SQL scripting or SSIS or PowerShell or any other tools or techniques?. Do we have any robust method to perform this activity? You can find many posts over the internet about refreshing databases. This article talks about one of the methods using PowerShell SMO.

Solution

Yes, this can be done using T-SQL, SSIS, import and Export and PowerShell SMO. The various methods are listed below

  • Backup and Restore
  • SSIS
  • Import and Export
  • PowerShell SMO 

The purpose of the article is to illustrate refresh activity using PowerShell SMO. Initiating Backup and Restore for the desired group of specific databases.  I feel the PowerShell gives a robust way to define the process and the advantage of using PowerShell SMO is the flexibility to ease out the flow.

Also, I've attached reference links which talks about other techniques and guidelines. Refer the reference section for more information

Step by Step Details

  • Backup the databases
  • Script the logins, users and roles
  • Restore the databases
  • Execute security script and fix the login

DB Refresh Flow

  • List the databases to be backup
  • Define the backup directory
  • A function call to backup specific group databases
  • Restore databases on the target server

 

https://sqlpowershell.files.wordpress.com/2016/05/backupandrestoreimage1.jpg?w=640

Backup

The multiple SQL databases backup invokes PowerShell SMO class to initiate a backup on a specific folder

Input Parameters

The backup databases have three mandatory parameters

  • $SQLServer – Source SQL Server where we have to initiate the backup – For Example AQDBSP18
  • $BackupDirectory – Backup path for the BAK files – For example – F:\PowerSQL
  • $dbList – List of databases separated by a comma – “rtc,SafetyDB”

Example

PS:\>Invoke_SQLDBBackup -SQLServer <ServerName> -BackupDirectory <Directory> -dbList <"db1,db2,db3">

Backup Code

Function Invoke_SQLDBBackup 
{ 
 param ([Parameter(Mandatory=$true,Position=0)][String]$SQLServer,  
[Parameter(Mandatory=$true,Position=1)][String]$BackupDirectory, 
[Parameter(Mandatory=$true,Position=2)][String]$dbList) 
   
[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 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
   
$BackupDate = get-date -format yyyyMMdd_HHmmss 
   
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer 
   
foreach ($Database in $Server.databases) 
{ 
foreach($db in $DbList.split(",")) 
{ 
if($Database.Name -eq $db) 
{ 
$DatabaseName = $Database.Name 
$DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup") 
$DatabaseBackup.Action = "Database"
$DatabaseBackup.Database = $DatabaseName 
$DatabaseBackup.Devices.AddDevice($BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK", "File") 
$DatabaseBackup.SqlBackup($Server) 
} 
} 
} 
}
  
Invoke_SQLDBBackup -SQLServer ASDBSP18 -BackupDirectory f:\PowerSQL -dbList "SafetyDB,rtc,rtcab1"

Output of DB backup

https://sqlpowershell.files.wordpress.com/2016/05/backupspecificdatabases1.jpg?w=640

Scripting Logins, Role Assignments and Server Permissions Using PowerShell

Copying SQL server logins to file is a little bit tricky though we are able to generate the script the password need to handled in a proper way to make it work.

Usually, we’ll make use of scripts like sp_help_revlogin to manage copying logins from one instance to another but the requirement won’t suffice our purpose it won’t generate the script for server permissions and role assignments.When you are automating the entire refresh process, PowerShell provides a flexibility to run SQL and generate the scripts in the said path. The SQL file can be invoked using invoke-sqlcmd to execute it across any target server

The below code which also loads and validates the SQL snapins

SqlServerProviderSnapin100

SqlServerCmdletSnapin100

Example 1

To generate the script on a source server

PS:\>Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut

Example 2

Apply the generated script on the target server

PS P:\> Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut -TargetServer <ServerName>

Copy-Logins Code

function Copy-Logins{
    [cmdletbinding()]
    Param
    (
    [parameter(Mandatory=$true)][string] $Sourceserver,
    [String] $TargetServer,
    [string] $outpath)
  
##Create a new outfile at query function call
  
[string]$FileDS = Get-Date -Format "yyyyMMdd"
  
[string]$outFile = $outpath +'\'+ $Sourceserver+'_'+$FileTS + "_login.sql"
  
New-Item -ItemType file $outfile -Force
  
##Prepare the connection string
  
$SqlConnection = New-Object System.Data.SQLClient.SQLConnection("Server=$Sourceserver;Integrated Security=sspi;");
$SqlCommand = New-Object System.Data.SQLClient.SqlCommand;
$SqlCommand.Connection = $SqlConnection;  
  
Try
{
  
##SQL Account - Executing a SQL is better than working with Hashed value
  
$SQL=  "
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
               CASE
                    WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = '  +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '
                        + CASE WHEN SL.is_expiration_checked = 1 THEN  'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,'  ELSE 'OFF,' END
                    ELSE ' FROM WINDOWS WITH'
                END
       +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
        ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')"
  
$SQL2="SELECT
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
    JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
    JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
        AND SL.name NOT LIKE '##%##'
        AND SL.name NOT LIKE 'NT AUTHORITY%'
        AND SL.name NOT LIKE 'NT SERVICE%'
        AND SL.name <> ('sa')"
  
$SQL3="
SELECT
    CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
        THEN SrvPerm.state_desc
        ELSE 'GRANT'
    END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
    CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
        THEN ''
        ELSE ' WITH GRANT OPTION'
    END collate database_default AS [-- server Level Permissions to Be Granted --]
FROM sys.server_permissions AS SrvPerm
    JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
WHERE   SP.type IN ( 'S', 'U', 'G' )
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')"
   
        $SqlCommand.CommandText = $SQL;  
        $SqlConnection.Open();
        $table = $SqlCommand.ExecuteReader();
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            }
        $SqlConnection.Close();
        $SqlConnection.Open();
        $SqlCommand.CommandText = $SQL2;  
        $table = $SqlCommand.ExecuteReader();
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            }
        $SqlConnection.Close();
        $SqlConnection.Open();
        $SqlCommand.CommandText = $SQL3;  
        $table = $SqlCommand.ExecuteReader();
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            }
        $SqlConnection.Close();
  
}
catch
{
 $ErrorMessage = $_.Exception.Message
 Write-host "$Sourceserver does't exist or Invalid SQL instance Name $ErrorMessage"
}
  
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin100'}))
{
   add-pssnapin SqlServerProviderSnapin100
   write-host "Loading SqlServerProviderSnapin100 in session"
}
else
{
  write-host "SqlServerProviderSnapin100 is already loaded"
}
  
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'}))
{  
    add-pssnapin SqlServerCmdletSnapin100
   write-host "Loading SqlServerCmdletSnapin100 in session"
}
else
{
  write-host "SqlServerCmdletSnapin100 is already loaded"
}
  
  
 If($TargetServer){
 try
    {
    Invoke-Sqlcmd -ServerInstance $TargetServer -InputFile $outFile -ErrorAction Stop
    }
catch
    {
     Write-host "$TargetServer not reachable "
     }
  
 }
  
  
} 

Restore

The purpose of the below script is to restore database/s from a database backup. 

Input Parameters

The Restore databases have three mandatory parameters

  • param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer – Source where you have the backup files
  • [Parameter(Mandatory=$true,Position=1)][String]$DestServer – Target Server where the databases to be restored
  • [Parameter(Mandatory=$true,Position=2)][String]$RestoreDbLis – List of databases to be restored on DestServer and databases are separated by comma – “rtc,SafetyDB”

After backup, physical file name details and converting the path to UNC  done through a Function BackupFile. It returns the UNC path of an actual backup file located on the source server.For example, The backup of ABC databases initiated on SQLSERVER20 and dump got created on F:\PowerSQL\ABC_20160511_104239.BAK then the function will return the following value

**\SQLSERVER20\f$\PowerSQL\ABC_20160511_104239.BAK **

For the database, restore has used the readily available code from the internet. The Function Invoke-DatabaseRestore uses the target server and actual backup physical location to restore the database

The successive restore will overwrite the target database

Example

PS:\>Invoke_SQLDBRestore -SourceServer <SourceServerName> -DestServer <DestinationServer> -RestoreDbList "SafetyDB,rtc,rtcab1"

Restore Code 

Function Invoke_SQLDBRestore
{
  
param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer, 
[Parameter(Mandatory=$true,Position=1)][String]$DestServer,
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbList)
   
#Load the required assemblies SMO and SmoExtended.
[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
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
#SourceServer
$Backupserver=$SourceServer
$targetServer = $DestServer
##Restore DB List
#$RestoreDbList = "SafetyDB,rtc,rtcab1"
   
   
  
Function BackupFile
{
Param(
    [Parameter(Mandatory=$true,Position=0)]
    [string]$server,
    [Parameter(Mandatory=$true,Position=1)]
    [string]$database
)
Function New-SMOconnection {
    Param (
        [Parameter(Mandatory=$true)]
        [string]$server,
        [int]$StatementTimeout=0
    )
    If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
        Throw "Could not connect to SQL Server $server."
    }
    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
    $conn.applicationName = "PowerShell SMO"
    $conn.StatementTimeout = $StatementTimeout
    Try {$conn.Connect()}
    Catch {Throw $Error}
    if ($conn.IsOpen -eq $false) {
        Throw "Could not connect to SQL Instance $server."
    }
    $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
    $smo
}
Function Get-LastBackupFile {
    Param(
        [string]$server,
        [string]$database
    )
    <#   Use a hereto to construct the T-SQL
        You will notice the query eliminates any snapshots. This is because we
        sometimes have VEEAM backups on some servers.
    #>
    $qry = @"
DECLARE @dbname sysname 
SET @dbname = '$database'
SELECT  f.physical_device_name as [backup]
FROM    msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
            msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE   (s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7) 
        AND (s.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM         msdb.dbo.backupset WITH (nolock)
WHERE     (database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))
"@
    
    # Get an SMO Connection
    $smo = New-SMOconnection -server $server
    # most appropriate to use MSDB
    $db = $smo.Databases["msdb"]
    # Execute query with results
    $rs = $db.ExecuteWithResults($qry)
    # SMO connection is no longer needed
    $smo.ConnectionContext.Disconnect()
    # Return the result
    $rs.Tables[0].Rows[0].Item('backup')
}
# Load SMO Assemblies
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
    
# Call the function and trap any error
Try {$backup = Get-LastBackupFile -server $server -database $database}
Catch {
    $ex = $Error[0].Exception
    Write-Host $ex.Message
    While($ex.InnerException) {
        $ex = $ex.InnerException
        Write-Host $ex.Message
    }
}
<# Verify the file  
    NOTE: most development and run-time is performed on remote servers
    so there may be a need to convert to UNC format
#>
#$backup.replace(':','$')
#$backup
   
$backup = Join-Path "\\$($server.split('\')[0])"  $backup.replace(':','$')
    
if(!(Test-Path $backup)) {
    Throw "Database backup $backup not found"
}
return  $backup
   
}
   
function invoke-DatabaseRestore {
    param ([String]$SQLServer="(local)", $BackupPath, [String]$BackupFileFilter = "")
    #load assemblies
    [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
        
     
        $backupFile = $BackupPath
    
        #we will query the database name from the backup header later
        $server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $SQLServer
        $backupDevice = New-Object( "Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($BackupPath, "File")
        $smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" )
        $backupDevice| FL *
    
        #Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx
        $DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath }
        $LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath }
    
        #restore settings
        $smoRestore.NoRecovery = $false;
        $smoRestore.ReplaceDatabase = $true;
        $smoRestore.Action = "Database"
        $smoRestore.PercentCompleteNotification = 10;
        $smoRestore.Devices.Add($backupDevice)
     
        #get database name from backup file
        $smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
     
        #display database name
        "Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"]
     
        #give a new database name
        $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]
    
        #Relocate each file in the restore to the default directory
        $smoRestoreFiles = $smoRestore.ReadFileList($server)
    
        foreach ($File in $smoRestoreFiles) {
            #Create relocate file object so that we can restore the database to a different path
            $smoRestoreFile = New-Object( "Microsoft.SqlServer.Management.Smo.RelocateFile" )
     
            #the logical file names should be the logical filename stored in the backup media
            $smoRestoreFile.LogicalFileName = $File.LogicalName
    
            $smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L") {$LogPath} else {$DataPath} ) + "\" + [System.IO.Path]::GetFileName($File.PhysicalName)
            $smoRestore.RelocateFiles.Add($smoRestoreFile)
        }
        #restore database
        $smoRestore.SqlRestore($server)
     
       
}
   
   
foreach($db in $RestoreDbList.split(","))
{
$backupFile= BackupFile -server $Backupserver -database $db
invoke-DatabaseRestore -SQLServer $targetSErver -BackupPath $backupFile
}
}

Conclusion

  • Time Saving - Database refresh activity may eat a lot of our time during the process. If you are spending a lot of time in doing a refresh, please start now. This will save you countless hours in the future
  • Automated - Creating an automated tasks ease out refresh activity on dev or QA
  • Multiple database refreshes with one single execution - Think of refreshing multiple databases. for example refresh 20 SharePoint Content databases. Imagine the amount of time you spend in doing the entire activity. This process works great and tests for 20 databases.

References