Scribd
Upload
5 views2 pages

SQL Server CDC Database Management Guide

The document outlines the steps to create a SQL Server database named 'Test03' and enable Change Data Capture (CDC) for a table 'TestDetails03'. It includes commands for inserting, updating, and deleting records while tracking changes through CDC. Additionally, it details how to detach and reattach the database in SQL Server management.

Uploaded by

bharathkrishnan154
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views2 pages

SQL Server CDC Database Management Guide

The document outlines the steps to create a SQL Server database named 'Test03' and enable Change Data Capture (CDC) for a table 'TestDetails03'. It includes commands for inserting, updating, and deleting records while tracking changes through CDC. Additionally, it details how to detach and reattach the database in SQL Server management.

Uploaded by

bharathkrishnan154
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Cdc Capture

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\DATA\[Link]


C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\DATA\
Test01_log.ldf

Create database Test03;


use Test03;
create table TestDetails03(
Name varchar (20),
age int
);
--enable cdc at db level
EXEC sys.sp_cdc_enable_db;

--enable cdc at table level


EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'TestDetails03',
@role_name = NULL

select * from TestDetails03


insert into TestDetails03 values ('Bharath',23)
select * from cdc.dbo_TestDetails03_CT
insert into TestDetails03 values ('Ishaan',2)
UPDATE TestDetails03
SET age = 25
WHERE Name = 'Bharath';
insert into TestDetails03 values ('Amaya',2)
insert into TestDetails03 values ('Krita',5)
insert into TestDetails03 values ('Atman',12)
insert into TestDetails03 values ('Ayush',10)
insert into TestDetails03 values ('Bharath',23)
DELETE FROM TestDetails03
WHERE Name = 'Ayush';

USE master;
GO

-- Set the database to single-user mode and roll back any active transactions
ALTER DATABASE [Test03] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Detach the specified database


EXEC [Link].sp_detach_db @dbname = N'Test03';
GO

-- Attach the database


USE master;
GO

-- Attach the database


USE master
GO
CREATE DATABASE [Test03] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\
DATA\[Link]' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2019\MSSQL\
DATA\Test03_log.ldf' )
FOR ATTACH
GO

USE Test03
GO
select name,type,type_desc,is_tracked_by_cdc
from [Link]
where name = ‘TestDetails03’
GO
USE Test03
GO
select name, is_cdc_enabled
from [Link] where name = ‘IVYMDB’
GO
SELECT state_desc FROM [Link] WHERE name = 'Test03';

You might also like