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
82 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
  • 8
  • 7
  • 6
21 Comments
 
LVL 45

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 45

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
 

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 45

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 45

Expert Comment

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

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 76

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 76

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 45

Accepted Solution

by:
Vitor Montalvão earned 500 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 76

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 76

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 45

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 76

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now