Powershell script to restore all database

JosephKing

so i am trying to create a script that will take all of the backups we have and restore them to a new server for disaster recovery purposes.  but it's not working.

import-module sqlps

#update path for to actual backup location

$path = "D:\Temp"

$logPath = "D:\Log" #change to proper log path when running

$backupPath = "D:\backup" # shouldn't need but putting here just encase

$server = "server1"

$database = 'DBname'

#$database = gc( 'C:\Database Recovery Script\'+$server+' Database list.txt' )

#$dataFileLocation = "D:\SqlData\$data.mdf"

$password = 'xxxxxxx'

foreach ($data in $database)

{

#For testing use the QBW filter

#Once testing is over swithc to proper filter.

$filter = '*server1_DBname_FULLL*'

#$filter = '*_'+$Server+'_'+$data+'_FULL*'

$latest = get-childitem -Path $path -Filter $filter |

    where-object { -not $_.PSIsContainer } |

    sort-object -Property $_.CreationTime |

    Select-Object -first 3

$latest.fullname

$dataFileLocation = "D:\SqlData\$data.mdf"

$logFileLocation = "L:\SqlLogs\$data.ldf"

$sql = @"

USE [master]

RESTORE DATABASE "$data" `

FROM DISK = N'$latest.fullname' `

WITH FILE = 1, `

     MOVE N'CurrentDB' TO N'$dataFileLocation', `

     MOVE N'CurrentDB_log' TO N'$logFileLocation', `

     NOUNLOAD, NORECOVERY, REPLACE, STATS = 5, PASSWORD = '$password' ` `

 invoke-sqlcmd -ServerInstance "localhost\tedbdev01" -Query $sql -Username "xxxx" -Password "xxxx"

}

When i run the sql in mssql manager i get an error which i believe is because of the fact that redgate took the backup and it's not a native sql.  Is there a way to script this properly so that it works.
Msg 3241, Level 16, State 0, Line 4
The media family on device 'D:\Temp\20191020_045728_AWSCSQL02_DB21387_FULL.sqb'
is incorrectly formed. SQL Server cannot process this media family.

petey2

I think there is an error in the 'D:\Temp\20191020_045728_AWSCSQL02_DB21387_FULL.sqb' file.  Could you try restoring from another backup file?

JosephKing

I have tried this with roughly 15 other files.  All taken with redgate backup.  If i take a backup manually it will work it seems that this is only with redgate taken backup.

petey2

Which SQL Server version were the backups taken from, and which version are they being restored to?

JosephKing

They were taken from a 2014 server and being put back on a 2014 server.

petey2

If you took a full backup of any database from the source using SQL Backup with the minimum of options e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE [database name] TO DISK = [backup file name]"'

and restored this backup on the destination server, is the same error raised?

If not, could you then try incrementally adding the other options used in the original backups until the restore fails?  When that restore fails, could you please post the contents of the backup and restore logs?  The default folder for the logs is C:\ProgramData\Red Gate\SQL Backup\Log\<instance name>

JosephKing

Petey2,  thanks for helping with this over the weekend i did some digging in logs and found that there was an exe that was used to take and restore backups on the redgate servers sqlbackupc.exe and i have been trying to use this but geting other errors now.  I will work with this and see if i can't get it to work.