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

CBM Corporate
CBM Corporate used Ask the Experts™
on
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.



*********SCRIPT**********

# 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"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)
 
# Call the SqlRestore mathod to complete restore database
$dbRestore.SqlRestore($sqlServer)
 
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You can't change the logical name. You must use the same logical name that comes from Production.

Author

Commented:
hi Vitor,

Ok, so that means the database files will be named production as opposed to production_test ?
That is ok, provided we naturally output the files in a different location than the originals.

Can you please go through my script and advise what needs to be modified ?

CBM
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I think you just need to get rid of these two lines:
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreLog.LogicalFileName = $dbname + "_Log"


NOTE: After Restore you can change the logical name of the files with an ALTER DATABASE command:
ALTER DATABASE production_test
MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

Author

Commented:
Error being received:

System.Management.Automation.MethodInvocationException: Exception calling "SqlRestore" with
1" argument(s): "Restore failed for Server 'SERVERNAME'. " --->
icrosoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server
SERVERNAME'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception
ccurred while executing a Transact-SQL statement or batch. --->
ystem.Data.SqlClient.SqlException: Logical file '' is not part of database 'production_test'.
se RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.
  at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction
ction, Object execObject, DataSet fillDataSet, Boolean catchException)
  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
qlCommand, ExecutionTypes executionType)
  --- End of inner exception stack trace ---
  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
qlCommand, ExecutionTypes executionType)
  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection
qlCommands, ExecutionTypes executionType)
  at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server,
tringCollection queries)
  at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
  --- End of inner exception stack trace ---
  at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
  at CallSite.Target(Closure , CallSite , Object , Object )
  --- End of inner exception stack trace ---
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
My bad.
Isn't for delete those rows but provide the correct logical names instead:
$dbRestoreFile.LogicalFileName = "Production logical name for mdf"
$dbRestoreLog.LogicalFileName = "Production logical name for ldf"


You may hardcode the names or retrieve them from the BAK file.

Author

Commented:
sorry confused with your statement
"isn't for delete those rows......."

so do i still leave these two commented out ?
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreLog.LogicalFileName = $dbname + "_Log"

and simply modify these with hard coded file names for the restored db ????
$dbRestoreFile.LogicalFileName = "Production logical name for mdf"
$dbRestoreLog.LogicalFileName = "Production logical name for ldf"
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Correct. Hard code the logical file names and should be fine.
Distinguished Expert 2017

Commented:
Please refer to the tsql in https://msdn.microsoft.com/en-us/library/ms186858.aspx, adding the move parameter to the restore directive, relocates/renames for the restored db.
I beleive a tsql can be run that in one step restores a database under a new name with new file names  from backup.

Author

Commented:
hi all,

no luck.
The backup of the DB works perfectly, however the restore **see below** fails with error:
PLS HELP !

System.Management.Automation.MethodInvocationException: Exception calling "SqlRestore" with "1"
 argument(s): "Restore failed for Server 'SERVERFQDN'. " ---> Microsoft.SqlServer.Management.Smo.Fa
iledOperationException: Restore failed for Server 'SERVERFQDN'.  ---> Microsoft.SqlServer.Managemen
t.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statem
ent or batch. ---> System.Data.SqlClient.SqlException: Logical file '\\SERVERFQDN\db\production_train1.
mdf' is not part of database 'production_train'. Use RESTORE FILELISTONLY to list the logical file
names.
RESTORE DATABASE is terminating abnormally.

****SCRIPT*****
# Set SQL Server instance name
$sqlName= "SERVERFQDN"
 
# Set new or existing databse name to restore backup
$dbname= "PRODUCTION_TRAIN"
 
# Set the existing backup file path
$backupPath= "\\SERVERFQDN\db\PRODUCTION.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 = "\\SERVERFQDNl\db\production_train1.mdf"
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_1Data.mdf"
$dbRestoreLog.LogicalFileName = "\\SERVERFQDN\db\production_train1.ldf"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_1Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)
 
# Call the SqlRestore mathod to complete restore database
$dbRestore.SqlRestore($sqlServer)
 
Write-Host "...SQL Database"$dbname" Restored Successfully..."
Distinguished Expert 2017

Commented:
Where are you restoring it in relation to tge backup you take? Do tge users that are part of the db security exist on the server where you are restoring it!  See Ms SQL login transfer, deals with creating users while maintaining the Sid references which is what is referenced within the DBsecurity ..............

Author

Commented:
hi
im restoring to the same SQL server / different DB
PRODUCTION DB is backed up
and then I need to script the restore of this DB onto PRODUCTION_TRAIN
Initially backups get dumped onto UNC path, and im restoring from this path.

Permissions arent a problem, user is DBO, and domain admin account is DBO for both, and the same error occurs for both.
W
Distinguished Expert 2017

Commented:
You are not using the MOVE option to indicate that you are relocating/relabeling the filenames for the restore.
please note the reference the the TSQL restore, the Physical/Logical posted in a prior post must match the data in the bakup file.

Scripting and testing restore of a Production DB on the Production server while you are trying to rename it is a very dangerous practice, testing scripts should always be done on a system where an error will not have any impact.

Try running the TSQL command to perform the restore, once that works as you expect, translate the working TSQL to the Powershell.

instead of actually executing the commands, echo out what the command would be

i.e. what is the data in $dbRestoreLog.PhysicalFileName

The powershell code you are trying seems to be for SQL 2012/2014/2016.
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.sqlrestore.aspx
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(v=sql.110).aspx

powershell, tsql using osql.exe?

Author

Commented:
Thanks Arnold,

Ok, not really a TSQL guru, nor SQL scripting guru.. :-)

The server is SQL2016.

I take your comments on board re "production vs test... no need to preach to the converted.
Will spend some time looking at the TSQL side of things to echo out the code.

Keep you appraised of progress !

C
IT Engineer
Distinguished Expert 2017
Commented:
You still insisting in renaming the Logical files names and that's NOT POSSIBLE during restore operation.
$dbRestoreFile.LogicalFileName = "\\SERVERFQDNl\db\production_train1.mdf"
$dbRestoreLog.LogicalFileName = "\\SERVERFQDN\db\production_train1.ldf"

And btw, those aren't logical names that you provided but their location. You should replace those values with the original ones, like I said before. Hardcoded it to be more easy.
Distinguished Expert 2017

Commented:
I am uncertain which you are trying to use,
See if the link below helps you along.

https://technet.microsoft.com/en-us/library/mt683379.aspx
Has different examples,

Author

Commented:
@ Arnold

What we are using:

Powershell Script, which backs up Database to UNC path, and another script to restore the backup to a different database.

Sql2016, and well the rest is self explanatory.. see above posts

W
Distinguished Expert 2017

Commented:
try the TSQL on a test server to restore from backup whilenaming the database NEWDB with renamed files.

you can then use that with powershell executing osql.exe with the TSQL restore command.

the other options as Vitor pointed out, restore the database as is on the second system, and run alter database chaging its name....
Note if you have functions, procedures, etc. that explicitly reference database name, those will stop working...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
and run alter database chaging its name....
Only the database logical filenames. The database name and physical file names and locations can be configured during the restore.
Distinguished Expert 2017

Commented:
Vitor, at this stage the restore script need only confirm that a straight forward restore with no changes works.
Once that works, the other should as well.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Not necessary. Depends on the requirement. And it's easy to perform it at once during the Restore.
Only file logical names aren't possible to be changed during restore (pitty, I may say) so there's no other way to be performed in a 2nd step.

Author

Commented:
hi all,

As per Vitor's advise, I have modified the script and resolved the issue.
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial