Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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.



*********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
CBM Corporate
Asked:
CBM Corporate
  • 8
  • 7
  • 6
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't change the logical name. You must use the same logical name that comes from Production.
0
 
CBM CorporateAuthor 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
CBM CorporateAuthor 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 ---
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
CBM CorporateAuthor 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"
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Correct. Hard code the logical file names and should be fine.
0
 
arnoldCommented:
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
 
CBM CorporateAuthor 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..."
0
 
arnoldCommented:
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
 
CBM CorporateAuthor 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
0
 
arnoldCommented:
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
 
CBM CorporateAuthor 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
arnoldCommented:
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
 
CBM CorporateAuthor 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
0
 
arnoldCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
arnoldCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
CBM CorporateAuthor Commented:
hi all,

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now