December 9, 2015 at 2:47 am
For a Long time we have been using the following script to create a Schema only copy of our database for testing
$Filepath='C:\Temp\' # local directory to save build-scripts to
$DataSource='ServerName' # server name and instance
$Database='DatabaseName'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
}
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $true; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename = "$($FilePath)\$($Database)_Build.sql";
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]
$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"All done"
which we copied from https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/
all went very well until we introduced CDC and built some views that look at the CDC tables.
The powershell script will create the script for the view, but ignores the CDC table it Looks at. (I'm guessing because it thinks they are sys.table and handled by the database).
My question is
Can CDC tables be scripted through powershell / smo?
Many thanks in advance
Ian
December 11, 2015 at 1:10 pm
Maybe this?
$CreationScriptOptions.ChangeTracking = $true
December 14, 2015 at 3:08 am
Thanks, but alas I'm still missing the CDC tables in the script.
Here is an example database , with one table and a view that looks at the CDC table.
USE [master]
GO
CREATE DATABASE [ScriptCDCTest]
GO
Use [ScriptCDCTest]
go
create table Tbl1 (ColID int identity ( 1,1) primary key
, Col1 int
,col2 int)
go
EXEC sys.sp_cdc_enable_db
go
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Tbl1',
@role_name = N'Public',
@supports_net_changes = 1
GO
Alter table [cdc].[dbo_Tbl1_CT]
add ChangeTime datetime default getdate()
go
Create View Tbl1_Changes
as
select Colid,Col1,Col2,ChangeTime
from [cdc].[dbo_Tbl1_CT]
[ScriptCDCTest]
I then run our SMO script via powershell
$filepath = 'C:\Temp\'
$DataSource='ServerName' # server name and instance
$Database='ScriptCDCTest'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
}
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.WithDependencies = $true # trying to capture CDC tables
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $true; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.ChangeTracking = $true # get those CDC tables in there
$CreationScriptOptions.Filename = "$($FilePath)\$($Database)_Build.sql";
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]
$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"All done"
and get the following script back
/****** Object: User [cdc] Script Date: 14.12.2015 10:47:29 ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'cdc')
CREATE USER [cdc] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[cdc]
GO
/****** Object: Schema [cdc] Script Date: 14.12.2015 10:47:29 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'cdc')
EXEC sys.sp_executesql N'CREATE SCHEMA [cdc]'
GO
/****** Object: Table [dbo].[Tbl1] Script Date: 14.12.2015 10:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tbl1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Tbl1](
[ColID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [int] NULL,
[col2] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ColID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: UserDefinedFunction [cdc].[fn_cdc_get_all_changes_dbo_Tbl1] Script Date: 14.12.2015 10:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cdc].[fn_cdc_get_all_changes_dbo_Tbl1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
create function [cdc].[fn_cdc_get_all_changes_dbo_Tbl1]
(@from_lsn binary(10),
@to_lsn binary(10),
@row_filter_option nvarchar(30)
)
returns table
return
select NULL as __$start_lsn,
NULL as __$seqval,
NULL as __$operation,
NULL as __$update_mask, NULL as [ColID], NULL as [Col1], NULL as [col2]
where ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 0)
union all
select t.__$start_lsn as __$start_lsn,
t.__$seqval as __$seqval,
t.__$operation as __$operation,
t.__$update_mask as __$update_mask, t.[ColID], t.[Col1], t.[col2]
from [cdc].[dbo_Tbl1_CT] t with (nolock)
where (lower(rtrim(ltrim(@row_filter_option))) = ''all'')
and ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1)
and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4)
and (t.__$start_lsn <= @to_lsn)
and (t.__$start_lsn >= @from_lsn)
union all
select t.__$start_lsn as __$start_lsn,
t.__$seqval as __$seqval,
t.__$operation as __$operation,
t.__$update_mask as __$update_mask, t.[ColID], t.[Col1], t.[col2]
from [cdc].[dbo_Tbl1_CT] t with (nolock)
where (lower(rtrim(ltrim(@row_filter_option))) = ''all update old'')
and ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1)
and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4 or
t.__$operation = 3 )
and (t.__$start_lsn <= @to_lsn)
and (t.__$start_lsn >= @from_lsn)
'
END
GO
/****** Object: UserDefinedFunction [cdc].[fn_cdc_get_net_changes_dbo_Tbl1] Script Date: 14.12.2015 10:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cdc].[fn_cdc_get_net_changes_dbo_Tbl1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
create function [cdc].[fn_cdc_get_net_changes_dbo_Tbl1]
(@from_lsn binary(10),
@to_lsn binary(10),
@row_filter_option nvarchar(30)
)
returns table
return
select NULL as __$start_lsn,
NULL as __$operation,
NULL as __$update_mask, NULL as [ColID], NULL as [Col1], NULL as [col2]
where ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 0)
union all
select __$start_lsn,
case __$count_03F93619
when 1 then __$operation
else
case __$min_op_03F93619
when 2 then 2
when 4 then
case __$operation
when 1 then 1
else 4
end
else
case __$operation
when 2 then 4
when 4 then 4
else 1
end
end
end as __$operation,
null as __$update_mask , [ColID], [Col1], [col2]
from
(
select t.__$start_lsn as __$start_lsn, __$operation,
case __$count_03F93619
when 1 then __$operation
else
(select top 1 c.__$operation
from [cdc].[dbo_Tbl1_CT] c with (nolock)
where ( (c.[ColID] = t.[ColID]) )
and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
and (c.__$start_lsn <= @to_lsn)
and (c.__$start_lsn >= @from_lsn)
order by c.__$seqval) end __$min_op_03F93619, __$count_03F93619, t.[ColID], t.[Col1], t.[col2]
from [cdc].[dbo_Tbl1_CT] t with (nolock) inner join
(select r.[ColID], max(r.__$seqval) as __$max_seqval_03F93619,
count(*) as __$count_03F93619
from [cdc].[dbo_Tbl1_CT] r with (nolock)
where (r.__$start_lsn <= @to_lsn)
and (r.__$start_lsn >= @from_lsn)
group by r.[ColID]) m
on t.__$seqval = m.__$max_seqval_03F93619 and
( (t.[ColID] = m.[ColID]) )
where lower(rtrim(ltrim(@row_filter_option))) = N''all''
and ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)
and (t.__$start_lsn <= @to_lsn)
and (t.__$start_lsn >= @from_lsn)
and ((t.__$operation = 2) or (t.__$operation = 4) or
((t.__$operation = 1) and
(2 not in
(select top 1 c.__$operation
from [cdc].[dbo_Tbl1_CT] c with (nolock)
where ( (c.[ColID] = t.[ColID]) )
and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
and (c.__$start_lsn <= @to_lsn)
and (c.__$start_lsn >= @from_lsn)
order by c.__$seqval
)
)
)
)
) Q
union all
select __$start_lsn,
case __$count_03F93619
when 1 then __$operation
else
case __$min_op_03F93619
when 2 then 2
when 4 then
case __$operation
when 1 then 1
else 4
end
else
case __$operation
when 2 then 4
when 4 then 4
else 1
end
end
end as __$operation,
case __$count_03F93619
when 1 then
case __$operation
when 4 then __$update_mask
else null
end
else
case __$min_op_03F93619
when 2 then null
else
case __$operation
when 1 then null
else __$update_mask
end
end
end as __$update_mask , [ColID], [Col1], [col2]
from
(
select t.__$start_lsn as __$start_lsn, __$operation,
case __$count_03F93619
when 1 then __$operation
else
(select top 1 c.__$operation
from [cdc].[dbo_Tbl1_CT] c with (nolock)
where ( (c.[ColID] = t.[ColID]) )
and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
and (c.__$start_lsn <= @to_lsn)
and (c.__$start_lsn >= @from_lsn)
order by c.__$seqval) end __$min_op_03F93619, __$count_03F93619,
m.__$update_mask , t.[ColID], t.[Col1], t.[col2]
from [cdc].[dbo_Tbl1_CT] t with (nolock) inner join
(select r.[ColID], max(r.__$seqval) as __$max_seqval_03F93619,
count(*) as __$count_03F93619,
[sys].[ORMask](r.__$update_mask) as __$update_mask
from [cdc].[dbo_Tbl1_CT] r with (nolock)
where (r.__$start_lsn <= @to_lsn)
and (r.__$start_lsn >= @from_lsn)
group by r.[ColID]) m
on t.__$seqval = m.__$max_seqval_03F93619 and
( (t.[ColID] = m.[ColID]) )
where lower(rtrim(ltrim(@row_filter_option))) = N''all with mask''
and ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)
and (t.__$start_lsn <= @to_lsn)
and (t.__$start_lsn >= @from_lsn)
and ((t.__$operation = 2) or (t.__$operation = 4) or
((t.__$operation = 1) and
(2 not in
(select top 1 c.__$operation
from [cdc].[dbo_Tbl1_CT] c with (nolock)
where ( (c.[ColID] = t.[ColID]) )
and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
and (c.__$start_lsn <= @to_lsn)
and (c.__$start_lsn >= @from_lsn)
order by c.__$seqval
)
)
)
)
) Q
union all
select t.__$start_lsn as __$start_lsn,
case t.__$operation
when 1 then 1
else 5
end as __$operation,
null as __$update_mask , t.[ColID], t.[Col1], t.[col2]
from [cdc].[dbo_Tbl1_CT] t with (nolock) inner join
(select r.[ColID], max(r.__$seqval) as __$max_seqval_03F93619
from [cdc].[dbo_Tbl1_CT] r with (nolock)
where (r.__$start_lsn <= @to_lsn)
and (r.__$start_lsn >= @from_lsn)
group by r.[ColID]) m
on t.__$seqval = m.__$max_seqval_03F93619 and
( (t.[ColID] = m.[ColID]) )
where lower(rtrim(ltrim(@row_filter_option))) = N''all with merge''
and ( [sys].[fn_cdc_check_parameters]( N''dbo_Tbl1'', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)
and (t.__$start_lsn <= @to_lsn)
and (t.__$start_lsn >= @from_lsn)
and ((t.__$operation = 2) or (t.__$operation = 4) or
((t.__$operation = 1) and
(2 not in
(select top 1 c.__$operation
from [cdc].[dbo_Tbl1_CT] c with (nolock)
where ( (c.[ColID] = t.[ColID]) )
and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
and (c.__$start_lsn <= @to_lsn)
and (c.__$start_lsn >= @from_lsn)
order by c.__$seqval
)
)
)
)
'
END
GO
/****** Object: View [dbo].[Tbl1_Changes] Script Date: 14.12.2015 10:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Tbl1_Changes]'))
EXEC dbo.sp_executesql @statement = N'Create View Tbl1_Changes
as
select Colid,Col1,Col2,ChangeTime
from [cdc].[dbo_Tbl1_CT]'
GO
/****** Object: DdlTrigger [tr_MScdc_ddl_event] Script Date: 14.12.2015 10:47:29 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'tr_MScdc_ddl_event')
EXECUTE dbo.sp_executesql N'
create trigger tr_MScdc_ddl_event on database for ALTER_TABLE, DROP_TABLE, ALTER_INDEX, DROP_INDEX
as
set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set ARITHABORT ON
set CONCAT_NULL_YIELDS_NULL ON
set NUMERIC_ROUNDABORT OFF
set QUOTED_IDENTIFIER ON
declare @EventData xml
set @EventData=EventData()
exec sys.sp_MScdc_ddl_event @EventData'
GO
ENABLE TRIGGER [tr_MScdc_ddl_event] ON DATABASE
GO
But the script does not create the table [CDC].[dbo_Tbl1_CT]
December 14, 2015 at 8:17 am
Cheers for trying it, think that option is actually for Change Tracking rather than CDC (thought that might have been the case when I suggested it, but worth a try).
Seems SMO doesn't support scripting for CDC (or vice-versa): https://connect.microsoft.com/SQLServer/feedback/details/774042/transfer-or-script-out-cdc-enabled-tables-using-smo
All I can think of is creating & maintaining your own T-SQL for the CDC tables, perhaps put something in your PoSh script to append it to the generated file.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply