September 17, 2018 at 7:16 am
The scenario:
On Server1, I have a powershell script that is going to backup several databases, then move those .bak files to a remote server. I've already got this part ready and tested.
For the next step, over on Server2, I need to restore those databases. I'm not sure what the best approach is going to be. The stuff I already have is running in a loop. Here's the whole thing so far:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | out-null
# define the instance name
$instance = 'Server1Name'
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance
#Path
$bkdir = "J:\Backups"
$dbs = $s.Databases
foreach ($db in $dbs)
{
# Write-Output $db.Name
if($db.Name -like 'dbname1' -or $db.Name -like 'dbname2' -or $db.Name -like 'dbname3' -or
$db.Name -like 'dbname4' -or $db.Name -like 'dbname5' -or $db.Name -like 'dbname6' -or
$db.Name -like 'dbname7' -or $db.Name -like 'dbname8')
{
# CREATE THE BACKUP
$dbname = $db.Name
$dt = get-date -format yyyy_MM_dd #We use this to create a file name based on the timestamp
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$sPath = $bkdir + "\" + $dbname + "_forMig_" + $dt + ".bak"
$dPath = "\\Server2Name\forMigration\" + $bkdir + "\" + $dbname + "_forMig_" + $dt + ".bak"
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_forMig_" + $dt + ".bak", "File")
$dbBackup.CompressionOption = "1"
$dbBackup.SqlBackup($s)
# Write-Output $sPath
# MOVE THE FILE
Move-Item -Path $sPath -Destination $dPath;
# RESTORE THE DB
<#
THIS IS WHERE I'M NOT SURE OF WHAT TO DO
#>
}
}
Should I issue the RESTORE command from Server1, or should I drop a script on Server2 that accepts params, then call that script from Server1?
I'm not sure if this will play in, but the sizes on these baks are HUGE: 90-300gb
Space is another factor. That's why I'm doing the entire process 1 db at a time. There's not room on Server1 or Server2 to hold all of the baks at the same time, so I have to backup, move the bak, restore it, delete it. This brings me to another question. If I issue a restore command, followed by a Remove-Item command, with the Remove-Item wait for the Restore to complete, or will it immediately try to delete the bak after issuing the Restore?
September 17, 2018 at 9:33 am
Personally I would use Invoke-SQLCmd for the restore - it has a server option for the target server to run the process on. This waits until the SQL Server process is complete before continuing
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply