Restore Database With Partitions Excluded (or Selected Partitions)

--- Step 1: open this script and connect it to the instance you are going to restore the backup
use master
set nocount on
declare @Execute bit; set @Execute = 0  -- this one is Zero, if it's one, the script will perform restoration
-- Step 2: Enter backup information 
declare @StopAt datetime
select @StopAt = '2011-09-30 11:39:01.850'
select @StopAt = null
if object_id('tempdb..#Backups') is not null
	drop table #Backups
create table #Backups (BackupID int identity(1,1) primary key, Type varchar(50) check(Type in ('Full','Diff', 'Log')), FilePath nvarchar(max) )
insert into #Backups(Type, FilePath) values('Full', 'c:\temp\test\test.bak')
/*insert into #Backups(Type, FilePath) values('Diff', 'c:\temp\test\diff.bak')
insert into #Backups(Type, FilePath) values('Log', 'c:\temp\test\Log.bak')
insert into #Backups(Type, FilePath) values('Log', 'c:\temp\test\Log1.bak')
*/
declare @Backup nvarchar(max); 
select @Backup = FilePath from #Backups where Type = 'Full'
if @Backup is null
begin
	raiserror('No full backup specified', 16, 1)
	return
end
-- Step 3: Endter the place where you want to put the data file, end with "\"
declare @TargetPath nvarchar(max); set @TargetPath = 'c:\temp\test\Target\'
-- Step 4: Enter the differential backup file name and 

-- Step 5: Enter the database you want to create, if the database exists. 
--         Important Note: If the target database exists, it will be DROPped
declare @TargetDatabaseName sysname set @TargetDatabaseName = 'TestTarget'
declare @DisabledLogicalName table (LogicalName sysname)
-- Step 6: Ensure @Execute is ZERO and Run this script
--         It will list the partitions. find the partition you don't want to restore
--         Copy past the sql generated below, you can run it again to verify
-- Step 7:  Change @Execute to ONE, then run
select @Execute = 1
---put unwanted filegroup here.....start
insert into @DisabledLogicalName values('F2010')
---put unwanted filegroup here.....end
if OBJECT_ID('tempdb..#FilesInBackup') is not null
	drop table #FilesInBackup
create table #FilesInBackup(LogicalName nvarchar(128), PhysicalName nvarchar(max), Type nvarchar(10), FileGroupName nvarchar(128), Size numeric(30,0), MaxSize numeric(30,0), FileID int, CreateLNS numeric(30,0), DropLSN numeric(30,0), UniqueID uniqueidentifier, ReadOnlyLSN numeric(30,0), readWriteLSN numeric(30,0), BackupSizeInBytes numeric(30,0), SourceBlockSize numeric(30,0), FileGroupID int, LogGroupGUID uniqueidentifier, DifferentialBaseLSN numeric(30,0), DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit, TDEThumbPrint uniqueidentifier)
insert into #FilesInBackup
	exec sp_executesql N'restore filelistonly from disk = @Backup', N'@Backup nvarchar(max)', @Backup
if @Execute = 0
begin
	;with x as (
		select 
				left(PhysicalName, len(PhysicalName) - charindex('\', reverse(PhysicalName))+1) Path, 
				* 
		from #FilesInBackup
	)
	select x.FileGroupName, x.LogicalName, replace(x.PhysicalName, Path, '') PhysicalFileName ,  case when y.LogicalName is null then '' else 'Excluded' end IsExcluded ,'insert into @DisabledLogicalName values('''+x.LogicalName+''')' SQL
	from x
		left outer join @DisabledLogicalName y on x.LogicalName = y.LogicalName
	where x.FileGroupName is not null
	order by x.FileGroupName, x.LogicalName
	return
end

delete x from #FilesInBackup x where exists(select 1 from @DisabledLogicalName y where x.LogicalName = y.LogicalName)
declare @SQL nvarchar(max); set @SQL = '
alter database ' + quotename(@TargetDatabaseName) + ' set single_user with rollback immediate;
drop database ' + quotename(@TargetDatabaseName)
if DB_ID(@TargetDatabaseName) is not null
	exec(@SQL)
select @SQL = 'use master
restore database ' + quotename(@TargetDatabaseName) + ' ' + stuff((select ', File = ''' + LogicalName + '''' from #FilesInBackup where FileGroupName is not null for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') +'
from disk = ''' + @Backup + '''
with partial, norecovery, stats = 1
' +(select '
	, move ''' + LogicalName + ''' to '''+@TargetPath+ replace(PhysicalName, left(PhysicalName, len(PhysicalName) - charindex('\', reverse(PhysicalName))+1), '') +''''  from #FilesInBackup for xml path(''), type).value('.', 'nvarchar(max)') +';'
exec (@SQL)	
-- differential backup
select @Backup = null
select @Backup = FilePath from #Backups where Type = 'Diff'
if(@Backup is not null)
begin
	select @SQL = 'restore database ' + quotename(@TargetDatabaseName) + '  from disk = ''' + @Backup + ''' with norecovery, stats = 1'
	exec (@SQL)
end

--log backup
declare c cursor static for
	select FilePath from #Backups where Type = 'Log' order by BackupID
open c
fetch next from c into @Backup
while @@fetch_status = 0
begin
	select @SQL = 'restore log ' + quotename(@TargetDatabaseName) + ' from disk = ''' + @Backup + ''' with norecovery, stats = 1'  + isnull(',stopat=''' + convert(varchar(100), @StopAt, 121) + '''','')
	print @SQL
	begin try
		exec(@SQL)
	end try
	begin catch
		print error_message()
	end catch
	fetch next from c into @Backup
end
close c 
deallocate c
---recovery
select @SQL = 'begin try
restore database ' + quotename(@TargetDatabaseName) + ' with recovery;
end try
begin catch
	print error_message()
end catch'
exec (@SQL)


declare @Proc nvarchar(218); set @Proc = quotename(@TargetDatabaseName)+'..sp_executesql'
if object_id('tempdb..#Partitions') is not null
	drop table #Partitions
create table #Partitions(
							ObjectID int, SchemaName sysname, TableName sysname, 
							IndexID int, IndexName sysname, FileGroupName sysname, 
							Status sysname, PartitionSchemeName sysname, PartitionFunctionName sysname, 
							RangeType sysname, PartitionNumber int, ParameterID int, 
							Value sql_variant, DataType sysname)
insert into #Partitions
exec @Proc N'

select distinct 
		i.object_id ObjectID, OBJECT_SCHEMA_NAME(i.object_id) as SchemaName, OBJECT_NAME(i.object_id) TableName, 
		i.index_id IndexID, i.name as IndexName, fg.name FileGroupName, 
		df.state_desc Status, ps.name PartitionSchemeName, pf.name PartitionFunctionName, 
		case when pf.boundary_value_on_right = 1 then ''Right'' else ''Left'' end RangeType, dds.destination_id PartitionNumber, rv.parameter_id,
		rv.Value as Value, t.name DataType
from sys.partition_schemes ps
	inner join sys.partition_functions pf on ps.function_id = pf.function_id
	inner join sys.data_spaces ds on ds.data_space_id = ps.data_space_id
	inner join sys.indexes i on i.data_space_id = ds.data_space_id
	inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id and dds.destination_id <= pf.fanout
	inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
	inner join sys.database_files df on df.data_space_id = fg.data_space_id
	inner join sys.partition_parameters pp ON pp.function_id = pf.function_id
	inner join sys.types t ON (t.user_type_id = pp.system_type_id ) or (t.system_type_id = pp.system_type_id and t.user_type_id = pp.user_type_id) and (t.is_user_defined = 0) and (t.is_assembly_type = 1)
	left outer join sys.partition_range_values rv on rv.function_id = pf.function_id  and rv.boundary_id = dds.destination_id
	order by 7, 3,4, 11
'

if not exists(select * from #Partitions where status <> 'ONLINE') -- all filegroups are restored
	return
declare @PartitionNameSuffix nvarchar(50); set @PartitionNameSuffix =  replace(cast(NEWID() as nvarchar(50)), '-', '')
if object_id('tempdb..#DestinationPartition') is not null
	drop table #DestinationPartition
;with x as
(
	select distinct 
			p1.ObjectID, p1.IndexID, p1.SchemaName, p1.TableName,
			p1.PartitionFunctionName, p1.PartitionNumber, p1.DataType , 
			p1.Value, p1.RangeType, PartitionSchemeName, 
			FileGroupName, p1.ParameterID
	from #Partitions p1
	where p1.Status = 'ONLINE'
		and exists(select 1 from #Partitions p2 where p2.PartitionFunctionName = p1.PartitionFunctionName and p2.Status <> 'ONLINE')
		and p1.IndexID in (0,1)
), y as
(
	select distinct PartitionFunctionName, RangeType, PartitionSchemeName, SchemaName, TableName
	from x
)
select 
	SchemaName, TableName, y.PartitionSchemeName+@PartitionNameSuffix NewPartitionSchemeName, 
	'create partition function ' + quotename(y.PartitionFunctionName+@PartitionNameSuffix) +
	'(' + stuff((
					select ',' + x.DataType 
					from x 
					where x.PartitionFunctionName = y.PartitionFunctionName 
						and x.PartitionSchemeName = y.PartitionSchemeName  
						and x.ParameterID is not null
					group by x.PartitionFunctionName, x.DataType, x.ParameterID 
					order by ParameterID 
					for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') +	')'+
	' as range ' + RangeType + ' for values (' +
	stuff((select distinct ',' + case when x.DataType in( 'int', 'bigint', 'decimal', 'numeric', 'float', 'real', 'bit', 'money', 'tinyint', 'binary', 'varbinary', 'smallint', 'smallmoney', 'timestamp', 'image') then convert(nvarchar(200), x.Value, 1)
							else '''' + convert(nvarchar(200), x.Value, 121) + ''''
						end from x 
			where x.PartitionFunctionName = y.PartitionFunctionName 
				and x.PartitionSchemeName = y.PartitionSchemeName  
				and x.Value is not null
			for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') +
	')' as PartitionFunctionDefinition,
	'create partition scheme ' + quotename(y.PartitionSchemeName+@PartitionNameSuffix) + 
	' as partition ' + quotename(y.PartitionFunctionName+@PartitionNameSuffix) + ' TO (' +
	stuff((
			select ',' + quotename(x.FileGroupName) 
			from x 
			where x.PartitionFunctionName = y.PartitionFunctionName 
				and x.PartitionSchemeName = y.PartitionSchemeName 
			order by x.PartitionNumber for xml path(''), type
			).value('.', 'nvarchar(max)'), 1, 1, '') +
	')' as PartitionSchemeDefinition,
	cast(null as nvarchar(max))TableDefinition
	into #DestinationPartition
from y

declare @TableName sysname, @SchemaName sysname, @FullTableName sysname, @Definition nvarchar(max), @NewPartitionSchemeName sysname
declare c cursor static for
	select SchemaName, TableName, NewPartitionSchemeName
	from #DestinationPartition
open c
fetch next from c into @SchemaName, @TableName, @NewPartitionSchemeName
while @@fetch_status = 0
begin
	select @FullTableName = @SchemaName + '.' + @TableName
	exec @Proc
N'declare @NewTableName sysname; set @NewTableName = @ExistingTableName + @PartitionNameSuffix
select @Definition = ''create table '' + @NewTableName + '' ('' +
(select  quotename(col.name) + '' '' +
case t.name 
				when ''binary'' then t.name + case when col.max_length = -1 then ''(max)'' else ''('' + cast(case when t.name  in (''nvarchar'', ''nchar'') then col.max_length/2 else col.max_length end as varchar(20)) + '')'' end
				when ''varbinary'' then t.name + case when col.max_length = -1 then ''(max)'' else ''('' + cast(case when t.name  in (''nvarchar'', ''nchar'') then col.max_length/2 else col.max_length end as varchar(20)) + '')'' end
				when ''datetime2'' then t.name + ''(''+cast(col.scale  as varchar(20))+'')''
                when ''decimal'' then t.name + ''(''+cast(col.precision as varchar(20))+'',''+cast(col.scale  as varchar(20))+'')''
                when ''numeric'' then t.name + ''(''+cast(col.precision as varchar(20))+'',''+cast(col.scale  as varchar(20))+'')''
                when ''char'' then t.name + case when col.max_length = -1 then ''(max)'' else ''('' + cast(case when t.name  in (''nvarchar'', ''nchar'') then col.max_length/2 else col.max_length end as varchar(20)) + '')'' end
                when ''nchar'' then t.name + case when col.max_length = -1 then ''(max)'' else ''('' + cast(case when t.name  in (''nvarchar'', ''nchar'') then col.max_length/2 else col.max_length end as varchar(20)) + '')'' end
                when ''nvarchar'' then t.name + case when col.max_length = -1 then ''(max)'' else ''('' + cast(case when t.name  in (''nvarchar'', ''nchar'') then col.max_length/2 else col.max_length end as varchar(20)) + '')'' end
                when ''varchar'' then t.name + case when col.max_length = -1 then ''(max)'' else ''('' + cast(case when t.name  in (''nvarchar'', ''nchar'') then col.max_length/2 else col.max_length end as varchar(20)) + '')'' end
                when ''datetimeoffset'' then t.name + ''(''+cast(col.scale  as varchar(20))+'')''
                else 
					t.name
		end + case when col.is_nullable = 1 then '' null'' else '' not null'' end + 
		ISNULL( (select '' default '' + dc.definition from sys.default_constraints dc where dc.parent_object_id = col.object_id and dc.parent_column_id = col.column_id ) ,'''')
		+'',''
from sys.columns col 
	inner join sys.types t on t.user_type_id = col.user_type_id
where col.object_id = object_id(@ExistingTableName)
order by col.column_id
for xml path(''''), type).value(''.'', ''nvarchar(max)'')+
'' Constraint '' + (select name+@PartitionNameSuffix from sys.indexes where object_id = object_id(@ExistingTableName) and index_id in(0,1)) + '' primary key clustered ('' + stuff((select '',''+quotename(col_name(ix.object_id, ix.column_id)) from sys.index_columns ix where ix.index_id in(0,1) and ix.object_id = object_id(@ExistingTableName) order by key_ordinal for xml path(''''), type).value(''.'', ''nvarchar(max)''), 1, 1, '''') +  
'') on '' + @NewParitionName + ''('' + 
stuff((select '',''+quotename(col_name(object_id, column_id)) from sys.index_columns where index_id in(0,1) and object_id = object_id(@ExistingTableName) and partition_ordinal >0  order by key_ordinal  for xml path(''''), type).value(''.'', ''nvarchar(max)''), 1, 1,'''') + ''));
'' + 
isnull((select ''create '' + case when i.is_unique = 1 then ''unique'' else ''''end + '' index '' + i.name +'' on '' + @NewTableName + '' ('' +
		stuff((select '',''+quotename(col_name(ix.object_id, ix.column_id)) from sys.index_columns ix where ix.index_id =i.index_id and ix.object_id = object_id(@ExistingTableName) and partition_ordinal =0 order by ix.key_ordinal for xml path(''''), type).value(''.'', ''nvarchar(max)''), 1, 1, '''') + 
		'')'' + '' on '' + @NewParitionName + ''('' + stuff((select '',''+quotename(col_name(ix.object_id, ix.column_id)) from sys.index_columns ix where ix.index_id = i.index_id and ix.object_id = object_id(@ExistingTableName) and ix.partition_ordinal >0  order by key_ordinal  for xml path(''''), type).value(''.'', ''nvarchar(max)''), 1, 1,'''')  + '');
''
from sys.indexes i 
where i.object_id = object_id(@ExistingTableName) 
	and i.index_id > 1
	and i.is_disabled = 0 
	and exists(	select 1 
				from sys.index_columns ix1 
				where ix1.index_id =i.index_id 
					and ix1.object_id = i.object_id 
					and ix1.partition_ordinal > 0 )
for xml path(''''), type).value(''.'', ''nvarchar(max)''), '''')', 
		N'@ExistingTableName sysname, @PartitionNameSuffix sysname, @NewParitionName sysname, @Definition nvarchar(max) output', @FullTableName, @PartitionNameSuffix, @NewPartitionSchemeName, @Definition output
	update #DestinationPartition set TableDefinition = @Definition where SchemaName = @SchemaName and TableName = @TableName
	fetch next from c into @SchemaName, @TableName, @NewPartitionSchemeName
end
close c
deallocate c

select @SQL = (
				select PartitionFunctionDefinition +'
' + PartitionSchemeDefinition + '
' + TableDefinition 
from #DestinationPartition
for xml path(''), type).value('.', 'nvarchar(max)')
print @sql
exec @Proc @SQL

---switch partition
declare @ExistingTableName sysname, @NewTableName sysname, @TempTableName sysname, @ProcRename sysname 
set @ProcRename = @TargetDatabaseName + '..sp_rename'
declare c cursor static for
	select quotename(SchemaName)+'.'+quotename(TableName), quotename(SchemaName)+'.'+quotename(TableName+ @PartitionNameSuffix), TableName
	from #DestinationPartition
open c
fetch next from c into @ExistingTableName, @NewTableName, @TableName
while @@fetch_status = 0
begin
	select @SQL = ';with x as (select rv.Value, dds.destination_id boundary_id, fg.name
	from sys.partition_schemes ps
	inner join sys.partition_functions pf on ps.function_id = pf.function_id
	inner join sys.data_spaces ds on ds.data_space_id = ps.data_space_id
	inner join sys.indexes i on i.data_space_id = ds.data_space_id
	inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id and dds.destination_id <= pf.fanout 
	inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
	left join sys.partition_range_values rv on rv.function_id = pf.function_id  and rv.boundary_id = dds.destination_id
where i.object_id = object_id(@ExistingTableName) and i.index_id in(0,1)
), y as (
select distinct rv.Value, dds.destination_id boundary_id, fg.name
from sys.partition_schemes ps
	inner join sys.partition_functions pf on ps.function_id = pf.function_id
	inner join sys.data_spaces ds on ds.data_space_id = ps.data_space_id
	inner join sys.indexes i on i.data_space_id = ds.data_space_id
	 
	inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id and dds.destination_id <= pf.fanout
	inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
	left join sys.partition_range_values rv on rv.function_id = pf.function_id  and rv.boundary_id = dds.destination_id
where i.object_id = object_id(@NewTableName) and i.index_id in(0,1)
)
select @SQL = (select ''alter table ''+@ExistingTableName+'' switch partition ''+cast(x.boundary_id as varchar(20))+'' TO ''+@NewTableName+'' partition ''+cast(y.boundary_id as varchar(20))+'' ;''
from x
	inner join y on (x.Value is null and y.Value is null or x.Value = y.Value)
for xml path(''''), type).value(''.'', ''nvarchar(max)'')

'
	exec @Proc @SQL, N'@ExistingTableName sysname, @NewTableName sysname, @SQL nvarchar(max) output', @ExistingTableName, @NewTableName, @SQL output
	--select @sql
	exec @Proc @SQL
-- rename
	select @TempTableName = @TableName + @PartitionNameSuffix + 'temp'
	exec @ProcRename @ExistingTableName, @TempTableName
	exec @ProcRename @NewTableName, @TableName
	fetch next from c into @ExistingTableName, @NewTableName, @TableName
end
close c
deallocate c