September 6, 2013 at 3:43 am
Comments posted to this topic are about the item SQL Powershell Script To Restore multiple databases at One Go
February 9, 2014 at 11:45 pm
This is not workin in the restore part.please share your views
$DBDropFolder = "\\share\BAK\"
$destinationFolder = "E:\temp\"
$destinationServerInstance = "."
$destinationDbfileFolder = "E:\Data\"
function CopyBakFileFromServer($sourceFolder, $destinationFolder, $patternFull, $patternDiff) {
if (-not (test-Path $destinationFolder)) { New-Item $destinationFolder -type directory }
write-host "Copy file from $sourceFolder to $destinationFolder with pattern: full - $patternFull; diff - $patternDiff"
$files = @(gci $DBDropFolder -Filter $patternFull | Sort LastWriteTime -Descending)
if (-not ($files -eq $null) -and $files.Count -gt 0) {
$file = $files[0]
$fileName = $file.Name
$datetimeFull = $file.LastWriteTime
write-host "file found: $fileName created $datetimeFull, copying ..."
Copy-Item -path $file.FullName -destination "$destinationFolder" -force
write-host "file found: $fileName created $datetimeFull, copied ..."
$filesDiff = @(gci $DBDropFolder -Filter $patternDiff | Sort LastWriteTime -Descending)
if (-not ($filesDiff -eq $null) -and $filesDiff.Count -gt 0) {
$fileDiff = $filesDiff[0]
$fileNameDiff = $fileDiff.Name
$datetimeDiff = $fileDiff.LastWriteTime
if ($datetimeDiff -gt $datetimeFull) {
write-host "file found: $fileNameDiff created $datetimeDiff, copying ..."
Copy-Item -path $fileDiff.FullName -destination "$destinationFolder" -force
write-host "file found: $fileNameDiff created $datetimeDiff, copied ..."
} else {
write-Host "file found: $fileNameDiff created $datetimeDiff, which is older than the full bak, skipped copying"
}
}
}
}
function RestoreDatabaseFromFile ($serverInstance, $bakFile, $dbFileFolder, $dbName) {
if (-not (test-Path $dbFileFolder)) { New-Item $dbFileFolder -type directory }
if (test-Path $bakFile) {
$fileMode = "1"
if ($bakFile.Contains("_DIFF.bak")) { $fileMode = "2" }
write-host "restoring $dbName ==> $serverInstance, mode $fileMode ..."
$sqlRestoreDB = "use Master;ALTER DATABASE $dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE [$dbName] FILE = N'$dbName' FROM DISK = N'$bakFile' WITH FILE = $fileMode, $SQLConn = New-Object System.Data.SQLClient.SQLConnection
$SQLConn.ConnectionString = “Server=$SQLServer; Trusted_Connection=True”
$SQLConn.Open()
$SQLCmd = New-Object System.Data.SQLClient.SQLCommand
$SQLcmd = $SQLconn.CreateCommand()
$sqlcmd.commandtimeout=0
$SQLcmd.CommandText=“Restore filelistonly from disk='$dbFileFolder\$bakfile'"
$rdr=$SQLcmd.ExecuteReader()
$files = $null
while($rdr.Read())
{
$test = $rdr["Type"].ToString()
if($test -eq 'D')
{
$files = $files + "MOVE N'$dbName' TO N'$dbFileFolder\$dbName.mdf"
}
if($test -eq 'L')
{
$files = $files + "MOVE N'$dbName'"+"_log"+" TO N'$dbFileFolder\$dbName.ldf"
}
}, NOUNLOAD, REPLACE, STATS = 10; ALTER DATABASE $dbName SET MULTI_USER;"
Invoke-Sqlcmd -Query $sqlRestoreDB -ServerInstance $serverInstance
} else {
write-host "$bakFile was not found"
}
}
CopyBakFileFromServer $DBDropFolder $destinationFolder DW.bak DW_DIFF.bak
CopyBakFileFromServer $DBDropFolder $destinationFolder Staging.bak Staging_DIFF.bak
CopyBakFileFromServer $DBDropFolder $destinationFolder Data.bak Data_DIFF.bak
RestoreDatabaseFromFile $destinationServerInstance DW.bak $destinationDbfileFolder DW
RestoreDatabaseFromFile $destinationServerInstance DW_DIFF.bak $destinationDbfileFolder DW
RestoreDatabaseFromFile $destinationServerInstance Staging.bak $destinationDbfileFolder Staging
RestoreDatabaseFromFile $destinationServerInstance Staging_DIFF.bak $destinationDbfileFolder Staging
RestoreDatabaseFromFile $destinationServerInstance Data.bak $destinationDbfileFolder Data
RestoreDatabaseFromFile $destinationServerInstance Data_DIFF.bak $destinationDbfileFolder Data
February 10, 2014 at 10:13 am
can you please share the error..
February 14, 2014 at 11:49 am
Thanks for sharing this script
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply