Microsoft SQL Server: How to Use T SQL to Move Transaction Log Files

Automated Script:

# moveDbStorage.ps1
# Version 0.0.1
# This version is intended for a local MS SQL Server
# Requirements:
# - Integrationed authentication has been enabled within local SQL Server
# - Each database is expected to have been configured with 1 db file and 1 transaction logs file. Deviation to this will be handled in next script version.

$intendedDbLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
$intendedDbLogLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
# $intendedDbBackupLocation='Z:\Backups' # this iteration doesn't deal with backups
function moveDbStorage($databaseName,$intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation){
$erroractionpreference='stop'
    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
            if ($userInput.ToLower() -eq $testValue.ToLower()){
                $confirmed=$true;
                write-host "Confirmed!`r`n";
                break;                
            }elseif($userInput -like 'cancel'){
                write-host 'Cancel command received.'
                $confirmed=$false
                break
            }else{
                cls;
                $content|write-host
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                }
            }
        return $confirmed;
    }

    $results=[hashtable]@{}
    try{
        # Ensure that the server has SQL PowerShell tools 
        if(!(Get-Module -ListAvailable -Name 'sqlps' -ea SilentlyContinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        $nugetInstalled=Get-PackageProvider nuget
        if(!$nugetInstalled){Install-PackageProvider -Name Nuget -RequiredVersion 2.8.5.201 -Force}          
        $trustPSGallery=(Get-psrepository -Name 'PSGallery').InstallationPolicy
        If($trustPSGallery -ne 'Trusted'){
            Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted
            }
        Install-Module PSWindowsUpdate -Confirm:$false -Force
        }         
        # Setting correct ACLs on provided storage locations
        $sqlInstance='MSSQLSERVER' # Assming default instance
        $sqlRunas=(Get-WMIObject Win32_Service -Filter "Name='$sqlInstance'").StartName
        foreach ($path in @($intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation)){
            write-host "Setting correct permissions on $path"
            if(!(Test-Path $path)){
                $null=New-Item -ItemType Directory -Force -Path $path
            }
            try{
                write-host "Granting $env:username and Administrators full access to $path..."
                $acl=Get-ACL $path
                $originalOwnerAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($acl.Owner,"FullControl","Allow")
                $administratorsAccess=New-Object System.Security.AccessControl.FileSystemAccessRule('Administrators',"FullControl","Allow")
                #$thisUserAccess = New-Object System.Security.AccessControl.FileSystemAccessRule($env:username,"FullControl","Allow")
                $sqlAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlRunas,"FullControl","Allow")
                $acl.AddAccessRule($originalOwnerAccess)
                $acl.AddAccessRule($administratorsAccess)                
                #$acl.AddAccessRule($thisUserAccess)
                $sqlHasFullPermissions=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
                if(!$sqlHasFullPermissions){
                    $acl.AddAccessRule($sqlAccess)
                }                           
                Set-Acl $path $acl
            }catch{
                Write-warning $_
            }
        }

        # Get default locations    
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        # $sqlServer=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
        # $defaultBackupDirectory=$sqlServer.Settings.BackupDirectory
        # $defaultDataDirectory=$sqlServer.Settings.DefaultFile
        # $defaultLogDirectory=$sqlServer.Settings.DefaultLog
        # Change Default backup directory
        # if($defaultBackupDirectory -ne $intendedDbBackupLocation){
        #     write-host "Setting default backup location to $dbBackupLocation"
        #     $sqlServer.Properties["BackupDirectory"].Value = $dbBackupLocation
        #     $sqlServer.Alter()
        # }

        # Change database storage locations
        $databases=Get-SqlDatabase -ServerInstance $env:computername
        # $nonSystemDatabases=$databases|?{$_.Name -notin @('master','model','msdb','tempdb')}|sort -property Size
        $nonSystemDatabases=$databases|?{$_.IsSystemObject -ne $true}|sort -property Size
        Clear-Host
        $confirmedAll=confirmation 'RUN ALL CHANGES AUTOMATICALLY'
        
        foreach($database in $nonSystemDatabases){
            #$database=$nonSystemDatabases[0]
            $dbName=$database.Name
            $tsql="exec sp_helpdb @dbname='$dbName'"
            $dbProfile=Invoke-Sqlcmd -Query $tsql -ServerInstance $env:computername
            if($null -eq $dbProfile){
                write-warning "$dbName status is currently unknown"
                $results[$dbName]="$(get-date) : status unknown - no changes"
            }else{
                $filePaths=$dbProfile.GetEnumerator()|%{$_.filename}
                $dbFile=$filePaths[1]
                $dbLogFile=$filePaths[2]
                $intendedDbFile=Join-Path -Path $intendedDbLocation -ChildPath $($dbName+'.mdf')
                $intendedDbLogFile=Join-Path -Path $intendedDbLogLocation -ChildPath $($dbName+'_log.ldf')
                $moveDbStorage=$dbFile -ne $intendedDbFile
                $moveDbLogStorage=$dbLogFile -ne $intendedDbLogFile
                if($moveDbStorage -or $moveDbLogStorage){
                    # Alternative method:
                    # $logicalFileNames=Invoke-Sqlcmd -Query "
                    #     USE $dbName;
                    #     GO
                    #     SELECT file_id, name AS logical_name
                    #     FROM sys.database_files"
                    # $dbLogicalName=$logicalFileNames.logical_name[0]
                    # $dbLogLogicalName=$logicalFileNames.logical_name[0]
                    # $tSqlAlterInPlace="
                    #     Use MASTER
                    #     GO
                    #     ALTER DATABASE [$dbName] SET OFFLINE;  
                    #     GO
                    #     ALTER DATABASE [$dbName]
                    #         MODIFY FILE (NAME = '$dbLogicalName',   
                    #         FILENAME = '$intendedDbFile');  
                    #     GO
                    #     ALTER DATABASE [$dbName]   
                    #         MODIFY FILE ( NAME = '$dbLogLogicalName',   
                    #         FILENAME = '$intendedDbLogFile');  
                    #     GO
                    #     ALTER DATABASE AdventureWorks2014 SET ONLINE;  
                    #     GO"
                    # Invoke-Sqlcmd -Query $tSqlDetachDb

                    $confirmMessage="Set $dbName with these paths:`r`n$dbFile => $intendedDbFile`r`n$dbLogFile => $intendedDbLogFile"
                    $confirmed=if(!$confirmedAll){confirmation $confirmMessage}else{$true}
                    if($confirmed){
                        write-host "Detaching database $dbName..."
                        $timer=[System.Diagnostics.Stopwatch]::StartNew()
                        $startTime=get-date
                        $tSqlDetachDb="
                            Use MASTER
                            GO
                            ALTER DATABASE [$dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                            GO
                            sp_detach_db @dbname = '$dbName', @skipchecks = 'true';
                            GO"
                        try{
                            Invoke-Sqlcmd -Query $tSqlDetachDb -EA Stop
                            if($moveDbStorage){
                                write-host "Moving $dbFile to $intendedDbFile..."
                                $null=move-item $dbFile $intendedDbFile
                            }
                            if($moveDbLogStorage){
                                write-host "Moving $dbLogFile to $intendedDbLogFile..."
                                $null=move-item $dbLogFile $intendedDbLogFile
                            }
                            $tSqlAttachDb="
                                Use MASTER
                                GO
                                sp_attach_DB '$dbName',
                                '$intendedDbFile',
                                '$intendedDbLogFile'
                                GO"
                            Invoke-Sqlcmd -Query $tSqlAttachDb
                            $elapsed=[math]::round($timer.Elapsed.TotalMinutes,2)
                            $endTime=get-date
                            $resultMessage="$dbName detached at $startTime and attached at $endTime - duration of $elapsed minutes."
                            write-host $resultMessage -foregroundcolor Green
                            $results[$dbName]="$endTime : $resultMessage"
                        }catch{
                            write-warning $_
                        }                          
                    }                    
                }else{
                    $results[$dbName]="$(get-date) : no changes"
                }
            }
        }
    }catch{
        Write-Warning $_
        }
    return $results
}

$results=moveDbStorage $databaseName $intendedDbLocation $intendedDbLogLocation $intendedDbBackupLocation
$results|write-host

Prior to making any changes to a SQL Server, one should gather information about the databases residing on such machines

-- Step 0: access the level of activities of all databases on SQL Server
WITH fs
AS
(
    select database_id, type, size * 8.0/1048576 size
    from sys.master_files
)
SELECT 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB  
FROM sys.databases db
ORDER BY LogFileSizeGB Desc

/*
name	        DataFileSizeGB	LogFileSizeGB
-------------   -------------   -------------
REPORTINGDB	    1.298828125	    1.713317871
DATABASE009	    235.080322265	1.407531738
DATABASE010	    6.718688964	    1.359436035
DATABASE999	    2.700012207	    0.869995117
*/

How To Manually Move Transaction Log File of a Single Database

-- Step 1: get database size of a single database
USE REPORTINGDB;
SELECT
    name,
    size,
    size * 8.0/1048576 'Size (GB)',
    max_size
FROM sys.database_files;

/* Sample output:
name	            size	Size (GB)	max_size
------------        ------  ---------   ---------
REPORTINGDBDat.mdf	155680	1.187744140	-1
REPORTINGDBLog.ldf	33592	0.256286621	268435456
*/

-- Step 2: get database and transaction log file locations
USE REPORTINGDB;
SELECT
  name 'Logical Name', 
  physical_name 'File Location'
FROM sys.database_files;

/* Sample Output
Logical Name        File Location
------------        ------------
REPORTINGDBDat.mdf	D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf
REPORTINGDBLog.ldf	D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf
*/

-- Step 3: set database to single user mode and detach database
-- WARNING: this will render database as OFFLINE !!!
Use MASTER
GO
ALTER DATABASE REPORTINGDB -- Set database to single user mode
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO 
sp_detach_db @dbname = 'REPORTINGDB', @skipchecks = 'true'; -- Detach the database
GO

-- Step 4: Move the files to new locations using PowerShell
-- move-item $dbFile $intendedDbFile
-- move-item $dbLogFile $intendedDbLogFile

-- Step 5: re-attach database with new log file location
USE master
GO
sp_attach_DB 'REPORTINGDB',  -- Re-attach the database
'D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf',
'E:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf'
GO

Leave a Reply

Your email address will not be published. Required fields are marked *