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
100 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 46

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 46

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 46

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 46

Expert Comment

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

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 77

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 77

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 46

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 77

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 77

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 46

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 77

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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

929 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