Using Powershell to Refresh a Test SQL Server Instance from Production

A project I’ve been wanting to work on for a long time is how to automate restores of our Production databases to the Test instance. There are a number of challenges associated with this. First, the restore has to be able to find which backup it needs to use. Secondly, many of our databases do not have their files configured in the same way (for example one may have a single .mdf and log whereas another may have multiple .ndf files). Third, restoring from a different instance causes the SQL Authentication users to be orphaned and therefore connections to the databases to fail. And this is just at the high level. There are many smaller complications along each of those roads. Because of these our restore model has typically been per-request, manually executed, and filled with many small and tedious steps. I wanted to replace it with one process that would restore all of the production databases from the proper backup, configure any “with MOVE” options dynamically, and fix any orphaned users. A secondary goal was also to make it as portable as possible so that I could easily reconfigure it for our other PROD/Test/Dev instances.

The result of this labor of love is copied below. You’ll notice that at the top you can configure the Source and Destination SQL instances to be used, as well as the Data\Log paths for both. This is the section that allows me to reconfigure it for other instances. You can make these parameters that are passed in if you want to call it manually. For my purposes it is a scheduled process that always runs against specific instance. Following that section is an exclusion list, built to avoid system databases as well as any others you want to skip (use cautiously to avoid angering coworkers in the Development or Testing departments).

The only function is one called “FixOrphans”. If you’ve guessed that’s where the orphaned users are repaired, then you’re today’s winner! This works by pulling a list of available logins from the destination, creating a list of orphans, and using the Source to find which login users should be matched to. It will also match “dbo” to a different login if that’s the way the Source database was configured. Of course, this breaks down if the logins are not named the same on both instances. This is the case for some of mine as well. In those cases I have a section at the bottom to take care of one-off scenarios by hard coding them. It isn’t ideal, but will have to do until I can change some policies in my organization.

A fun side note about the FixOrphans function. It started as a small section of the script, then quickly grew to become its own function. I became so interested in what could be done with it that I side-tracked for a while and wrote a stand-alone script just for repairing orphans. Being a faithful reader, you will remember it being covered in Fixing Orphaned SQL Users via PowerShell. So, the restore project was actually first and fixing orphans spun out of that. I then took that work and rolled it back into the restore script, so the code will be very similar to that first post.

After that function is declared we move on to the real work. A variable is created to store Source backup information so that we know which backup to use. It then loops through each of the objects stored in that variable. If the database is in the exclusion list it notes that and moves on. Otherwise, it sets a baseline for the restoration query and starts building on that. This is the portion that allows me to account for differing file configurations per database on the instance. For each database it will pull Source information about the files and dynamically build the MOVE statements. At the end it packages them all together and adds any remaining options, such as keep_cdc. After the full statement is built the script will set the database to single user, closing any connections. It will then drop the database and execute the restoration. Dropping isn’t entirely necessary, but our Test environment is often short on space. Dropping first allows me to free up room before starting the restore.

There are two things to note at this section. The first is that, while documentation will tell you that not specifying a timeout value for Invoke-Sqlcmd means that it’s unlimited, that simply isn’t true. Unless you specify one it will die after thirty seconds. Secondly, once the script successfully kicks off that restore command it will truck happily along its way to the next line, whether your restore finishes instantaneously or not (my wager is that it doesn’t). For that reason I built a Do…While loop to monitor for restore commands to finish and then allow the script to proceed. Otherwise it gets awkward trying to set your database to simple when it doesn’t actually exist yet. The commands to set that recovery option and shrink the log file are also both in the interest of saving space.

Once all of that is finished, it’s just a matter of resetting variables so that the next database has a clean run and calling the aforementioned FixOrphans function to give those SQL Auth users a home. After all of the elements in the array have been processed I write an error string that I’ve been compiling to a file and call it a day. Two files will be created. RefreshLog.txt contains all of the operational information, and Errors.txt contains, you guessed it, errors.

As with my other scripts, much of this will be different in your environment than it is in mine. However, I hope that this will be easily adaptable for your use and, if nothing else, that you can find useful elements that can be incorporated into your own scripts. As always, feel free to send me any questions, and I hope this helps.

P.S. I chose to use Invoke-Sqlcmd so that all versions of SQL, new or old, would be compatible. For newer instances feel free to use SMO.

#Save current working directory.
Push-Location

#Import only the Cmdlet we need from the SQL Module without warning about name checking.
Import-Module -DisableNameChecking -Name SQLPS -Cmdlet Invoke-Sqlcmd

#Return to working directory.
Pop-Location

#Set source and destination info.
$SourceSQLInstance = 'COMPUTER_NAME\INSTANCE_NAME'
$DestSQLInstance = 'COMPUTER_NAME\INSTANCE_NAME'

$SourceDataRoot = 'H:\PROD_INSTANCE_DATA'
$SourceLogRoot = 'H:\PROD_INSTANCE_LOG'

$DestDataRoot = 'J:\TEST_INSTANCE_DATA'
$DestLogRoot = 'J:\TEST_INSTANCE_LOG'

#Collection of errors to write to file.
$ErrMessage = @()

#List of databases to skip (ie system ones).
$DBExclusionList = "master", "model", "msdb", "tempdb", "TroubleMakerDatabase"

#List of current destination databases.
$CurrentDestDBs = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from sys.databases
                                                                                            where name != 'master'
                                                                                            and name != 'model'
                                                                                            and name != 'msdb'
                                                                                            and name != 'tempdb'"



####################################################################################################################
# Functions
####################################################################################################################


#Function to fix orphaned users.
function FixOrphans ($db)
 {

    #Get list of available logins on the destination instance.
    $LoginList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from master..syslogins"

    #Get list of orphaned users on the destination instance.
    $OrphanList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $db -query "exec sp_change_users_login 'Report'" 

    #Check the orphan count, proceed if there are any.
    if ($OrphanList.Count -gt 0)
    {
        #Process each orphaned user.
        foreach ($Orphan in $OrphanList)
        {
            #Set database user pricipal name.
            $User = $Orphan.UserName

            #Set login name from source instance.
            $Login = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database $db -query "SELECT S.loginname FROM $db.sys.database_principals as P 
                                                                                                join master..syslogins S on P.sid = S.sid where P.name = '$User'"
            #Make the login name a string for easy use.
            $LoginName = $Login.loginname
            
            #Test for whether the login exists on this instance. If it does, match it to the user.
            if ($LoginList.name -contains $LoginName)
            {
                
                #Handle orphand dbo users. These cannot be updated by sp_change_users_login.
                if ($User -eq "dbo")
                {
                    
                    $LogTime = Get-Date
                    $Message = "Changing owner to $LoginName for database $db."
                    #Log time and message.
                    $Message

                    Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $db -query "EXEC sp_changedbowner '$LoginName';"     

                }

                #For all other cases outside of dbo, use sp_change_users_login.
                else
                {
                    $Message = "Matching user $User to login $LoginName for database $db on $DestSQLInstance."
                    $Message

                    #Update the user's login.
                    Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $db -query "exec sp_change_users_login 'update_one', '$User', '$LoginName'" 
                }
            }


            #Give an informative message when the login is not in the list.
            else
            {
                
                $ErrMessage = $ErrMessage + "Failed to update user $User for database $db because there is no login on $DestSQLInstance."

            }
        }
    }
    
    #Inform if there are no orphans for this database. Happy day!
    else
        {
            $ErrMessage = $ErrMessage + "There are no orphans in database $DatabaseName."
            
        }


 }

####################################################################################################################
# End Functions
####################################################################################################################


#Get list of all database names and path to the most recent backup.
$FilePaths = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database MSDB -query "SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupmediafamily.physical_device_name 
                FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
                WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_finish_date, 102) >= GETDATE() - 7) AND  msdb.dbo.backupset.type = 'D' 
                ORDER BY msdb.dbo.backupset.database_name"

#Loop through each database.
foreach ( $file in $FilePaths ) 
{
    #Set backup file path.
    $BackupName = $file.physical_device_name

    #Set database name.
    $DBName = $file.database_name
   

    #Check for database names in the exclusions list.
    if ($DBExclusionList -contains $DBName)
        {
            echo "Database $DBName in Exclusion List"
        }

    
    #Process databases that have not been excluded.
    else
        {
            
            #Set database file information.
            $FileInfo = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database $DBName -query "SELECT name, physical_name FROM sys.database_files"

            #Set the foundation for the restore query.
            $RestoreQuery = "RESTORE DATABASE $DBName FROM  DISK = N'$BackupName' WITH "

            #Use the database file information to dynamically build the move file commands.
            foreach ($DBFile in $FileInfo)
            {
                #Build commands for data files.
                if ($DBFile.physical_name -like "*.mdf" -or $DBFile.physical_name -like "*.ndf")
                 {
                    $DataName = $DBFile.Name
                    $DataFile = $DBFile.physical_name.Replace($SourceDataRoot, $DestDataRoot)

                    #Append the move file command.
                    $FileCommand = $FileCommand + "MOVE N'$DataName' TO N'$DataFile', "
                
                 }
               
               #Build commands for log files.
               elseif ($DBFile.physical_name -like "*.ldf")
                 {
                    $LogName = $DBFile.Name
                    $LogFile = $DBFile.physical_name.Replace($SourceLogRoot, $DestLogRoot)

                    #Append the move file command.
                    $FileCommand = $FileCommand + "MOVE N'$LogName' TO N'$LogFile', "

                 }
            
            }
           
           #Add the completed move file commands to the overall query, and place remaining options on the end.
           $RestoreQuery = $RestoreQuery + $FileCommand + "REPLACE, keep_cdc"

           #$RestoreQuery >> C:\Users\lkfowler\Desktop\QueryOutput.txt

           echo "Restoring $DBName"

           #If database exists, set it to single user and drop it.
           if ($CurrentDestDBs.name -contains $DBName)
           {
                Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -query "ALTER DATABASE $DBName SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE"
                Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -query "DROP DATABASE $DBName"
           }


           #Execute the restore command.
           Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "$RestoreQuery" -querytimeout ([int]::MaxValue)

           #Loop until restore is finished         
           DO
            {
              $Restoring =  Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select count(*) from sys.dm_exec_requests where command = 'RESTORE DATABASE'" 
              Start-Sleep -s 5
            }
            WHILE ($Restoring -eq 1)
          

           #Set to simple.
           Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "ALTER DATABASE $DBName set recovery simple"

           #Shrink log file
           Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DBName -query "DBCC SHRINKFILE ($LogName, 1)"

           #Clear command-building variables so that they can be reused for the next database.
           Clear-Variable FileCommand
           Clear-Variable RestoreQuery

           #Call function to fix any orphaned users.
           FixOrphans -db $DBName

        }
}


#Write to error log after processing has finished.
Out-File Errors.txt -InputObject $ErrMessage

Leave a Reply Cancel reply