Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-06
21
Medium Priority
?
288 Views
Last Modified: 2016-09-11
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
0
Comment
Question by:CBM Corporate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 6
21 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41785688
You can't change the logical name. You must use the same logical name that comes from Production.
0
 

Author Comment

by:CBM Corporate
ID: 41785694
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
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41785700
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 )
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:CBM Corporate
ID: 41785714
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 ---
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41785718
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.
0
 

Author Comment

by:CBM Corporate
ID: 41785719
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"
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41785743
Correct. Hard code the logical file names and should be fine.
0
 
LVL 80

Expert Comment

by:arnold
ID: 41786147
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.
0
 

Author Comment

by:CBM Corporate
ID: 41789021
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..."
0
 
LVL 80

Expert Comment

by:arnold
ID: 41789032
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 ..............
0
 

Author Comment

by:CBM Corporate
ID: 41789033
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
0
 
LVL 80

Expert Comment

by:arnold
ID: 41789117
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?
0
 

Author Comment

by:CBM Corporate
ID: 41789133
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
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41789136
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.
0
 
LVL 80

Expert Comment

by:arnold
ID: 41789142
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,
0
 

Author Comment

by:CBM Corporate
ID: 41789170
@ 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
0
 
LVL 80

Expert Comment

by:arnold
ID: 41790506
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...
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41790800
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.
0
 
LVL 80

Expert Comment

by:arnold
ID: 41791176
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.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41791196
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.
0
 

Author Comment

by:CBM Corporate
ID: 41793664
hi all,

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question