March 17, 2022 at 7:03 pm
Hello,
I have a requirement to restore database using powershell with multiple backup files.
I think the below query prints out what needs to be executed but i am unsure of how to use invoke-sqlcmd and execute everything together.
Appreciate your help
$backupRoot = Get-ChildItem -Path "\\<network shared drive\"
$dbname = 'Database1'
$server = 'server1'
$query0 = "USE [master] RESTORE DATABASE $dbname"
write-host $query0
foreach($folder in $backupRoot)
{
# Get the most recent .bak files for all databases...
$backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*$dbname*.bak" -Recurse | Sort-Object -Property CreationTime | Select-Object -First 1
# For each .bak file...
foreach ($backupFile in $backupFiles)
{
$query1 = "FROM DISK = N'"+$backupFile.FullName+"'"
write-host $query1
#Invoke-Sqlcmd -ServerInstance $server -Query $query
}
}
$query2 = "WITH FILE = 1,
MOVE N'INR_Data' TO N'G:\Database\$dbname.mdf', MOVE N'INR_Log' TO N'L:\Log\$dbname.ldf',
NOUNLOAD, STATS = 5
GO"
write-host $query2
March 18, 2022 at 8:05 am
In this day and age I wouldn't even consider writing my own routines for SQL in PowerShell, I'd just immediately go for DBATools instead, as there is no point re-inventing the wheel for something which already does nearly everything you could want.
dbatools docs | Restore-DbaDatabase
Restore-DbaDatabase -SqlInstance server\instance1 -Path c:\backups -DatabaseName example1
March 18, 2022 at 8:08 am
indeed, nowadays DBATOOLS is the way to go!
https://docs.dbatools.io/Restore-DbaDatabase
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply