Otras notas de portada.

In this recipe, we will perform an online piecemeal restore.

Getting ready

We will use a test database called StudentDB database, which has three fi legroups—one primary, two custom fi legroups FG1 and FG2—in this recipe. Each of FG1 and FG2 will have one secondary datafi le stored in the C:\Temp folder.

You can use the script 6464 - Ch05 - 11 - Perform an Online PieceMeal Restore - Prep.ps1 to create your fi les, which is included in the downloadable fi les for this book. When the script has fi nished executing, you should see the following database:

This is how the tables will be structured:

Table Filegroup Datafile name Datafile location

Student_PRIMARY PRIMARY StudentDB.mdf Default data

directory Student_FG1 FG1 Student_FG1_ data C:\Temp Student_FG2 FG2 Student_FG2_ data C:\Temp

Student_TXN PRIMARY StudentDB.mdf Default data

directory

For our recipe, we will restore only the PRIMARY fi legroup, and fi legroup FG2 to our second SQL Server instance KERRIGAN\SQL01. At the end of our task, only Student_PRIMARY and Student_FG2 tables will be accessible.

Feel free to substitute this with a database available in your development environment that already has separate fi legroups and fi legroup backups.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module as follows:

#import SQL Server module

Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

$instanceName = "KERRIGAN\SQL01"

$server = New-Object -TypeName Microsoft.SqlServer.Management. Smo.Server -ArgumentList $instanceName

#let's timestamp our databasename

#this is strictly for testing and checking purposes $timestamp = Get-Date -Format yyyyMMddHHmmss

$restoreddbname = "StudentDBRestored_$($timestamp)"

$relocatePath = "C:\Program Files\Microsoft SQL Server\MSSQL11. SQL01\MSSQL\DATA"

#for this piecemeal restore, we need to specify #files to restore

#primary filegroup

$primaryfgbackup = "C:\Backup\StudentDB_PRIMARY.bak"

#additional filegroup(s) to restore, and filegroup name $fg2backup = "C:\Backup\StudentDB_FG2.bak"

$fg2name = "Student_FG2_data"

#transaction log backup

$txnbackup = "C:\Backup\StudentDB_TXN.bak"

#========================================= #primary fg

#=========================================

#because we want to restore to a different instance, #we need to create an array of files which will #contain the new file locations of data and log #files in the primary filegroup

$relocateFileList = @()

$smoRestore = New-Object Microsoft.SqlServer.Management.Smo. Restore

$smoRestore.Devices.AddDevice($primaryfgbackup , [Microsoft. SqlServer.Management.Smo.DeviceType]::File)

$smoRestore.ReadFileList($server) | ForEach-Object {

$relocateFile = Join-Path $relocatePath (Split-Path $_.PhysicalName -Leaf)

$relocateFileList += New-Object Microsoft.SqlServer. Management.Smo.RelocateFile($_.LogicalName, $relocateFile) }

#========================================= #restore primary fg

#needs to be only mdf and ldf #========================================= Restore-SqlDatabase ` -Partial ` -ReplaceDatabase ` -ServerInstance $instanceName ` -Database $restoreddbname ` -BackupFile $primaryfgbackup ` -RelocateFile $relocateFileList ` -NoRecovery #========================================= #fg2 #========================================= $relocateFileList = @()

#for the custom filegroup we want to restore, we want to #relocate only that filegroup's datafiles

$smoRestore = New-Object Microsoft.SqlServer.Management.Smo. Restore

$smoRestore.Devices.AddDevice($fg2backup , [Microsoft.SqlServer. Management.Smo.DeviceType]::File)

$smoRestore.ReadFileList($server) | ForEach-Object {

if($_.LogicalName -eq $fg2name) {

$relocateFile = Join-Path $relocatePath (Split-Path $_.PhysicalName -Leaf)

$relocateFileList += New-Object Microsoft.SqlServer. Management.Smo.RelocateFile($_.LogicalName, $relocateFile) }

}

#========================================= #restore fg2

#dont need partial anymore

#========================================= Restore-SqlDatabase ` -ReplaceDatabase ` -ServerInstance $instanceName ` -Database $restoreddbname ` -BackupFile $fg2backup ` -RelocateFile $relocateFileList ` -NoRecovery

#========================================= #restore transaction log backup

#this will restore using with recovery #========================================= Restore-SqlDatabase ` -ReplaceDatabase ` -ServerInstance $instanceName ` -Database $restoreddbname ` -BackupFile $txnbackup

How it works...

Online piecemeal restore is an Enterprise feature available starting from SQL Server 2005. This type of restore, also referred to as partial restore, allows you to stage your restores. With each restore sequence, one or more fi legroups are available online, leaving the rest offl ine. The power of this feature is that as soon as your fi rst fi legroup is restored, the objects you have in this fi legroup already become accessible to your end users or applications. The fi rst thing you will need to do is line up your fi les. You will need to specify where the PRIMARY fi legroup backup, any user fi legroups you want to restore, and the transaction log backup fi les are. In our recipe, we are also restoring the database to a different instance, so we will need to relocate our database fi les. For this reason, we must also specify what the fi legroup names are for the fi legroups we are restoring.

#primary filegroup

$primaryfgbackup = "C:\Backup\StudentDB_PRIMARY.bak"

#additional filegroup(s) to restore, and filegroup name $fg2backup = "C:\Backup\StudentDB_FG2.bak"

$fg2name = "Student_FG2_data"

#transaction log backup

$txnbackup = "C:\Backup\StudentDB_TXN.bak"

Once we have the files lined up, we need to create an array that contains the files we are relocating:

$relocateFileList = @()

$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore $smoRestore.Devices.AddDevice($primaryfgbackup , [Microsoft.SqlServer. Management.Smo.DeviceType]::File)

$smoRestore.ReadFileList($server) | ForEach-Object {

$relocateFile = Join-Path $relocatePath (Split-Path $_.PhysicalName -Leaf)

$relocateFileList += New-Object Microsoft.SqlServer. Management.Smo.RelocateFile($_.LogicalName, $relocateFile) }

We can then use our Restore-SqlDatabase cmdlet to restore the primary fi legroup fi rst with NORECOVERY. Note that when restoring the PRIMARY fi legroup, you will need to specify the option Partial:

#========================================= #restore primary fg

#partial must be used if restoring primary fg #needs to be only mdf and ldf

#========================================= Restore-SqlDatabase ` -Partial ` -ReplaceDatabase ` -ServerInstance $instanceName ` -Database $restoreddbname ` -BackupFile $primaryfgbackup ` -RelocateFile $relocateFileList ` -NoRecovery

Next, for our user fi legroups, we must still create an array that contains the specifi c fi lenames of the fi legroup(s) we are restoring.

$relocateFileList = @()

#for the custom filegroup we want to restore, we want to #relocate only that filegroup's datafiles

$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore $smoRestore.Devices.AddDevice($fg2backup , [Microsoft.SqlServer. Management.Smo.DeviceType]::File)

$smoRestore.ReadFileList($server) | ForEach-Object {

if($_.LogicalName -eq $fg2name)

{

$relocateFile = Join-Path $relocatePath (Split-Path $_.PhysicalName -Leaf)

$relocateFileList += New-Object Microsoft.SqlServer. Management.Smo.RelocateFile($_.LogicalName, $relocateFile) }

If we add items in the array that pertain to fi legroups that we are not restoring, we are going to get an error like this:

Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient. SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on ... 'c:\\Temp\\Student_FG1_data.ndf'

Once we have the array of relocated fi les, we can restore our user fi legroup. Note that for this statement, we no longer need to specify the option Partial:

#========================================= #restore fg2

#dont need partial anymore

#========================================= Restore-SqlDatabase ` -ReplaceDatabase ` -ServerInstance $instanceName ` -Database $restoreddbname ` -BackupFile $fg2backup ` -RelocateFile $relocateFileList ` -NoRecovery

Lastly, we need to restore the transaction logfi le(s). If there are multiple transaction logfi les, each transaction logfi le before the fi nal transaction logfi le needs to be restored with NORECOVERY. The last transaction logfi le can be restored using WITH RECOVERY.

#========================================= #restore transaction log backup

#========================================= Restore-SqlDatabase ` -ReplaceDatabase ` -ServerInstance $instanceName ` -Database $restoreddbname ` -BackupFile $txnbackup

What you should see after you restore this sequence is shown in the following screenshot:

It is a little bit deceiving because it looks like the whole database is already available and accessible. However, since we only restored FG2, only objects in FG2 are truly accessible. If you try to access any of the objects that reside in the unrestored fi legroup, you will get an error similar to this:

Msg 8653, Level 16, State 1, Line 2

The query processor is unable to produce a plan for the table or view 'Student_FG1' because the table resides in a filegroup which is not online.

To restore the rest of your fi legroups, you can use the same steps as described previously until the fi nal fi legroup is restored. Remember to always restore the fi legroup, and then the transaction log backup. Lather, rinse, and repeat.

See also

 The Creating a fi legroup backup recipe

 Learn more about performing piecemeal restores:

http://msdn.microsoft.com/en-us/library/ms177425(v=sql.110). aspx