Restore-SQLBackupFile.ps1
|
function Restore-SQLBackupFile { <# .Synopsis Restore a SQL Database to a File .DESCRIPTION Working with SQL Backups works much faster than working with NAVDataBackup. Easily working with SQL Backups can termendously make you more effective .NOTES No Return value .PREREQUISITES #> [CmdletBinding()] param ( [Parameter(Mandatory=$true)] [String] $BackupFile, [Parameter(Mandatory=$false)] [String] $DatabaseServer = '.', [Parameter(Mandatory=$false)] [String] $DatabaseInstance = '', [Parameter(Mandatory=$true)] [String] $DatabaseName, [Parameter(Mandatory=$false)] [String] $DatabaseDataPath = '', [Parameter(Mandatory=$false, Position=2)] [String] $DatabaseLogPath = '', [Parameter(Mandatory=$false)] [String] $TimeOut = 30 ) import-module 'sqlps' -DisableNameChecking if ([String]::IsNullOrEmpty($DatabaseDataPath)){ $SQLString = "SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')" $DatabaseDataPath = (invoke-sql -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -sqlCommand $SQLString)."Default Data Path" } if ([String]::IsNullOrEmpty($DatabaseLogPath)){ $SQLString = "SELECT [Default Log Path] = SERVERPROPERTY('InstanceDefaultLogPath')" $DatabaseLogPath = (invoke-sql -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -sqlCommand $SQLString)."Default Log Path" } $SQLString = "RESTORE FILELISTONLY FROM DISK=N'$BackupFile'" $DatabaseFileList = Invoke-sql -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -sqlCommand $SQLString $RestoreSQLString = "RESTORE DATABASE [$DatabaseName] FROM DISK = N'$BackupFile' WITH FILE = 1, " foreach ($File in $DatabaseFileList){ if ($File.Type -eq 'L') { $DatabaseFile = (join-path $DatabaseLogPath ($DatabaseName + "_$($File.FileId)" + [io.path]::GetExtension($File.PhysicalName)) ) } else { $DatabaseFile = (join-path $DatabaseDataPath ($DatabaseName + "_$($File.FileId)" + [io.path]::GetExtension($File.PhysicalName)) ) } $RestoreSQLString += "MOVE N'$($File.LogicalName)' TO N'$($DatabaseFile)', " } $RestoreSQLString += 'NOUNLOAD, REPLACE, STATS = 5' write-Host -ForegroundColor Green "Restoring database $DatabaseName" write-host -ForegroundColor gray $RestoreSQLString $null = Invoke-Sqlcmd -Query $RestoreSQLString -ServerInstance "$DatabaseServer\$DatabaseInstance" -QueryTimeout $TimeOut -Database 'master' } |