prod - non prod database refersh script -Multiple datafiles

Question

Hi We have requirement of refresh the database from the production backup. following powershell is working for when the database with single datafiles . I am looking modifiy for multiple datafiles as failing . any one can help me please
Sql server 2016 Sp2 enterprise

script:

Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalLogFileName', NEWNAME='$TargetLogicalLogFileName')"

begin script configuration here

$TargetSqlServerInstance = "SQLDEST1" # target server instance
$TargetDb = "CRMDBTST" # target database
$BackupDir = "\sqlbackups\" # directory / share where backups are stored
$SourceLogicalDataFileName = "CRMPRD" # logical data file name of source db
$SourceLogicalLogFileName = "CRMPRD_log" # logical log file name of source db
$TargetLogicalDataFileName = "CRMDBTST" # logical name you want to change logical data file on target db to
$TargetLogicalLogFileName = "CRMDBTST_log" # logical name you want to change logical log file on target db to
$TargetPhysicalDataFileName = "U:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\CRMDBTST.mdf" # full path\file of target db physical data file
$TargetPhysicalLogFileName = "L:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\CRMDBTST_log.ldf" # full path\file of target db physical log file
$CompatLevel = 130 # compatibility level to set target database to (2019=150, 2017=140, 2016=130, #

import sqlserver module

Import-Module sqlserver
$SQLSERVER="SQLDEST1"
$databaseName="CRMDBTST"

latest full backup file name is dynamically determined and appended to backup directory

$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$FileToRestore = $BackupDir + '\' + $LatestFullBackupFile

kill any connections in target database

$KillConnectionsSql=
"
USE master
GO
ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
ALTER DATABASE $TargetDb SET MULTI_USER
GO
GO
"
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql

import sqlserver module

Import-Module sqlserver

restore

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalDataFileName", "$TargetPhysicalDataFileName")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalLogFileName", "$TargetPhysicalLogFileName")
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase

end restore

Answer 1

Hi @Ashwan ,

Sorry that I am not very good at PowerShell, and cannot help you to change the code.
But, as Dirk said, you can use the Powershell module dbatools, which can more easily and conveniently meet your requirements.
For example:

Install-Module dbatools  
  
Import-Module dbatools  
$TargetSqlServerInstance = ‘wx-80490’  
$TargetDb= ‘RefreshTest’  
$BackupFile = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\RefreshTest.bak’  
$DestinationDataDirectory = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA”  
$DestinationLogDirectory = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA"  
 Restore-DbaDatabase -SqlInstance $TargetSqlServerInstance -Path $BackupFile -DestinationDataDirectory $DestinationDataDirectory -DestinationLogDirectory  $DestinationLogDirectory -WithReplace -DatabaseName $TargetDb -ReplaceDbNameInFile  

In addition, you can add a windows-server-powershell tag to this thread so that people there will help you more effectively.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

Hi there,

why not utilize the powershell module dbatools? www.dbatools.io

So, once you have a backup file available you run
Restore-DbaDatabase with the appropriate parameters

https://docs.dbatools.io/#Restore-DbaDatabase

Are your data files all in the same location?
Then you just need to provide your destination path during restore

e.g.
Restore-Dbdadatabase -path "my backup file path" -DestinationDataDirectory "D:\datafiles\"

Answer 3

Hi Amelia Thats great . But my requirment is mutiple datafiles dynamically select the number of datafiles . Assume we add multiple files to production and then the restore script should work with no script modification.

thank you

Answer 4

Hi @Ashwan ,
For multiple datafiles, please add the name and directory of them in code.
For example:

# restores a full database backup to another database from source's latest full backup file in specified directory  
# begin script configuration here  
$TargetSqlServerInstance = "wx-80490"                                                                 # target server instance   
$TargetDb = "RefreshTest"                                                                                      # target database   
$BackupDir = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup"                                                                   
# directory / share where backups are stored  
$SourceLogicalDataFileName = "RefreshTest"   
$SourceLogicalDataFileName2= "RefreshTest2"                                        # the secondary logical data file name of source db   
$SourceLogicalLogFileName = "RefreshTest_log"                                      # logical log file name of source db  
$TargetLogicalDataFileName ="RefreshProd"  
$TargetLogicalDataFileName2= "RefreshProd2"                                       # the secondary  logical name you want to change logical data file on target db to  
$TargetLogicalLogFileName = "RefreshProd_log"                                  # logical name you want to change logical log file on target db to  
$TargetPhysicalDataFileName = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\RefreshProd.mdf”  
$TargetPhysicalDataFileName2 = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\RefreshProd2.ndf”  
$TargetPhysicalLogFileName = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\RefreshProd_log.ldf" # full path\file of target db physical log file   
$CompatLevel = 140                                                                                                     

# end script configuration here  
   
# import sqlserver module  
Import-Module sqlserver  
   
# latest full backup file name is dynamically determined and appended to backup directory  
$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1   
$FileToRestore = $BackupDir + '\' + $LatestFullBackupFile  
   
# kill any connections in target database  
$KillConnectionsSql=  
"  
USE master  
GO  
ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
GO  
USE master  
GO  
ALTER DATABASE $TargetDb SET MULTI_USER  
GO  
USE master  
GO  
"  
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql   
   
# import sqlserver module  
Import-Module sqlserver  
   
# restore  
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalDataFileName", "$TargetPhysicalDataFileName")  
$RelocateData2 = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalDataFileName2", "$TargetPhysicalDataFileName2")  
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalLogFileName",  "$TargetPhysicalLogFileName")  
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateData2,$RelocateLog) -ReplaceDatabase  
# end restore  
   
# rename logical files  
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalDataFileName', NEWNAME='$TargetLogicalDataFileName')"  
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalDataFileName2', NEWNAME='$TargetLogicalDataFileName2')"  
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalLogFileName', NEWNAME='$TargetLogicalLogFileName')"  

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November