Avatar of CBM Corporate
CBM Corporate
Flag for Australia

asked on 

Restoring a backup SQL database onto another SQL (test) database from *.bak file, and renaming the restored files to reflect name of test database

hi all,

I have a couple of SQL 2016 databases:
1st database is the production SQL database - called PRODUCTION
2nd database is the test database called PRODUCTION_TEST

I can backup the database through powershell, all is well, and it outputs a bak file.

the challenge for me is to restore that BAK file over the top of another database PRODUCTION_TEST
Yes I know I can do this through SQL Management Studio, but I don't want to, i need to script it for our own reasons.
The script is one that I have found earlier **see below**, and I need to modify it to do exactly what I want it to do.


# Set SQL Server instance name
$sqlName= "SERVERFQDN"
# Set new or existing databse name to restore backup
$dbname= "production_test"
# Set the existing backup file path
$backupPath= "UNCpath\dbackup.bak"
#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName
# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")
# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")
# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
# Call the SqlRestore mathod to complete restore database
Write-Host "...SQL Database"$dbname" Restored Successfully..."

Error I get when I run this script is:

"1" argument(s): "Restore failed for Server 'SERVERNAME'. " --->
  Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server
  'SERVERNAME'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception
  occurred while executing a Transact-SQL statement or batch. --->
  System.Data.SqlClient.SqlException: Logical file 'production_test' is not part of database
  'production_test'. Use RESTORE FILELISTONLY to list the logical file names.

thanks for all your help
Microsoft SQL ServerDatabasesMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
CBM Corporate

8/22/2022 - Mon