Sometime the application installation want to create the same databases with the same data files.
In this case we need to rename them and move another folder not to update.
This PowerShell script renames two local SQL Server databases (database1 and database2),
Closes active connections with SINGLE_USER, detaches, moves their files to an “old” subfolder in their current location,
Reattaches them. All PowerShell output and commands are logged to Documents\log.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
# Get user's 'My Documents' path and prepare log directory $myDocs = [Environment]::GetFolderPath('MyDocuments') $logDir = Join-Path $myDocs 'log' if (-not (Test-Path $logDir)) { New-Item -Path $logDir -ItemType Directory | Out-Null } # Log file with timestamp $logFile = Join-Path $logDir "SQLTask_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt" Start-Transcript -Path $logFile # Parameters $serverInstance = 'localhost' # Change if needed $dbs = @('OWOC', 'OWOC_workflow') $suffix = '_old' Import-Module SqlServer -ErrorAction SilentlyContinue function Exec-SqlQuery($query) { Invoke-Sqlcmd -ServerInstance $serverInstance -Query $query } # Step 1: Rename databases with SINGLE_USER to close connections foreach ($db in $dbs) { $dbOld = "${db}${suffix}" Write-Host "Setting $db to SINGLE_USER and renaming to $dbOld..." Exec-SqlQuery @" ALTER DATABASE [$db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE [$db] MODIFY NAME = [$dbOld]; "@ } # Step 2: Get file paths for each database $dbFiles = @{} foreach ($db in $dbs) { $dbOld = "${db}${suffix}" $result = Exec-SqlQuery " SELECT mf.name AS LogicalName, mf.physical_name AS PhysicalName, mf.type_desc AS Type FROM sys.master_files mf WHERE mf.database_id = DB_ID('$dbOld'); " $dbFiles[$dbOld] = $result } # Step 3: Detach databases foreach ($db in $dbs) { $dbOld = "${db}${suffix}" Write-Host "Setting $dbOld to SINGLE_USER before detach..." Exec-SqlQuery "ALTER DATABASE [$dbOld] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;" Write-Host "Detaching $dbOld..." Exec-SqlQuery "EXEC sp_detach_db '$dbOld';" } # Step 4: Move files to "old" subfolder in their current location foreach ($dbOld in $dbFiles.Keys) { foreach ($row in $dbFiles[$dbOld]) { $filePath = $row.PhysicalName $baseDir = Split-Path $filePath $oldDir = Join-Path $baseDir 'old' if (-not (Test-Path $oldDir)) { Write-Host "Creating folder $oldDir" New-Item -Path $oldDir -ItemType Directory | Out-Null } $destPath = Join-Path $oldDir (Split-Path $filePath -Leaf) Write-Host "Moving $filePath to $destPath" Move-Item -Path $filePath -Destination $destPath # Update path for attach step $row.PhysicalName = $destPath } } # Step 5: Attach databases back with correct syntax foreach ($dbOld in $dbFiles.Keys) { $fileClauses = @() foreach ($row in $dbFiles[$dbOld]) { $fileClauses += "( FILENAME = N'$($row.PhysicalName)' )" } $fileClauseString = $fileClauses -join ",`n" $sql = "CREATE DATABASE [$dbOld] ON $fileClauseString FOR ATTACH;" Write-Host "Attaching $dbOld with files:" Write-Host $sql Exec-SqlQuery $sql } Write-Host "DONE. Databases renamed, files moved to 'old' subfolder at their original location, and reattached. See log at $logFile" Stop-Transcript |