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
Categories: